We have heard from DB2 LUW users that their ETLs take too long. Index changes were the solution.
We have heard that response times were too slow. Index changes were the solution.
We have heard that CPU utilization is too high, or that there are periodic spikes in CPU utilization. Index changes were the solution.
We have seen OLTP applications appear to "seize up" or "stall". Index changes were the solution.
We have seen DB2 users struggle to complete year end and quarter end reports on a timely basis. Again, Index changes were the solution...
And then there is my personal favorite - the organization using DB2 that complains about poor performance from their storage subsystem. After a brief analysis, we discover that their OLTP database is doing asynchronous prefetch I/O 80% or more of the time to find result sets. Don't blame your storage vendor when your database is missing dozens of well designed indexes that it is screaming for!
One of DBI's customers isolated three statements out of thousands that together consumed over 90% of the read I/O in the database. After adding two indexes, there is a night and day difference in performance of this mission critical OLTP document management database.
Another DB2 User used DBI's Brother-Panther® to isolate one SQL statement amongst thousands that was using over 90% of the aggregate CPU time. A new index provided a great solution that cut CPU utilization on the server by over 50% and markedly improved overall system response times.
We have seen several cases where enabling AUTOMATIC tuning actually made performance of a system worse. The best that AUTOMATIC tuning can do is shuffle memory allocations around in a vigorous effort to try to compensate for physical design flaws.
IF YOU WANT OPTIMIZED PERFORMANCE FROM YOUR DATABASE AT THE LOWEST POSSIBLE COSTS, THEN YOU MUST MAKE SURE THE PHYSICAL DESIGN PROPERLY SUPPORTS THE WORKLOAD. PERIOD.
INDEX DESIGN
There are two types of indexes: Those that help and those that hurt.
Harmful Indexes
- Have a cardinality of ONE when the table cardinality is greater than 1,000.
- Have low cardinality relative to the cardinality of the table
- Often suffer from skewed distributions to the detriment of insert/update/delete performance
- Are redundant with other defined indexes
- Waste disk space
- Can confuse the optimizer into choosing multiple index access instead of using a single index
- Are often sources of lock contention and problems
Helpful Indexes
- Have relatively high cardinalities compared to the cardinality of the table
- Deliver great precision to the target row RIDS to avoid leaf page and data page scans
- Do not degrade insert, update, and delete performance
- Facilitate Index Access Only for critical, frequently executed queries
- Help avoid costly sorts via UNIQUE or CLUSTER definitions
- Tend to be composite (multi-column) indexes
- Avoid or mitigate the consequences of data value skew while delivering exceptional data retrieval performance
- Improve database server scalability and reliability by making workloads more predictable and more efficient.
- Can help reduce data center energy costs by 24-44% and database software licensing costs by 80% or more via improved server consolidation ratios
INDEX CARDINALITY QUICK CHECK
To check the cardinality of your indexes compared to table cardinality, all you need to do is run the simple query that is found in this blog post on Index Cardinality. The query provided will identify indexes where the index cardinality is less than 60% of the table's cardinality. If you see an index where the cardinality is less than 5% of the table cardinality, then you should consider either dropping the index or inflating its cardinality by adding a high cardinality column to the definition.
MORE INDEX DESIGN TIPS
- Read this blog post on the value of composite indexes. For multi-column indexes, place the columns that are objects of equal predicates first into the index, and then supplement with additional columns that participate in "between", "greater than", "less than", or "IN" predicates
- Read and USE the index design tips that are offered HERE - but you'll need to pass a simple cost quiz first!
PLEASE DON'T FALL VICTIM
TOO MANY TIMES - WAY TOO OFTEN - We hear someone say "Oh, we don't need an index on that small table - it is in the bufferpool all of the time!" PLEASE PUT INDEXES ON SMALL FREQUENTLY ACCESSED TABLES!!! DB2 LUW is NOT DB2 on the mainframe! Most DBI customers cut CPU utilization by 30-90% within hours by isolating SQL statements with high aggregate CPU costs that are often driving I/O to small-medium size tables. This lie - this rumor - this fallacy is accountable for enough wasted energy costs to power the City of Austin Texas on a 104F hot day! Most IT organizations could improve server consolidation ratios and cut database software licensing costs by 30-50% if they would just put proper indexes in place on tables of all sizes - small, medium, and large. Please read more in this 3 page Executive Briefing
DATABASE TUNING BENEFITS
The ripple effect benefits of proper index physical design can be astounding. If you haven't seen it yet, watch our short video created in cooperation with the San Mateo IBM Innovation Center - click HERE! You will be amazed by the performance improvements obtained by adding just TWO indexes to a DB2 9.5 database on a p570 with 16 CPUs.Want to learn more about Index Design? Register for DBI's upcoming Webinar "DB2 LUW Index Design Best Practices and Case Studies" being initially offered on 12 August 2009 at 10am EDT/9am CDT/8am MDT/7am PDT and mid-afternoon in most European locations: REGISTRATION LINK
If you would like to get started on improving the performance, reliability, and scalability of your DB2 LUW databases, and also impress your management with your amazing abilities to help lower IT costs, then please CONTACT DBI and WE'LL HELP YOU!
Kind regards,
Scott