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

Fig: Find HeapTables

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”.

Figure 1: 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.

Figure 2: Select Session Name and Database Name

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.

Figure 3: Tuning Options

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.

Figure 4: Progress Status

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 Recommendationswhere you can schedule it for later implementation for all your definitions.

Figure 5: Recommendations for Tuning

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.

Figure 6: Tuning Advisor Reports

CLICK HERE to watch live practical.

Reference: Manzoor Siddiqui [www.SQLServerLog.com]