articles >> performance tuning >> How to Identify Non-Active SQL Server Indexes ...

How to Identify Non-Active SQL Server Indexes

By : Luis Martin Apr 06, 2005

As a DBA, it is our regular task to review our databases and see if there is any way we can boost their performance. While adding new or better indexes to a database is one of the primary ways we can boost performance, an often forgotten way to help boost database performance is to remove indexes that are no longer being used, or to identify overly large indexes that take a lot of SQL Server's resources. Unused indexes hurt INSERT, UPDATE, and DELETE performance, and add unnecessary disk I/O. So anytime we can eliminate unnecessary indexes, so much the better.

In addition, it is often a good idea to identify very large indexes, and see if they are being used correctly. A poorly designed index, that is huge, can also contribute to the same kinds of problems you see when you have unused indexes.

The hard question is, how do we know which indexes are used and which are not? And how do we easily identify very large indexes? In this regard, SQL Server does not offer much in the way of automated aid, and it is often necessary to identify unused or excessively large indexes on our own.

The method proposed in this article to identify unused and very large indexes is simple, but it demands some time and patience to obtain our objective.

 

Methodology

Our key tool for this battle is Profiler. With it, we must collect database activities that have occurred for at least 4 or 5 days. While this time frame is not critical, it helps to ensure that seldom used indexed are not identified and accidentally deleted.

The events that we must collect with Profiler include:

Stored Procedures

RPC:Completed

SP:StmtCompleted

TSQL

SQL:BatchCompleted

SQL:StmtCompleted

With respect to the columns, the recommended ones to collect are:

ApplicationName

EventClass

TextData

Duration

LoginName

Reads

NTUserName

To help reduce the amount of events collected over the time of the Profiler trace, it is recommended that you only collect those events that have a duration of 50 milliseconds or longer. This will still produce quite a large file, so you will want to ensure that you are not storing this data on your production SQL Server, and also that you have plenty of room for the trace file to be stored.

 

Generating a Report of the Active Indexes

Next, we want analyze our Profiler trace file using the Index Tuning Wizard. Be sure to use the options “Keep all existing indexes” and to use the “Thorough” tuning mode. Under the “Advanced Options,” unselect “Limit number of workload queries to sample.”

As you might imagine, running this analysis will put a load on your production SQL Server. Be sure to run this during a time when your production SQL Server is not very busy.

Once the Index Tuning Wizard has completed, we want to take a look at the Index Usage Report, as shown below. Note that this report tells us which indexes were and were not used. It also tells us the size of each index.

At this point, we could stop and use these results as our basis for removing unused indexes and identifying large indexes. But if we have a lot of tables to work with, then you might want to consider the following, additional steps.

Assuming we want to automate our additional analysis, what we want to do now is to “save” this report in the text format using the “Save” button, then we will import this text file into a SQL Server table.