page contents

About the Post

Author Information

HOW TO: Use the Database Engine Tuning Advisor in Microsoft SQL Server

Before we begin, I want to provide a little background on this tool.

Database Engine Tuning Advisor (DTA) is a component of SQL Server Management Studio, which is an integral part of Microsoft SQL Server, the database management application. This tool is used for improving query performances by examining the way queries are processed and recommending enhancements by specifying indexes, views and partitioning on the database. It replaced the Index Tuning Advisor in Microsoft SQL Server 2000. The Database Engine Tuning Advisor is installed along with other components of Management Studio when you install SQL Server. In case it does not get installed, you can choose to add it after running the setup again. You will need to modify the Management Tools -Complete to get this feature installed.

Basically, it is used to configure a database so that queries will process more efficiently. It does this task by modifying indexed views, indexes and partitions, which can make queries perform faster because you are fine-tuning the physical design of the database.

This tool was introduced by Microsoft with SQL 2005. DTA has made significant advancements over the predecessor and the main purpose is much the same as the Index Tuning Wizard. DTA does seem to produce more reliable results and appears to be more efficient at doing it. Unfortunately, there is still the possibility of getting some bad recommendations from this tool, so please proceed with caution. And yes, the bigger issue is that there was blanket acceptance of all indexes. You can learn more about this tool here. This tool is available in Microsoft SQL Server 2005 and newer releases.

So, how do you use this tool? Good question.

Instructions:

Navigate to the Database Engine Tuning Advisor by clicking “Start,” “All Programs,” “Microsoft SQL Server 2008,” “Performance Tools,” then “Database Engine Tuning Advisor.”

Click “Connect” in the “Connect to Server” window.

  1. Open SQL Server Management Studio (SSMS) and save a query. I grabbed one from a sample off Microsoft TechNet. (As seen below)

 

USE AdventureWorks2008R2;

GO

SELECT pp.FirstName, pp.LastName, e.NationalIDNumber

FROM HumanResources.Employee AS e WITH (INDEX(AK_Employee_NationalIDNumber))

JOIN Person.Person AS pp on e.BusinessEntityID = pp.BusinessEntityID

WHERE LastName =
'Johnson';

GO 

-- Force a table scan by using INDEX = 0.

USE AdventureWorks2008R2;

GO

SELECT pp.LastName, pp.FirstName, e.JobTitle

FROM HumanResources.Employee AS e WITH (INDEX = 0)
JOIN Person.Person AS pp

ON e.BusinessEntityID = pp.BusinessEntityID

WHERE LastName =
'Johnson';

GO

NOTE: You can download the Adventureworks Sample databases from here.

Follow these simple steps to do get your query analyzed:

  • Click “Start”
  • Go to “All Programs”
  • Select “Microsoft SQL Server 2008 R2”
  • Choose “SQL Server Management Studio”
  • Click “New Query” to open the Query Editor.
  • Paste the SQL script copied from Microsoft TechNet into the Query Editor, then save as “Test_DTA.sql” in a familiar directory.

There is also a short cut to this process. You can open your query in SSMS and right-click in the Window to select “Analyze Query in Database Engine Tuning Advisor” (seen below)

Then it will open in DTA as seen below:

Please see screenshot listed below for assistance with these steps:

  • In the “Session name” field enter a unique name, such as “Session1.”
  • Choose “File” as the type of Workload and then navigate to the “Test_DTA.sql” script you saved earlier.
  • Choose “AdventureWorks 2008R2” as the “Database for Workload Analysis,
  • Select it again in “Select Databases and tables to tune.”
  • Check “Save tuning log.”
  • Click the “Tuning Options” tab to review the defaults for future reference. (This will not be used at this time)
  • Navigate to the toolbar and click “Start Analysis.”

Wait for the analysis to complete, which you can observe on the “Progress” tab.

Review the results in “Recommendations” tab to view the “Index Recommendations” and “Partition Recommendations” columns. Carefully examine those indexes or partitions that DTA recommends that should be added or deleted.

Deselect any recommendations that you don’t want to keep. Click “Apply Recommendations” or “Save Recommendations” under “Actions” for changes that you want to implement.

Now, run the newly saved script in a test environment to verify results before running the new script in production.

There is also a good article on MSDN about this subject called Tutorial: Database Engine Tuning Advisor

Tags: , , , , ,

Comments are closed.

Copy Protected by Chetan's WP-Copyprotect.