DB2 LUW Index Design Best Practices


Posted by Scott on July 8, 2009, 3:44 pm
in DB2 Performance Best Practices ( DB2 Performance)

Despite all the hype about AUTOMATIC tuning, a remarkably large number of DB2 LUW customers are still suffering from inadequate and sub-optimal performance. AUTOMATIC tuning can provide some benefits, BUT it only works well when the database is free of significant physical design defects.


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


Despite all the hype about AUTOMATIC tuning, a remarkably large number of DB2 LUW customers are still suffering from inadequate and sub-optimal performance. AUTOMATIC tuning can provide some benefits, BUT it only works well when the database is free of significant physical design defects.


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



Helpful Indexes




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




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

Post from : http://www.dbisoftware.com/blog/db2_performance.php
Printed from : http://www.dbisoftware.com/blog/db2_performance.php?id=131