Using indexes can improve the performance of applications using the database. This is well known to many, many people. However, the application of too many indexes can have the reverse effect. There’s a balance that must be struck and you may find yourself needing to locate indexes that aren’t being used. If you’re already using the Quest SQL Optimizer for DB2 LUW, you can take advantage of the Index Usage Analyzer to help with this process.
Before we jump into this feature, I feel I should provide you with a little bit of background information. The Index Usage Analyzer identifies unused indexes by analyzing access plans and it is a wizard-driven process. While working through the wizard, you must specify which SQL statements you want to use for analysis. These SQL statements can be entered while working through the wizard or they can be selected from other features of the SQL Optimizer; specifically, the SQL Repository or the SQL Scanner. Let’s touch briefly on these other features.
The SQL Scanner extracts SQL statements embedded in database objects, captured from the DB2 Event Monitor, or stored in application source code and binary files. Once the SQL is extracted, access plans are retrieved and then classified according to a Problematic, Complex or Simple category, all without executing these statements. This is useful for locating potentially poor performing statements that may need to be optimized using the SQL Optimizer. You can access this feature using the Tools | SQL Scanner menu.
The SQL Repository stores statements that are used in the analysis of database performance. These statements could very well be ones you’ve deemed to be critically important. The repository is really a file that is located somewhere accessible to the workstation on which the SQL Optimizer is installed. You can manage the contents of the repository using the Tools | SQL Repository menu.
To start the Index Usage Analysis, select the Tools | Index Usage Analyzer menu. Now, select the Analysis | New Analysis menu. You’ll see the following screen:
Click Next and you’ll see something similar to the following:
Ok, now we’re coming to where we need to supply the SQL statements we want to use for this investigation. I’ve highlighted the dropdown list that is used to add SQL from the SQL Repository or SQL Scanner features I mentioned previously. You can also walk through another wizard-driven process and manually enter the SQL you want to investigate by clicking on the icon highlighted below:
Make sure to check the box next to the SQL you want to use in this investigation and then click the Finish button. This starts the analysis process and when it’s complete, you’ll see a screen similar to the following:
We can use the tree to focus our analysis by looking at the tables used by the SQL we’re investigating as well as the SQL statements themselves. Selecting a node changes the information displayed in the right-hand pane.
** An example of a table node selected and examining the Unused Index
If I’m satisfied with the results, I can use a right-mouse click feature to actually drop an Unused Index. Naturally, I should use caution before dropping an index and take the appropriate steps to ensure this is the proper decision. If I’m examining the Unused Index tab, as shown below, I can right-mouse click on an index and select the Drop Index option: