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...
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 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
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
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