How to get a 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.


Here,
Trace Name: You can write any trace name.
Use the Template: Choose the Tuning in the drop down since we are creating a 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 the 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.
After some time we will get the 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
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:
Thkxxx a lot bro,,,,article is awesome nd u helped me lot.......THANKS from bottom of my heart once again
Appriciated your efforts, Ritesh.
Many Thanks.
Vivek
Love U R web site No 1 in explaning any concept or practicle example..
thanks alot...Ritesh
Post a Comment