Return to Previous Article in the Series
Index Performance Audit Checklist
| Indexing Checklist | Your Response |
| Have you run the Index Tuning Wizard recently? | |
| Does every table in each database have a clustered index? | |
| Are any of the columns in any table indexed more than once? | |
| Are there any indexes that are not being used in queries? | |
| Are the indexes too wide? | |
| Are tables that are JOINed have the appropriate indexes on the JOINed columns? | |
| Are the indexes unique enough to be useful? | |
| Are you taking advantage of covering indexes? | |
| How often are indexes rebuilt? | |
| What is your index fillfactor? |
Enter your results in the table above.
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. For example, this website has many index-related tips, many of them very advanced, on these topics:
- Indexes (General)
- Indexes (Clustered)
- Indexes (Composite)
- Indexes (Covering)
- Indexes (Non-clustered)
- Indexes (Rebuilding)
- Index Tuning Wizard
If you have not done so yet, you will want to review each of these tips pages.
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.