The Database Engine Tuning Advisor (DTA) is a new tool in SQL Server 2005 that enables you to tune databases and improve the performances of your queries. DTA examines how queries are processed in the databases you specify and then it recommends how you can improve performance by suggesting the creation of indexes and statistics. It replaces the Index Tuning Wizard from Microsoft SQL Server 2000.
You can use DTA to tune a database by using workload (trace files) that were previously created with the profiler or you can tune a Query directly in Management Studio.
A workload is a set of Transact-SQL statements that execute against the database that you want to tune. The Database Engine Tuning Advisor use trace files, trace tables, or Transact-SQL scripts as workload input when tuning databases.
In this article, we will create a few tables in the Adventureworks sample database. Run the following script:
USE AdventureWorks
GO
SELECT * INTO DTA_Individual
FROM Sales.Individual
GO
SELECT * INTO DTA_Customer
FROM Sales.Customer
GO
SELECT * INTO DTA_CustomerAddress
FROM Sales.CustomerAddress
GO
SELECT * INTO DTA_Address
FROM Person.Address
GO
SELECT * INTO DTA_Vendor
FROM Purchasing.Vendor
GO
SELECT * INTO DTA_VendorContact
FROM Purchasing.VendorContact
GO
SELECT * INTO DTA_Employee
FROM HumanResources.Employee
GO
SELECT * INTO DTA_SalesOrderHeader
FROM Sales.SalesOrderHeader
GO
SELECT * INTO DTA_SalesOrderDetail
FROM Sales.SalesOrderDetail
GO
SELECT * INTO DTA_Product
FROM Production.Product
GO
Now we have tables to work with.
Creating a Trace file:
To run SQL Server Profiler, on the Start menu, point to All Programs, Microsoft SQL Server 2005, Performance Tools, and then click SQL Server Profiler. Or, you can to start the profiler from the tools menu in Management Studio.
Once the SQL Profiler is started, you need to create a new trace. “Select New Trace…” from the file menu:
After you have connected to the server, the Trace Properties window appears:
The DTA assumes that the workload trace file is a rollover file.
The captured workload information can be stored in a file (recommended for tuning) or in a table. I recommend that you use the SQL Server Profiler Tuning template for capturing workloads for DTA.
If you want to use your own template, ensure that the following trace events are captured for the version of SQL Server that you are using.
SQL Server 2005:
SQL Server 2000:
The best time to create a trace file is when actual users are on the server doing real work. Not you running a few queries right and left. A workload should be representative of your typical load on the server. Starting the profiler WILL have an effect on the performance of the server. I also recommend increasing the max file size, since the profiler will create a new file once it reaches the limit. I normally increase it to 100MB.
Running the Trace:
Click on the run button and go back to Management Studio and run the following script:
USE AdventureWorks
GO
SELECT City, NumberOfOrders = count(*)
FROM DTA_Individual i JOIN DTA_Customer c ON i.CustomerID = c.CustomerID AND c.CustomerType = 'I'
JOIN DTA_CustomerAddress ca ON ca.CustomerID = c.CustomerID
JOIN DTA_Address a ON a.AddressID = ca.AddressID
JOIN DTA_SalesOrderHeader sh ON c.CustomerID = sh.CustomerID
JOIN DTA_SalesOrderDetail sd ON sd.SalesOrderID = sh.SalesOrderID
JOIN DTA_Product p ON p.ProductID = sd.ProductID
WHERE StateProvinceID = (SELECT StateProvinceID FROM Person.StateProvince WHERE StateProvinceCode = 'CA')
GROUP BY City
ORDER BY 2 DESC
GO
SELECT i.CustomerID, c.ModifiedDate, City
FROM DTA_Individual i JOIN DTA_Customer c ON i.CustomerID = c.CustomerID AND c.CustomerType = 'I'
JOIN DTA_CustomerAddress ca ON ca.CustomerID = c.CustomerID
JOIN DTA_Address a ON a.AddressID = ca.AddressID
WHERE i.CustomerID = 13311
GO
SELECT i.CustomerID, c.TerritoryID, City
FROM DTA_Individual i JOIN DTA_Customer c ON i.CustomerID = c.CustomerID AND c.CustomerType = 'I'
JOIN DTA_CustomerAddress ca ON ca.CustomerID = c.CustomerID
JOIN DTA_Address a ON a.AddressID = ca.AddressID
WHERE StateProvinceID = (SELECT StateProvinceID FROM Person.StateProvince WHERE StateProvinceCode = 'CA')
ORDER BY i.CustomerID, City
GO
When you go back to the profiler, you will see everything that was sent to the server:
When you will be running this for real (on your production server), you have to let the trace run for a while; I let it run for 20-30 minutes. When you feel you have a good workload, you can stop the trace and quit the profiler.
Running the Database Tuning Advisor:
To begin, open the DTA, From the Windows Start menu, point to All Programs, point to Microsoft SQL Server 2005, point to Performance Tools, and then click Database Engine Tuning Advisor. In the Connect to Server dialog box, verify the default settings, and then click Connect.
By default, Database Engine Tuning Advisor opens to the configuration in the following illustration:
Before you can start the DTA, you need to select the trace file and select the database(s) to tune.
Click the Start Analysis button on the toolbar. While Database DTA is analyzing the workload, you can monitor the status on the Progress tab. When tuning is complete, the Recommendations tab is displayed.
After a couple of minutes (could take a while depending on the trace file size) the Recommendations tab is displayed and you can see the recommendations:
At this point, you have two choices; you can apply the recommendations directly or save them to a file. I personally save the recommendation to file because I like to see what suggestions were made and I try to understand them. You do this by clicking Save Recommendations on the Actions menu. You will have to execute the file in Management Studio to implement the changes.
For this demo, it is OK to apply them. If you apply the recommendation directly from the DTA, you will have more problems removing them afterwards.
What was created?
Once you have implemented the recommendations you can see what was created by looking in the Indexes and the Statistics level for a given table in the Object Explorer. All objects created by the DTA starts with “_dta”.
Cleaning up:
You can run this script to clean up our mess:
USE AdventureWorks
GO
DROP TABLE DTA_Individual
GO
DROP TABLE DTA_Customer
GO
DROP TABLE DTA_CustomerAddress
GO
DROP TABLE DTA_Address
GO
DROP TABLE DTA_Vendor
GO
DROP TABLE DTA_VendorContact
GO
DROP TABLE DTA_Employee GO
DROP TABLE DTA_SalesOrderHeader
GO
DROP TABLE DTA_SalesOrderDetail
GO
DROP TABLE DTA_Product
GO
Hope this was useful,
Eric Vaillancourt
http://www.sqlprof.com