Browse DevX
Sign up for e-mail newsletters from DevX


Reap the Benefits of SQL Server's New Database Tuning Advisor : Page 2

The Database Tuning Advisor (DTA) is an improvement over the old Index Tuning Wizard. Learn how to access the advanced features of DTA from the command line with a custom configuration.


Methodology: Picking the Dataset and Indices to Test

I did not have any Profiler traces to feed DTA, so I had to shop around for something interesting to test. I decided to use the AdventureWorks sample database and review system tables to find a data set to evaluate alternate physical implementations suggested by DTA. I was looking for a data set to be narrow and feature a couple of interesting data types: a date and some things to count or add. I prefer columns that are not indexed, so I can create and destroy them, as well as measure their statistics and response times without tinkering with shipped objects.

As an enhancement to the data set, the test can include a volume of rows to make more than one partitioning plausible (say, months in one partition and regions in another). It can also make numerous possible indexes plausible (e.g., a covered query, one involving an equi-join on a clustered index of a single column, then separate standalone indexes). Other comparisons are for a multiple-column clustered index and a multiple-column, non-clustered index, notwithstanding uniqueness. To give the possible test conditions context, Table 1 shows a decision table in rule-name format that you can use as an idealized tuning test plan to exercise join types and index types.

Condition Matrix 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
Covered Query? 2 Y Y Y Y Y Y Y Y N N N N N N N N
Unique? 2 Y Y Y Y N N N N Y Y Y Y N N N N
Clustered? 2 Y Y N N Y Y N N Y Y N N Y Y N N
Simple? 2 Y N Y N Y N Y N Y N Y N Y N Y N
    1 2 3 4 5 6 7 8 n . . . . . . .
Join Type 4 (inner, outer,
equi, and theta)
A B C D A B C D ... ... ... ... ... ... ... ...
Table 1. Decision Table for Idealized Tuning Test Plan

Now, each column from Table 1 needs to be paired with each sequence of four join conditions. The explosion of conditions would be hard to read, but it is conceptually correct as depicted above. The total number of testable conditions is the product of the tables (i.e., sixteen times four, for a total of 64 combinations of conditions to test).

For a fresh look at which columns could be fruitful indexing candidates, I took a slightly automated tack to collect candidates. I looked in the sysindex table for_WA_Sys indexes. Typically, these are created by the optimizer when a column is used in a WHERE clause, but there was no index created for it. In other words, this is the optimizer's "helper" index, which was created to resolve an ad hoc query. Figure 1shows how I collected the un-indexed columns.

Click to enlarge

Figure 1. Finding Un-Indexed Columns to Examine

Once I knew the table and column names of the un-indexed columns, I could create and destroy indexes on them. This was just a device for assessing columns to be index candidates. DO NOT DO THIS IN PRODUCTION!This is not recommended for anything but test systems.

Alternatively, DTA can work from a SQL Profiler trace file or table as its workload. The benefit here is that it may already be part of your DBA's routine, which makes analysis an even less intrusive collection mechanism. It also is a more real-world representation of database use.

Filling in the Blanks Using _WA_Sys

To have my way with the index, I needed to commandeer the clustered index already on the table. Since the clustered index is the physical artifact of a primary key declaration, I must deal with the constraint first, and then I can trash the index. The following commands carry out that sequence of events:

sp_helpindex 'production.transactionhistory'
alter table production.transactionhistory drop constraint pk_transactionhistory_transactionid
drop index production.transactionhistory.pk_transactionhistory_transactionid

clears the desks for us to now create a new one:
create unique clustered index CUX_TransactionHistory on production.transactionhistory(transactiondate, 
transactionID, ReferenceOrderID, ReferenceOrderLineID, ProductID)

In keeping with the ancient database dictum, "Store it the way you read it," I have re-designed the old pk_index with a covering index (see Figure 2).

Click to enlarge

Figure 2. Using the Information Schema Views to Generate 'Create Index' Statements

I cut and pasted the generated statement into a second query window and executed it. Now, I can examine the statistics generated for the indexes to exercise the DTA, looking for a table with lots of rows and un-indexed columns so I can create a "where" clause to exercise index selections. Table 2 lists the best-looking candidates (i.e., un-indexed date data types with many rows) arrived at by this method.

Table NameColumn Name
TransactionHistory ReferenceOrderID
TransactionHistory ReferenceOrderLineID
TransactionHistory TransactionDate
Table 2. Candidate Un-Indexed Columns

Transaction History could be surrounded by SalesOrderHeader and Product. This provides a date, as well as costs and quantities, to aggregate.

Thanks for your registration, follow us on our social networks to keep up-to-date