Improving the Index_Evaluation_USP Stored Procedure

In the Reader to Reader article "Evaluate Index Usage in Databases", Shaunt Khaldtiance provided a stored procedure, Index_Evaluation_USP, that you can use to identify tables without indexes, indexes that aren't being used, and indexes that aren't being used efficiently. I made several improvements to that stored procedure.

The original version of Index_Evaluation_USP includes the code

IF EXISTS (SELECT 1 FROM  tempdb.sys.objects WHERE NAME LIKE  '%indexmap%' ) BEGIN DROP TABLE  #indexmap END

This code works well if the #indexmap temporary table belongs to the same session. However, if there's an #indexmap temporary table created by another session, the condition will evaluate to TRUE but the DROP TABLE statement will fail because the current session has no such temporary table. To work under any circumstance, I replaced that code with the following code

IF object_id('tempdb..#indexmap')  IS NOT NULL BEGIN DROP TABLE  #indexmap END

The second change I made was to the inner SELECT statement in Listing 1.

This SELECT statement reads from the dynamic management view (DMV) named sys.dm_db_index_usage_stats. This DMV contains one row for every index that was accessed since the SQL Server instance was restarted or a database was opened, so you can use it to obtain index usage information. However, sys.dm_db_index_usage_stats includes data from all the databases in an instance. I needed the ability to analyze one database at a time, so I added AND i.database_ID=db_id() to the ON clause of the INNER JOIN statement, as callout A in Listing 1 shows.

Although sys.dm_db_index_usage_stats provides dynamic information about index usage, it doesn't contain all the information needed. Thus, the Index_Evaluation_USP stored procedure uses the sys.indexes static view to get information that isn't exposed by sys.dm_db_index_usage_stats.

In sys.indexes, a table will have a row for each index and a row representing the table itself (aka heap). In the index_id column, a value of 1 indicates a clustered index, a value greater than 1 indicates a nonclustered index, and a value of 0 indicates the table itself. For example, a table might have a row showing index_id=0 (for the table itself) and two more rows showing index_id>1 (for the nonclustered indexes). Even when a table doesn't have any indexes, sys.indexes will still contain the row representing the table itself, so you can tell if a table doesn't have any indexes if the only row for that table is the one in which index_id=0. I added code to Index_Evaluation_USP that looks for this condition (see Listing 2).

As in the original stored procedure, the tables with no indexes are further analyzed to see how many times they were accessed with a table scan. If the tables were scanned, they're good candidates to get indexes.

The last improvement I made to the stored procedure was to add an input parameter—@i_DbName—in case I wanted to run it against a particular database instead of running it against all user databases on the SQL Server instance.

You can download the modified stored procedure—dbo.Index_Evaluation_USP—by by clicking the 102103.zip hotlink at the top of the page. You can place and deploy the stored procedure in any database. To analyze a single database, you can use code such as

DECLARE @rc intEXECUTE @rc=dbo.Index_Evaluation_USP 'DB'

where DB is a valid database name. To analyze all the databases in the current SQL Server instance, you don't include a parameter, using code such as

DECLARE @rc intEXECUTE @rc=dbo.Index_Evaluation_USP

The dbo.Index_Evaluation_USP stored procedure works on SQL Server 2008 and SQL Server 2005.