Database Engine Tuning Advisor Step By Step

Database Engine Tuning Advisor is inbuilt tool which comes with Microsoft SQL Server Management Studio which helps in analyzing required Indexes and Indexed views, Statistics, Partitioning Strategy and Physical Design Structure (PDS) to keep in the database for performance improvement. We will see step by step how Tuning Advisor provides recommendations for given workload by Query. We are going to use 3 HEAP tables mentioned in the below query.

SELECT stb.name, idx.type_desc FROM sys.tables stb 
INNER JOIN sys.indexes idx ON stb.object_id=idx.object_id
AND idx.type=0 ORDER BY stb.name

SELECT *FROM [tbl_Customer] CU INNER JOIN [tbl_SalesOrderHeader] SOH
ON CU.CustomerID = SOH.CustomerID INNER JOIN [tbl_SalesOrderDetail] SOD
ON SOH.SalesOrderID = SOD.SalesOrderID
WHERE CU.AccountNumber = 'AW00025663'

Step 1:

First, write your SQL query on query window for which you want tuning recommendations, then right click and select “Analyze Query in Database Engine Tuning Advisor”.

Step 2:

Database Engine Tuning Advisor window will pop up where by default your Session Name and Database will be selected in General tab.

Step 3:

Go to Tuning Options tab, select required Physical Design Structure (PDS) to use in the database, Partitioning Strategy to Employ and Physical Design Structure to keep in the database then click on Advanced Options and Define Maximum space for recommendation in MB, this space is required to Database Engine for Analysis purpose otherwise you may get error if there is insufficient space. You can give approximate space for this process and check.

Step 4:

Now, click on “Start Analysis” button. The Status bar will show you the current progress and error if any. Once process is complete you will get Success status as mentioned in below image.

Step 5:

Go to Recommendations tab, where you will find Estimated Improvement in percentage for your workload. In our case, we can achieve 99% performance improvement after implementing recommended indexes on the tables. You can click on Recommended Definition Link and Copy the query from clipboard and implement it on your required database. You have option to apply query one by one or Go to Actions tab in Tuning Advisor and select Apply Recommendations where you can schedule it for later implementation for all your definitions.

Step 6:

You can see the Tuning Summary from Reports tab where you can find some quick information of your workload query as in Figure 6.

 

CLICK HERE to watch live practical.

Reference: Manzoor Siddiqui [www.SQLServerLog.com]

You may also like

• 

  SQL Aliases
• 

  Understanding and Troubleshooting SQL Server Performance Issues
• 

  How to Enable ‘sa’ Login in SQL Server
• 

  SQL COUNT() Function
• 

  Find All Tables Missing Clustered Index in Database
• 

  Troubleshooting Database Slowness and Blocking with sp_who, sp_who2 and sp_WhoIsActive