Database Engine Tuning Advisor helps you select and create an optimal set of indexes, indexed views, statistics and partitions for the tables to improve the Query performance .

Database Engine Tuning Advisor uses trace files, trace tables, or plan cache as workload input when tuning databases or else it could use the query which you select for workload analysis.

The Database Engine Tuning Advisor (DTA) helps you to tune databases to improve query processing.

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

Before going into this session let us see how indexes (or)stats affects the performance of the query

1.I have created a new database(E.g..DTA).In the new database I have created a table named Test with 100000 rows in it. Let us see how to create necessary indexes and stats on tables to improve query performance based on the queries fired this table .

First let us enable statistics time on and statistics IO on to view the compile time,CPU time ,elapsed time and I/O’s performed by the query

set statistics time on
set statistics IO on

Example:Execute the query select * from Test where a=10,

 

Before creating index,it takes 1158 logical reads, cpu time=16ms and elapsed time=28ms.The performance is very low.In order to increase the performance we go for indexes.

 

 

After creating index, logical reads have become 329,cpu time=15ms and elapsed time= 11ms.

 

2. Let us see how to create this indexes and statistics using database tuning advisor. Copy the query which has to be tuned in SSMS new query windows.

 

 

3. select your query->right click->select Analyze query in database tuning advisor.

4.Then this window will appear .In that select the database  to do workload analysis.

5.Then click start analysis in tool bar.Tuning process will be started.

6.After tuning process completes, recommendations will be provided for your table to improve the query performance.

7.Either you could save the recommendation to apply later or you could apply it immediately .

 

8.We can also use  trace files as workload to tune the database. Let us create a trace file by using SQL Server Profiler  and see how that can be used to tune the database.

Open SQL Server Profiler

9.SQL Server Profiler window appears.In that give trace name.Then Run the profiler.

 

10. Now execute the queries which you would like to tune Trace file will capture the events.

For example: Let us execute the same select query which we used in the beginning.

 

 

11.  Save the trace file. 

 

12. Open the Database tuning advisor. In the Workload area, select the file that you created by using SQL Server Profiler.Select the database for workload analysis and to tune.Then click start analysis.

 

 

13.Index recommendations will be provided.By applying this recommendation the estimated improvement will be 56%.

14.Then apply the recommendation.Actions->Apply recommendation.You could apply  the recommendations immediately or you could schedule it for later.

15.Here I choose to apply the recommendations immediately.

 

16. Once the recommendations are applied performance of the query will improve.