SQL Server Database tuning advisor

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

image

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.

image

 

 

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

image

 

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.

 

image

 

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

image

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

image

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

image

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

image

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

image

 

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

image

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

image

 

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.

 

image

 

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.

image

 

 

image

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

image

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

image

15.Here I choose to apply the recommendations immediately.

image

 

image

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s