Evaluate Index Usage in Databases

When you think about the job of maintaining indexes, what tasks come to mind? Typically, DBAs think about tuning, defragmenting, and rebuilding indexes. However, you also need to identify whether any of your tables fall into the following categories and, if so, take appropriate action:

Tables without any indexes (even without clustered indexes). Tables without any indexes can be slow to query, especially if they have a lot of rows. If a table doesn’t have any indexes, it’s a good idea to put the necessary indexes in place to improve query performance.

Tables with indexes that aren’t being used. Sometimes SQL Server doesn’t use a table’s index because it determines that using the index isn’t cost effective in terms of I/O. If a table has a nonclustered index that’s not being used, you might consider dropping that index. If a table has a clustered index that’s not being used, you should investigate the reason why.

Tables with indexes that aren’t being used efficiently. With some tables, SQL Server does more index scanning (i.e., SQL Server searches the index vertically) than index seeking (i.e., SQL Server searches the index horizontally). Index scanning is less efficient than index seeking. SQL Server determines whether to use index scanning or index seeking based on the index definition and how the query has been presented. The following example illustrates this.

In the AdventureWorks database, suppose there’s a clustered index named PK_Address_AddressID on Person.Address for the AddressID column. You run the code

USE adventureworks  GO SELECT * FROM Person.Address

and check the execution plan. As Figure 1 shows, the execution plan reveals that SQL Server performed an index scan for the SELECT statement.

However, if you run this SELECT statement instead

SELECT * FROM Person.Address   WHERE AddressID=1

and check the execution plan (see Figure 2), you’ll discover that SQL Server performed an index seek rather than an index scan. Index seeks can be quite efficient on large tables.

I wrote 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. You can find the code for Index_Evaluation_ USP in the 99985.zip file. (To download this file, click the 99985.zip hotlink at the top of this page.)

You can place Index_Evaluation_USP in any database, but I typically place it in msdb. Running this stored procedure will highlight indexless tables, unused indexes, and inefficient indexes for all the databases in the current SQL Server 2005 instance. Index_Evaluation_USP stores the results in a table called Index_Evaluation_Map within the current database. Figure 3 shows sample output that identifies the indexes not being used.

—Shaunt Khaldtiance, senior DBA, CityIndex