Fundamentals: Running Database Engine Tuning Advisor and Selecting Indexes

Artikel
04/08/2011

[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.

Run a Tuning Session

To start a Database Engine Tuning Advisor tuning session, follow these steps:

Launch Database Engine Tuning Advisor. This is located under Performance Tool in the Microsoft SQL Server group on your Programs menu.
Verify that no one is using the server that is running a test copy of your ISV database. Running Database Engine Tuning Advisor will put a significant load on the server and will result in a loss of performance while it is running. This is strictly an after-hours activity that is best done over a weekend. To identify who is actively using the server:
1. Open SQL Server Management Studio
2. Connect to the SQL Server
3. Execute SP_WHO as a query. The currect connections to SQL Server (and their activity) is shown
In the Connect to Server dialog box, connect to the server that is running your ISV product.

The Database Engine Tuning Advisor starts.
In the Workload area, select the file that you created by using SQL Server Profiler in the previous post, and then click to select the SQL Server database associated with your ISV product (we are using wcadmin) check box. Note: all tables in the database are selected automatically .
For the first run, click the Tuning Options tab, and then click the Indexes, No partitioning, and Keep all existing PDS radio buttons.
Click Advanced Options, verify that the setting for Max. columns per index is 1023, verify that All recommendations are offline is selected, and then click OK.
Estimate how long this server is going to be unused. Typically, this is an end-of-day activity, so set the stopping time to one hour before you come to work or one hour before users are expected to start. Do not allow Database Engine Tuning Advisor to run without a stopping time or to cross into production hours.
Click Start Analysis.

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.

Apply the Recommendations

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 columnsspecified 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:

The fewer the columns, the smaller the indexes are physically and the fewer the resources required to maintain them.
Most well-designed databases have well-defined relationships between tables that involve only a few columns.
When a where clause is encountered in a select clause, performance can be greatly improved by building an index on just one of the where clause columns.

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:

Your first choices should be items that are labeled clustered, unique in the Details column (regardless of the number of columns) because they often affect the structure of data on the disk and thus add extra performance beyond that of indexing. Unfortunately, you cannot click the Details column to sort it; instead, you must look at all the rows.
If there are no recommendations that are labeled clustered, unique, move on to the recommendations that have just one column.

Now, click Apply Recommendations, and the Applying Recommendations dialog box is displayed.

Rerun the Tuning Session

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:

Never run it during business hours.
Always tell it when to stop.
Run it against several loads captured by SQL Server Profiler.
Add the simplest indexes first, and then rerun the tuning session.

Hitting the Threshold

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.

Comments

Anonymous
April 13, 2011
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.
Anonymous
July 09, 2013
If you're running this against a test DB how do you incorporate the final index changes to the live DB?

Freigeben über