Getting Starting with Database Engine Tuning Advisor in SQL Server – Part 1

Introduction

There are different techniques to optimize the performance of SQL Server queries, like keeping required and updated statistics, creating required indexes, partitioning tables, etc., but wouldn’t it be great if we had some recommendations before we started planning or optimizing queries so that we didn’t have to start from the scratch every time and in every scenario? This is where you can use the Database Engine Tuning Advisor utility to get recommendations based on your workload. I will be talking about Database Engine Tuning Advisor, how it works and its different interfaces, in this article series.

Database Engine Tuning Advisor

Database Engine Tuning Advisor is a utility that comes with SQL Server and can be used by both novice and experienced database administrators to get recommendations to improve the performance of SQL Server queries by making required physical structural changes. Based on your workload, Database Engine Tuning Advisor provides recommendations for best mix of indexes (clustered and non-clustered indexes) or indexed views, aligned or non-aligned partitions and required statistics.

Before you begin using Database Engine Tuning Advisor, you first need to collect the workload (a set of SQL Server queries that you want to optimize and tune). You can use direct queries, trace files, and trace tables generated from SQL Server Profiler as workload input when tuning databases. We will demonstrate these different methods of using workload with Database Engine Tuning Advisor to tune the performance.

When you start analysis with Database Engine Tuning Advisor, it analyzes the provided workload and recommends to add, remove, or modify physical design structures in your databases like creating indexes and partitioning. It also recommends if additional statistics objects need to be created to support physical design structures. (As discussed in my earlier article, by default SQL Server automatically creates and maintains single column statistics on strategic columns; in addition to that, Database Engine Tuning Advisor recommends that you create multicolumn statistics based on your workload). Database Engine Tuning Advisor provides T-SQL scripts to quickly implement the recommendation and summary reports on the effects of implementing those recommendations for the provided workload.

To use interactively, Database Engine Tuning Advisor has a nice, intuitive Graphical User Interface (GUI) and to use it in automated mode, it provides a Command Line Interface (CLI).

Getting Started with Database Engine Tuning Advisor – GUI Interface

There are different ways you can launch the GUI interface of Database Engine Tuning Advisor. For example, as shown in Figure 1, you can search for “Database Engine Tuning Advisor” and click on it to launch it.

Launching Database Engine Tuning Advisor
Figure 1 – Launching Database Engine Tuning Advisor

Whatever method you choose to launch Database Engine Tuning Advisor, you will be prompted to login to the SQL Server instance where you have your database for tuning. Once you are logged-in you will see an interface as shown below in Figure 2. In the left side tree view, you can see all the tuning sessions created earlier. By default, the name of the session is user name appended with date and time when the session was created; though you can change the default name by right clicking on the specific session in tree view and then clicking on “Rename Session”. On the right side, you can provide a file (with query or trace) or trace table as workload input.

Database Engine Tuning Advisor
Figure 2 – Database Engine Tuning Advisor

Now let me demonstrate how it works. First of all, go to SQL Server Management Studio (SSMS) and execute these scripts to create a database and then create two tables based on data from SalesOrderHeader and SalesOrderDetail tables of AdventureWorks database. Please note, I am using the SELECT INTO command to create these new tables as heap tables (with no indexes from the source tables) in the database that we created earlier.

CREATE DATABASE DETA
GO
USE   DETA
GO
SELECT * INTO   [SalesOrderHeader] FROM   [AdventureWorks2008R2].Sales.[SalesOrderHeader]
GO
SELECT * INTO   [SalesOrderDetail] FROM   [AdventureWorks2008R2].Sales.[SalesOrderDetail]
GO

Now copy these scripts into SSMS, select it and then right click on it. You will notice the  “Analyze Query in Database Engine Tuning Advisor” menu option in the pop-up menu as shown in Figure 3.

SELECT *
FROM   SalesOrderHeader SH 
INNER JOIN SalesOrderDetail SD ON   SH.SalesOrderID =   SD.SalesOrderID
WHERE   OrderDate = '2008-06-01   00:00:00.000'
GO
SELECT   OrderDate, ProductID,   SUM(OrderQty) AS OrderQty, SUM(UnitPriceDiscount)   AS UnitPriceDiscount,   SUM(LineTotal)
FROM   SalesOrderHeader SH 
INNER JOIN SalesOrderDetail SD ON   SH.SalesOrderID =   SD.SalesOrderID
GROUP BY OrderDate,   ProductID
HAVING SUM(OrderQty) >= 100
GO
SELECT   SalesOrderNumber, PurchaseOrderNumber, DueDate, ShipDate
FROM   SalesOrderHeader SH 
INNER JOIN SalesOrderDetail SD ON   SH.SalesOrderID =   SD.SalesOrderID
WHERE   OrderDate IN ('2008-06-01','2008-06-02', '2008-06-03', '2008-06-04', '2008-06-05')
GO
SELECT   SH.SalesOrderID,   SalesOrderNumber, PurchaseOrderNumber, DueDate, ShipDate
FROM   SalesOrderHeader SH 
INNER JOIN SalesOrderDetail SD ON   SH.SalesOrderID =   SD.SalesOrderID
WHERE   CarrierTrackingNumber IN ('EF67-4713-BD', '6C08-4C4C-B8')
ORDER BY SH.SalesOrderID
GO

Launching Database Engine Tuning Advisor in SSMS
Figure 3 – Launching Database Engine Tuning Advisor in SSMS

Clicking on “Analyze Query in Database Engine Tuning Advisor” will launch the graphical user interface of Database Engine Tuning Advisor as shown below in Figure 4 and imports the selected queries from SSMS as the workload for this tuning session.

Database Engine Tuning Advisor launched from SSMS using workload queries
Figure 4 – Database Engine Tuning Advisor launched from SSMS using workload queries

Now go to the “Tuning Options” tab as shown in Figure 5 to specify different tuning options to get recommendations on indexes or partitioning, etc. Once you have selected the appropriate options, you can click on the “Start Analysis” icon on the toolbar to let Database Engine Tuning Advisor start analyzing your provided workload and provide recommendations on completion.

Specify different tuning options and start analysis
Figure 5 – Specify different tuning options and start analysis

The result of the analysis can be seen in figure 6. If you remember, we created two heap tables and hence based on joins, Database Engine Tuning Advisor recommends creating cluster indexes on these heap tables. It also provides a recommendation to create a couple of other non-clustered indexes to improve the performance of the queries we have provided as part of the workload. As we chose to get recommendations for partitioning also, Database Engine Tuning Advisor provides recommendations for partitioning function, scheme and what indexes can be created on this partitioning scheme for better performance.

Tuning Analysis Results
Figure 6 – Tuning Analysis Results

As discussed earlier, Database Engine Tuning Advisor also provides T-SQL scripts to quickly implement the recommendation as well; just click on the hyperlinks in the recommendation table and you will see the implementation script as seen in figure 7 for one clustered index on the SalesOrderDetail table.

Recommendation comes as script for quicker implementation
Figure 7 – Recommendation comes as script for quicker implementation

To give you different perspectives and confidence to implement recommendations, Database Engine Tuning Advisor includes several reports, as seen in figure 8. With these reports you can find out the cost and frequency of queries from the provided workload, or the relationships between queries and the indexes they reference, etc.

Tuning Analysis result reports
Figure 8 – Tuning Analysis result reports

For example, as seen in figure 9, we can see the expected performance improvement of each of the queries from workload provided when I used “Statement cost report”.

"Statement cost report" example of Tuning Analysis
Figure 9 – “Statement cost report” example of Tuning Analysis

There are some reports (like Index detail report) to provide the effects of implementing proposed changes, including index usage, query distribution among tables, and query performance in the workload.

Please note, Database Engine Tuning Advisor makes the best effort in providing recommendations based on workload provided but that might not be best fit as-is in all scenarios as it is only based on workload provided. Though these recommendations provide a good starting point to start optimizing the performance; please consider doing thorough testing in a test environment before implementing it in production. At a bare minimum, you might have to change the names of these objects from its default random generated names to something aligning with the naming convention your organization follows.

Conclusion

In this article of the series, I discussed the Database Engine Tuning Advisor utility and how it helps you to get recommendations based on your workload for performance optimization. I demonstrated step by step how you can use the Graphical User Interface of Database Engine Tuning Advisor for SQL queries as input. In my next article, I will demonstrate how you can use the Command Line Interface of Database Engine Tuning Advisor and how you can use SQL Server Profiler to capture and trace queries for optimization with Database Engine Tuning Advisor.

Resources

Database Engine Tuning Advisor Features

Start and Use the Database Engine Tuning Advisor

See all articles by Arshad Ali

Arshad Ali
Arshad Ali
Arshad Ali works with Microsoft India R&D Pvt Ltd. He has 8+ years of experience, mostly on Microsoft Technologies. Most recently, as a SQL Developer and BI Developer he has been working on a Data Warehousing project. Arshad is an MCSD, MCITP: Business Intelligence, MCITP: Database Developer 2008 and MCITP: Database Administrator 2008 certified and has presented at several technical events including SQL-School. On an educational front, he has an MCA (Master in Computer Applications) and MBA-IT. Disclaimer : I work for Microsoft and help people and businesses make better use of technology to realize their full potential. The opinions mentioned herein are solely mine and do not reflect those of my current employer or previous employers.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles