SQL Server Database Index Performance Checklist

Here are some tips for using the Index Tuning Wizard when auditing a SQL Server database's indexes:

• When you do the Profiler capture (which is used by the Index Tuning Wizard to perform its analysis), capture the data during a time of day that is representative of a normal load on the database. I generally like to pick a time during mid-morning or mid-afternoon, and then run the Profiler trace over a period of one hour.
  
  
• Once the Profiler trace has been captured, the Index Tuning Wizard can be run at any time. But, it is a good idea to run it when the database is not busy, preferably after hours. This is because the analysis performed by the Index Tuning Wizard incurs some server overhead, and there is no point in negatively affecting the server's performance if you don't have to. Also, avoid running the analysis on your production server (the Wizard will still have to connect to the production server), but running the Wizard on another server reduces the load on the production server when the analysis is performed.
  
  
• Although it will take more time for the analysis to complete, you need to specify during the setup of the Index Tuning Wizard several options that will help ensure a thorough analysis. These include: not selecting the option to "Keep all existing indexes," as you will want to identify those indexes that are not being used; specifying that you want to perform a "Thorough" analysis, not a "Fast" or "Medium" one; not selecting the option to "Limit the number of workload queries to sample," and to leave the "maximize columns per index" setting to it maximum setting of 16; and specifying that all tables are to be selecting for tuning. By selecting this options, you allow the Index Tuning Wizard to do its job thoroughly, although it might take hours for it to complete, depending on the size of the Profiler trace and the speed of hardware you are performing the analysis on. Note: these instructions are for SQL Server 2000, SQL Server 7.0 instructions are slightly different.
  
  
• Once the analysis is complete, the Wizard might not have any recommendations, it may recommend to remove one or more indexes, or it may recommend to add one or more indexes, or it may recommend both. You will need to carefully evaluate its recommendations before you take them. For example, the Wizard might recommend to drop a particular index, but you know that this particular index is really needed. So why did the Wizard recommend it be deleted when you know it is not a good idea? This is because the Wizard does not analyze every query found in the trace file (only a sample of them), plus it is possible that your sample trace data did not include the query that needs the index. In these cases, the Wizard might recommend that an index be dropped, even though it may not be a good idea. Once you verify that an index is not needed, should you drop it.
  
  If the Wizard recommends adding new indexes, you will want to evaluate them, and also compare them to the currently existing indexes on the table to see if they make sense and might potentially cause new problems. For example, a recommended index might help a particular query, but it may also slow down a common INSERT operation this is performed thousands of times each hour.  The Wizard can't know this, and you must decide what is more important, some queries that run a little faster and INSERTs that run a little slower, or vice versa.
  
  And last of all, even if the Index Tuning Wizard doesn't recommend any new indexes, this doesn't mean that no new indexes are needed, only that based on the trace data that was analyzed that it didn't recommend any. You might want to consider running several traces over several days in order to get an even wider sample of the data in order to better help identify necessary indexes. And even then, the Index Tuning Wizard can't find all the needed indexes, but it will find all the obviously needed ones.

Once you have performed your analysis and made the recommended changes, I recommend that you do another trace and analysis in order to see what affect your changes made. Also keep in mind that using the Index Wizard Analysis is not a one time event. The underlying data in a database changes over time, along with the types of queries run. So you should make it a point to take traces and run analyses periodically on your servers to keep them in regular tune.

Does Every Table in Each Databases Have a Clustered Index?

As a rule of thumb, every table in every database should have a clustered index. Generally, but not always, the clustered index should be on a column that monotonically increases--such as an identity column, or some other column where the value is increasing--and is unique. In many cases, the primary key is the ideal column for a clustered index. 

If you have any experience with performance tuning SQL Server 6.5, you may have heard that is not a good idea to add a clustered index to a column that monotonically increases because it can cause a "hotspot" on the disk that can cause performance problems. That advice is true in SQL Server 6.5.

In SQL Server 7.0 and 2000, "hotspots" aren't generally a problem. You would have to have over 1,000 transactions a second before a "hotspot" were to negatively affect performance. In fact, a "hotspot" can be beneficial under these circumstances because it eliminates page splits.

Here's why. If you are inserting new rows into a table that has a clustered index as its primary key, and the key monotonically increases, these means that each INSERT will physically occur one after another on the disk. Because of this, page splits won't occur, which in itself saves overhead. This is because SQL Server has the ability to determine if data being inserted into a table has a monotonically increasing sequence, and won't perform page splits when this happens.

If you are inserting a lot of rows into a heap (a table without a clustered index), data is not inserted in any particular order onto data pages, whether the data is monotonically or not monotonically increasing. This results in SQL Server having to work harder (more reads) to access the data when requested from disk. On the other hand, if a clustered index is added to a table, data is inserted sequentially on data pages, and generally less disk I/O is required to retrieve the data when requested from disk.

If data is inserted into a clustered index in more or less random order, data is often inserted randomly into data pages, which is similar to the problem of inserting data into a heap, which contributes to page splits.

So again, the overall best recommendation is to add a clustered index to a column that monotonically increases (assuming there is a column that does so), for best overall performance. This is especially true if the table is subject to many INSERTS, UPDATES, and DELETES. But if a table is subject to few data modification, but to many SELECT statements, then this advice is less useful, and other options for the clustered index should be considered.

As part of your index audit, check to see if every table in your databases has an index or not. If there is no index at all, seriously consider adding a clustered index, based on the advice provided above. There is virtually no downside to adding a clustered index to a table that does not already have one.

Are Any of the Columns in Any Table Indexed More than Once?

This may sound like obvious advice, but it is more common than you think, especially if a database has been around for awhile and it has been administered by multiple DBAs. SQL Server doesn't care if you do this, as long as the names of the indexes are different. So as you examine your table's current indexes, check to see if any columns have unnecessary duplicate indexes. Removing them not only reduces disk space, but speeds up all data access or modification to that table.

One common example of duplicate indexes is forgetting that columns that have a primary key, or that are specified as unique, are automatically indexed, and then indexing them again under different index names.