[Prior Post in Series] [Next Post in Series]
In my last post Fundamentals: Creating a Workload by Using a Trace Log, I illustrated how to create a workload. In this post, I will look at the complimentary step, using the log as input to the 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 or wish to learn more, view the following webcasts and links:
The best situation is to use a Trace Log captured from the actual production system. The more representative of actual load that the log is, the more reliable will the improvement predicted be from the tuning advisor.
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.
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. I usually stop adding indexes when the remaining recommendations would result in less than a 5 percent improvement.
Quick and Dirty Rule of Thumb: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. You have one more step to do: delete unused or expensive indexes. My next post will show you how to do this.
This post describes a general pattern that you may use to tune your database. There is a series of adjunct posts on this blog that deals with real case studies tuning specific ISV products. You should check if your ISV product has been hand-tuned already. If there isn't one, and you are willing to allow me access to a test SQL Server of your system, then please email me to see if we can arrange a (free) hand-tuning.
I would recommend using the hand tuning as a starting point and then proceed with the above method afterwards to get the best results.
Great article for getting started and using what's available within the Microsoft Toolset. It would be great to see an article on how ISVs can use SQLNexus to do their analysis.