SQL Server - Using the Database Engine Tuning Advisor

.NET Developers often have to create their own SQL Server databases and be familiar with database schemas, tables, and the use of T-SQL stored procedures and joins. However, a weak point is often how to optimize the database with indexes. We summarize the use of the Database Engine Tuning Advisor in creating statistics and indexes based on an existing workload

View the YouTube Video Tutorial on SQL Server Database Tuning Advisor:

Microsoft SQL Server Database Engine Tuning Advisor helps you select and create an optimal set of indexes, indexed views, and partitions without requiring an expert understanding of the structure of the database or the internals of Microsoft SQL Server.

It analyzes a workload and the physical implementation of one or more databases. A workload is a set of Transact-SQL statements that execute against a database or databases that you want to tune. It can use trace files, trace tables, or Transact-SQL scripts as workload input when tuning databases. You can create Transact-SQL script workloads with the Query Editor in SQL Server Management Studio. You can create trace file and trace table workloads by using the Tuning Template in SQL Server Profiler. In this article, we examine only the use of SQL script workloads using the stock Northwind database.

After analyzing a workload, Database Engine Tuning Advisor can recommend that you add, remove, or modify physical design structures in your databases. The advisor can also recommend what statistics should be collected to back up physical design structures. The physical design structures include clustered indexes, nonclustered indexes, indexed views, and partitioning. The Advisor recommends a set of physical design structures that reduces the query optimizers estimated cost of the workload, and provides the SQL scripts to easily install these into your database.

With the Northwind database as an example, what we do is take the "stock" stored procedures that come with the database, remove the stored procedure related code, declare and populate the input parameters, and create a set of SQL statements that represent a workload. We then load this into the Tuning Advisor and execute it to create the recommended statistics and indexes based on the supplied workload. The more complete your workload is, the better the statistics and indexes will be.

Finally we execute these recommended scripts to tune our database. A partial example workload produced from the existing stored procedures in the Northwind database follows:

DECLARE @CustomerID nchar(5) = 'ALFKI'
SELECT ProductName, Total=SUM(Quantity)
FROM Products P, [Order Details] OD, Orders O, Customers C
WHERE C.CustomerID = @CustomerID
AND C.CustomerID = O.CustomerID AND O.OrderID = OD.OrderID AND OD.ProductID = P.ProductID
GROUP BY ProductName

DECLARE @OrderID int = 10255
SELECT ProductName,
UnitPrice=ROUND(Od.UnitPrice, 2),
Discount=CONVERT(int, Discount * 100),
ExtendedPrice=ROUND(CONVERT(money, Quantity * (1 - Discount) * Od.UnitPrice), 2)
FROM Products P, [Order Details] Od
WHERE Od.ProductID = P.ProductID and Od.OrderID = @OrderID

FROM Orders
WHERE CustomerID = @CustomerID

DECLARE @Beginning_Date DateTime = '1996-01-01'
DECLARE @Ending_Date DateTime = '1998-12-30'
SELECT Employees.Country, Employees.LastName, Employees.FirstName, Orders.ShippedDate, Orders.OrderID, "Order Subtotals".Subtotal AS SaleAmount
(Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID)
ON Employees.EmployeeID = Orders.EmployeeID
WHERE Orders.ShippedDate Between @Beginning_Date And @Ending_Date

This is what the Tuning Advisor Wizard looks like when configured to use the Nortwind Database and a custom script workload that you have saved on disk:

When the wizard has completed, the results look as follows:

At the right side of the wizard pane, there will be a hyperlink that brings up the T-SQL statements to create each set of statistics and indexes. You can press a button to copy this SQL to the clipboard, paste it into the Query Editor window, and execute it against the database.

The analysis that SQL Server Database Tuning Advisor does is much more complete than the typical developer has knowledge to perform.
You can view the Youtube tutorial video about the process above.

By Peter Bromberg   Popularity  (6925 Views)