DatabaseTuningAdvisor

The Database Engine Tuning Advisor is a new tool in Microsoft SQL Server 2005 that enables you to tune databases for improved query processing. Database Engine Tuning Advisor examines how queries are processed in the databases you specify and then it recommends how you can improve query processing performance by modifying physical design structures such as indexes, indexed views, and partitioning. It replaces the Index Tuning Wizard from Microsoft SQL Server 2000, and offers many new features.

If Perhaps an index was created using the wrong columns, or may be users have started querying different data over time, which would require the creation of new indexes. If any of this is true, your databases need tuning. To do that, you need to use the Database Engine Tuning Advisor. Before you can run the Database Engine Tuning Advisor, you need to create a workload file. You get this by running and saving a trace in Profiler (usually by creating a trace with the Tuning template) or the query file(.sql file) or XML format file. Maximum time profiler is considered to be the best, since it will provide you the actual work flow. If you are using profiler then it is best to get this workload during times of peak database activity to make sure you give the advisor an accurate load. First you need to create a workload file to use with the advisor as said above.

Here in this article I’m going to check DTA with Adventureworks db and ProductDescription table. I’ve deleted the index and primary key to check whether DTA is capable to detect this. After this step I’ve executed a select query on the above table and saved the query as a SQL file. I’ve used the above file in DTA as workolad file. When DTA finishes its work, it perfectly detects the missing index and recommended to create the same.

Deleting Index & primary key on ProductDescription table:

I’ve deleted the primary key and index “PK_ProductDescription_ProductDescriptionID” available on the table ProductDescription.

Creating workload file

Save the below query as SQL file. I’ve saved it as workload.sql.
select ProductDescriptionID,ModifiedDate from Production.ProductDescription
where Description like '%frame%'
You can also use profiler to get workload file.

Using DTA

• Goto Start --> Programs --> Microsoft SQL Server 2005 --> Performance Tools --> Database Engine Tuning Advisor
• Connect to the server by correct authentication mode
• In the left pane you can see “Session Monitor” where the connected servername will be displayed
• Right click on the server and click on New session, you will get a window as below
  
• Provide the sessionname, databasename and workload file as show in the figure above
• Then goto “Tunning Options” tab and you can see the default settings to create index or partitions as per recommendations as show below

• Once its done, then click on the “start analysis” button as shown below

• After analysis started you will see the below window

• As I told earlier once the analysis completed it has recommended to create a index on the description column, you can also get the index creation script from the result itself as show below

Conclusion:

DTA a good utility which can be used to make \ alter the database objects to work more efficient with respect to the given query (workload file). It will recommend for index and partition details.