2 Stored Procedures to Tune Your Indexes

If you’ve been a DBA for a while, you likely have encountered developers who are extremely proficient in front-end application design but have a rather limited understanding of the database technology they’re using as the back end. In these situations, it seems that indexes are only applied as an afterthought, if they are even applied at all.

Once these applications start to process a serious amount of data, performance problems begin to surface. This typically happens a few days after the application is released to production. A DBA at this point might be asked to take a look and see what can be done.

After some preliminary investigations and performing a few traces, the DBA might decide to perform a full index analysis. The Database Engine Tuning Advisor might be useful here, but I prefer getting to know my indexes personally rather than relying on a wizard’s best educated guess. So, I wrote two T-SQL stored procedures to aid in this task: sp_GenerateIndexes and sp_ListAllIndexes, which work on SQL Server 2005 and SQL Server 2000.

The sp_GenerateIndexes stored procedure generates SQL statements that can drop and create indexes for a specified table. By having the SQL code drop and create indexes, you can do some experimenting by adding or removing columns as you see fit. If you’re unhappy with the results, you can simply use sp_GenerateIndexes again to drop the new indexes and re-create the original indexes by running the SQL code that was generated previously. You would, of course, do this experimentation only in a test environment, not on your live production server.

The sp_GenerateIndexes stored procedure also creates an extremely useful report that details the various properties of each index, such as whether the index is clustered, the sort order, and the fill factor. Figure 1 contains an example of a report that’s been edited for space. I tried to keep the column names short so that I could include as much information as possible in the report. Here is a brief description of what each column in the report contains:

As you can see from the sample report in Figure 1, the MyTable table has a clustered primary key, a unique index, and a nonclustered compound index with a descending sort order on its second column. The original fill factor was specified as 100 for each index. What stands out in this report is that the third index is a compound index that includes the same column as the clustered index (i.e., TheID). This is wasteful because each index row in the nonclustered index already contains a row-locater, which in this case, is the clustered index key (i.e., TheID).

The second stored procedure that I created— sp_ListAllIndexes—iterates through all the tables in the current database, calling sp_GenerateIndexes for each table found. The output from sp_GenerateIndexes is captured and stored in a worktable, then displayed at the end as one big master report.

After running sp_ListAllIndexes, you’ll have a master report that lists all the indexes on all the tables in the current database. A quick glance at this report is often all you need to make a few strategic decisions, such as whether to add clustered indexes to heap tables or whether to remove obviously useless indexes. If there are no indexes to report on, you can bring the empty report to the development team and politely explain to them that a database without indexes is about as useful as a mouse without buttons.