Aug 7, 2013

How to use database engine tuning advisor to get recommendations in sql server


How to get recommendation from sql server tuning advisor to improve query performance?

Sql server tuning advisor in tools which accept a trace file and suggest missing indexes, statistics etc and generate various type of report like costliest queries etc. Today we are performing a demo work to know about how to use and get suggestions from sql server tuning advisor.

Step 1: 

We are creating a new database named Exact_Help

CREATE DATABASE Exact_Help

In the Exact_Help database we are creating a table tblEmployee and inserting records into it:

--Creating a new table in Exact_Help database
CREATE TABLE tblEmployee(
    ntEmployeeID BIGINT IDENTITY PRIMARY KEY,
    vcName VARCHAR(100),
    ntAge INT,
    moSalary MONEY
)

--Inserting records into the table tblEmployee
INSERT tblEmployee
SELECT
    LEFT([Text],20),
    severity,
    language_id 
FROM Sys.messages

Step 2: Now we are opening sql server profile to create a trace file. To open it, click on Tools -> Sql Server Profiler.


We will get the trace properties window:

Here,

Trace Name: You can write any trace name.
Use the Template: Choose the Tuning in drop down since we are creating trace for tuning purpose.
Save to File: To specify the path where we want to save the trace file. We can also save in the sql server table.


Now click on Event Selection Tab:
I want to restrict the tuning process for Exact_Help database. For this click on Columns Filters button and in DatabaseName write Like Exact_Help. Now Run the Profiler.

Step 3:

Now we are executing few sql queries in SSMS for which we want to get query optimization recommendations.

SELECT vcName, ntAge
FROM  tblEmployee
WHERE ntAge = 16

SELECT vcName, moSalary
FROM  tblEmployee
WHERE vcName LIKE 'a%'

SELECT *
FROM  tblEmployee
WHERE moSalary > 1000

We are executing each sql queries 3 times one by one. Now we are going to stop the profiler.


In this example we will get the Optimization_Demo.trc trace file at desktop since we have saved at this path.

Step 4: Now we are going to open Database Engine Tuning Advisor. We can get it from Tools -> Database Engine Tuning Advisor.


Then connect to database engine.


Session Name: You can write any session name.
Workload File: Specify the path of your trace file.
Database for workload analysis: Specify the database name where you want to analyze the database.
Select databases and tables to tune: Specify for which tables you want to get optimization suggestions.

In tuning option tab there any many options which we can set according to our requirements.

Step 5: Now click on start analysis button (at the top position)


After sometime we will get recommendation of missing indexes, statistics etc.


At the top we will get estimated imprudent which means if we will follow its recommendation query performance may increase by that percentage. Recommendations are:

--Index Suggestion 1
CREATE NONCLUSTERED INDEX [_dta_index_tblEmployee_14_2105058535__K3_2]
ON [dbo].[tblEmployee] (
    [ntAge] ASC
)
INCLUDE ( [vcName])
WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF)
ON [PRIMARY]

--Index Suggestion 2
CREATE NONCLUSTERED INDEX [_dta_index_tblEmployee_14_2105058535__K2_4]
ON [dbo].[tblEmployee] (
    [vcName] ASC
)
INCLUDE ( [moSalary])
WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF)
ON [PRIMARY]

Apart from this tuning advisor generates different reports to analyze the query. For example:

3 comments:

  1. Thkxxx a lot bro,,,,article is awesome nd u helped me lot.......THANKS from bottom of my heart once again

    ReplyDelete
  2. Appriciated your efforts, Ritesh.

    Many Thanks.

    Vivek

    ReplyDelete
  3. Love U R web site No 1 in explaning any concept or practicle example..

    thanks alot...Ritesh

    ReplyDelete