In my earlier post, Improving Windchill Performance by Adding Missing Indexes, I illustrated the easy way to identify some missing indexes by using SQL Server 2005 Performance Dashboard. These were the low-hanging fruit. To reach the higher fruit, you need to use a longer and more traditional route: create a workload and run it by using the Database Engine Tuning Advisor. In my last post, Creating a Workload by Using a Trace Log, I wrote about how to create a workload. In this post, I will look at the second part of this route, running Database Engine Tuning Advisor to identify performance-improving indexes.
This in-depth process takes some time and has a few potential issues. I will assume that you have obtained one or more trace logs and that you are familiar with Database Engine Tuning Advisor. If you are not, view the following webcasts and links:
To start a Database Engine Tuning Advisor tuning session, follow these steps:
The Database Engine Tuning Advisor starts.
The screen will start filling up with the capture.
Then, the Progress tab is displayed.
The process will take several hours to complete if lots of transactions were recorded during the workload. When the tuning calculations are complete, the Recommendations tab and the Reports tab are displayed.
Click the Recommendations tab to see the index recommendations and details about the recommendations. You might think that the list of recommendations that is generated is overwhelming.
The number of recommendations is often considerably more than the number that SQL Server 2005 Performance Dashboard produces. SQL Server 2005 Performance Dashboard detects the low-hanging fruit, but Database Engine Tuning Advisor covers the entire tree.
When you scroll to the right, you see the Definition column.
Now, you need to choose which indexes to implement.
The classic dilemma with adding indexes is to be sure to not add one-too-many indexes. Excessive indexes may result in a loss of performance. My usual cutoff is to stop adding indexes when adding all of the recommendations would result in less than a 5 percent improvement.
You should add the indexes that have the least number of columns specified in the Definition column, and then repeat the Database Engine Tuning Advisor run. This approach gives the most impact at the least cost. Here's why:
This index might change a table scan into a subset scan. There are greatly diminishing returns on adding additional columns seen in the where clause, because columns often have a high correlation coefficient.
The initial recommendations shown below illustrate the process.
It is important to note that the Details column may contain the words unique and clustered as qualifiers for the index definition. Typically, these are your best first-choice items:
Now, click Apply Recommendations, and the Applying Recommendations dialog box is displayed.
Having to wait until the evening to rerun the Database Engine Tuning Advisor is often difficult, but slow, systematic analysis is the best way to ensure that you add the indexes that have the maximum per-index value. If you have multiple trace logs available, I suggest that you set the Database Engine Tuning Advisor's workload to rotate the trace logs each evening to get better results. Mathematics and statistics predict better results by rotating trace logs. The next time through, you may have to go up to two column recommendations and then to three. Count any columns that are shown in the include clause of the Description column as one column.
If one of the trace logs fails below the threshold for adding indexes, do not stop. Eliminate this trace log from the list, and continue with the next trace log. For example, the next Database Engine Tuning Advisor session resulted in the following recommendations.
So, just adding two of the long list of recommendations resulted in 50 percent of the potential improvement. (The original run had an estimated 4 percent improvement; this run has an estimated 2 percent improvement.)
The key points to remember about running Database Engine Tuning Advisor are:
At some point, you will reach your improvement threshold and stop adding indexes. Then, you have one more step to do: delete unused or expensive indexes. My next post will show you how to do this.
Ken Lassesen is part of the original team that created Dr. GUI of MSDN and specializes in new and resurrected commercial product architecture. He developed architecture for several Microsoft websites, including the original MSDN site and the current Microsoft Partner Network site. He's equally at home with SQL Server, XHTML, Section 508 accessibility standards, globalization, Security Content Automation Protocol (SCAP) security, C#, and ASP.NET server controls. When he is not having fun with technology, he enjoys taking lunch-break hikes in the North Cascades.