IBM Data Management magazine Issue 2, 2009

The Performance
and Cost Optimization
Ripple Effect

What every manager needs to know to make
database optimization pay off

Scott Hayes (Scott.Hayes@dbisoftware. com) is president and CEO of DBI Software. He is an IBM Data Champion and Gold Consultant; a frequent speaker at IDUG, IBM, ISACA, and ISSA conferences; a published author; and a regular blogger on DB2 for Linux, UNIX, and Windows performance.

y o

Your database is probably the last place you would look for IT cost savings. After all, what’s most important about a database is that it delivers the correct information on time, every time. But the beauty of database optimization is that it both cuts costs and improves performance. Optimizing your organization’s databases reduces unnecessary and wasteful CPU consumption, generating a ripple effect of benefits: lower CPU consumption, lower energy costs for hardware and cooling, improved server consolidation and virtualization, lower hardware costs, lower software licensing costs, and improved organizational productivity through better, more reliable, and more predictable response times.

But to get the most out of your database ptimization efforts, there are two things that you need to know. The first thing is: The physical design of your database is critically important, especially the indexes. Indexes are important to databases for much the same reasons that they’re important to people: they make it possible to find things faster with less effort.

Imagine you have a bowl of blueberries in our refrigerator and you want to use them in a recipe. You get out your favorite cookbook to find recipes that use blueberries, but it’s pretty big: 592 pages. You could scan through every page of the cookbook and scan ingredients lists looking for blueberries, but scans are costly. They take time, energy, and might even make your eyes hurt. More likely, your cookbook has a good index in the

b b a

5 back and you can look up “blueberries,” find the recipes, and go straight to those pages to pick the one you want.

Databases work the same way. Your 92-page cookbook wouldn’t make for a very large database table—almost certainly small enough to fit on a 32 MB USB memory stick. But if you asked a cookbook application to search for recipes with blueberries in the ingredient list, the application would send a query to the database, and the database would scan recipe entries. The scan might take 592 CPU instructions or more if the cookbook is not already in database memory, and it might take five seconds to complete. Now, if a useful index on INGREDIENT is available, the same search could be completed using only six CPU instructions and would be finished in a fraction of a second.

Your business system applications prob-bly don’t do frequent searches for blueberries, but the same principles apply. Just about every business system database and data warehouse has a large number of frequently accessed small tables that would fit on USB jump drives. Ask your DBAs how many tables exist in your databases that are smaller than 64 MB—the answer may surprise you.

Small database tables are important ecause of the second thing you need to know: Maximum performance optimization is possible only when you consider the entire system.