Using the Tuning Advisor to to improve SQL performance

This post is about how to use the DataBase Engine Tuning Advisor.

Performance is a key issue in solutions and performance has very much to do with calling SQL procedures and avoiding deadlocks so tuning up SP can significantly boost performance.

SQL provides the DataBase Engine Tuning Advisor which is a tool to help indexing SQL tables.

In order to evaluate the DB performance we need to have a sample to tune on, this is where the SQL Sever profiler comes in.

How to create a Trace file:

1. On the SQL management studio go to tools -> SQL Profiler.

2. Start a new trace make sure you specify a file to save to and use the Tuning template


3. Select the Events Selection tab and click on Column Filters to bring up the Edit Filter window, now specify the name of the DB to trace


4. Press run to start tracing, now is the point where you need to start using your application, in my case pushing messages in to the BizTalk server and the more the better try to cover as much scenarios as possible especially scenarios that interact with SQL Server so the trace can have good statistics.


5. After letting the systems run for some time (In my case I let the system run for a couple of minutes)  we can stop the trace and now its time to use the tuning advisor, From the SQL management studio/or the profiler itself go to tools -> DataBase Engine Tuning Advisor.

6. Create a new session specify the trace file created in the Profiler, select the workload database which is you DB and select the same DB to tune(I selected all tables) then press the Start Analysis button. should look like the following


7. When the analysis is finished we get a recommendation of which tables to index and actually the script itself, notice the estimated improvement percentage on the top left and bellow are some other screen shots of the report that was generated


The reports, other reports are available in the select report drop down


After creating the indexes and running the tuning advisor again I managed to get 0% estimated improvement, Note before creating the indexes one of the SP had occasional Dead Locks, after indexing that never happened again.

A good reason to use the tuning Advisor is to sort out Dead Lock errors (in my case I over indexed several columns which can actually decrease performance).