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.