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

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.

We will get the trace properties window:

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.

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

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

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:

Write SQL queries in set-based approach SQL server
SQL Server best practices for stored procedure
What is Adhoc query in SQL server: Cached Query Plan
Subquery vs inner join which one is better in SQL server
Difference between actual execution plan and estimated execution plan in SQL server