Index Performance Audit Checklist

Auditing Index Use is Not an Easy Task, But Critical to Your Server’s Performance

When it comes to auditing index use in SQL Server databases, I sometimes get overwhelmed. For example, how to do you go about auditing indexes in a database with over 1,500 tables? While auditing a single index is relatively straight-forward, auditing thousands of them in multiple databases is not an easy task. Whether the task is easy or not, it is an important task if you want to optimize the performance of your SQL Server databases.

There are two different ways to approach the task of auditing large numbers of indexes. One option is to break down the chore into smaller, more manageable units, first focusing on those indexes that are most likely to affect the overall performance of your SQL Server. For example, you might want to start your audit on the busiest database on your server, and if that database has many tables, first start on those tables with the most data, and then working down to other tables with less data. This way, you will focus your initial efforts in areas where it will most likely have the great positive impact on your server’s performance.

Another option, and the one I generally follow (because I am somewhat lazy), is to use a more of a “management by exception” approach. What I mean by this is that if I don’t see any performance problems in a database, there is not much use in evaluating every index in the database. But if a database is demonstrating performance problems, then there is a good chance that indexes are less than optimal, and that I should pay extra attention to them, especially if the databases are mission critical. And if there are a lot of indexes to audit, then I start by focusing on the largest ones first, as they are the ones most likely to cause performance problems. For example, in the case of the database with 1,500 tables, I only audited about a dozen of them carefully (all very large), as they were the ones I felt needed the most attention.

However you decide to audit the indexes in the databases you manage, you need to come up with a sound plan and carry it out in a systematic way.

As you may have already noticed, the audit checklist I have provided above is not long. This is intentional. Remember, the goal of this article series on doing a performance audit is to identify the “easy”  and “obvious” performance issues, not to find them all. The ones that I have listed above will get you a long way to identifying and correcting the easy index-related performance problems. Once you have gotten these out of the way, then you can spend time on tougher ones.

Have You Run the Index Tuning Wizard Recently?

One of the best tools that Microsoft has given us in SQL Server 7.0 and 2000 is the Index Tuning Wizard. It is not a perfect tool, but it does help you to identify existing indexes that aren’t being used, along with recommending new indexes that can be used to help speed up queries. If you are using SQL Server 2000, it can also recommend the use of Indexed Views. It uses the actual queries you are running in your database, so its recommendations are based on how your database is really being used. The queries it needs for analysis come from the SQL Server Profiler traces you create.

One of the first things I do when doing a performance audit on a new SQL Server is to capture a trace of server activity and run the Index Tuning Wizard against it. In many cases, it can help me to quickly identify any indexes that are not being used and can be deleted, and to identify new indexes that should be added in order to boost the database’s performance.

Here are some tips for using the Index Tuning Wizard when auditing a SQL Server database’s indexes:

• When you do the Profiler capture (which is used by the Index Tuning Wizard to perform its analysis), capture the data during a time of day that is representative of a normal load on the database. I generally like to pick a time during mid-morning or mid-afternoon, and then run the Profiler trace over a period of one hour.
• Once the Profiler trace has been captured, the Index Tuning Wizard can be run at any time. But, it is a good idea to run it when the database is not busy, preferably after hours. This is because the analysis performed by the Index Tuning Wizard incurs some server overhead, and there is no point in negatively affecting the server’s performance if you don’t have to. Also, avoid running the analysis on your production server (the Wizard will still have to connect to the production server), but running the Wizard on another server reduces the load on the production server when the analysis is performed.
• Although it will take more time for the analysis to complete, you need to specify during the setup of the Index Tuning Wizard several options that will help ensure a thorough analysis. These include: not selecting the option to “Keep all existing indexes,” as you will want to identify those indexes that are not being used; specifying that you want to perform a “Thorough” analysis, not a “Fast” or “Medium” one; not selecting the option to “Limit the number of workload queries to sample,” and to leave the “maximize columns per index” setting to it maximum setting of 16; and specifying that all tables are to be selecting for tuning. By selecting this options, you allow the Index Tuning Wizard to do its job thoroughly, although it might take hours for it to complete, depending on the size of the Profiler trace and the speed of hardware you are performing the analysis on. Note: these instructions are for SQL Server 2000, SQL Server 7.0 instructions are slightly different.
• Once the analysis is complete, the Wizard might not have any recommendations, it may recommend to remove one or more indexes, or it may recommend to add one or more indexes, or it may recommend both. You will need to carefully evaluate its recommendations before you take them. For example, the Wizard might recommend to drop a particular index, but you know that this particular index is really needed. So why did the Wizard recommend it be deleted when you know it is not a good idea? This is because the Wizard does not analyze every query found in the trace file (only a sample of them), plus it is possible that your sample trace data did not include the query that needs the index. In these cases, the Wizard might recommend that an index be dropped, even though it may not be a good idea. Once you verify that an index is not needed, should you drop it. If the Wizard recommends adding new indexes, you will want to evaluate them, and also compare them to the currently existing indexes on the table to see if they make sense and might potentially cause new problems. For example, a recommended index might help a particular query, but it may also slow down a common INSERT operation this is performed thousands of times each hour.  The Wizard can’t know this, and you must decide what is more important, some queries that run a little faster and INSERTs that run a little slower, or vice versa. And last of all, even if the Index Tuning Wizard doesn’t recommend any new indexes, this doesn’t mean that no new indexes are needed, only that based on the trace data that was analyzed that it didn’t recommend any. You might want to consider running several traces over several days in order to get an even wider sample of the data in order to better help identify necessary indexes. And even then, the Index Tuning Wizard can’t find all the needed indexes, but it will find all the obviously needed ones.

Once you have performed your analysis and made the recommended changes, I recommend that you do another trace and analysis in order to see what affect your changes made. Also keep in mind that using the Index Wizard Analysis is not a one time event. The underlying data in a database changes over time, along with the types of queries run. So you should make it a point to take traces and run analyses periodically on your servers to keep them in regular tune.