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),
Quantity,
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
SELECT OrderID,
OrderDate,
RequiredDate,
ShippedDate
FROM Orders
WHERE CustomerID = @CustomerID
ORDER BY OrderID
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
FROM Employees INNER JOIN
(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.