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.
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 Im going to check DTA with Adventureworks db and ProductDescription table. Ive deleted the index and primary key to check whether DTA is capable to detect this. After this step Ive executed a select query on the above table and saved the query as a SQL file. Ive 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:
Ive deleted the primary key and index PK_ProductDescription_ProductDescriptionID available on the table ProductDescription.
Creating workload file
Save the below query as SQL file. Ive saved it as workload.sql.
select ProductDescriptionID,ModifiedDate from Production.ProductDescription where Description like '%frame%'
You can also use profiler (Select tuning template) to get workload file.
*) Go to 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 session name, database name and workload file as show in the figure above
*) Then go to Tuning 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
*) Once 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
DTA a good utility which can be used to make alter the database objects to work more efficient according to the given query (workload file). It will recommend for index and partition details