Using the Database Engine Tuning Advisor
Among the principal methods that SQL Server
uses to maintain and control queries are indexes and the statistics on
those indexes. Taking direct control over these indexes yourself can
take a lot of time and effort and require education and discovery.
Fortunately, SQL Server has a tool that will help you create
indexes—the Database Engine Tuning Advisor (DTA). The DTA can be run a
number of different ways to help you. You can capture a trace data set
and send it to the DTA for analysis. You can pass a query from the
Query Editor inside Management Studio straight into the DTA for
analysis. Now, with SQL Server 2012, you can use the query plans that
exist in the plan cache on a server as the base data for the DTA.
To see it in action, we’ll run the DTA against the query used previously (salesquery.sql in the download):
SELECT p.[Name],
soh.OrderDate,
soh.AccountNumber,
sod.OrderQty,
sod.UnitPrice
FROM Sales.SalesOrderHeader AS soh
JOIN Sales.SalesOrderDetail AS sod
ON soh.SalesOrderID = sod.SalesOrderID
JOIN Production.Product AS p
ON sod.ProductID = p.ProductID
WHERE p.[Name] LIKE 'LL%'
AND soh.OrderDate BETWEEN '1/1/2008' AND '1/6/2008' ;
In the query window, right-click the query
text, and select Analyze Query in Database Engine Tuning Advisor from
the context menu. This will open the DTA in a window that looks like Figure 15.
Figure 15. Database Engine Tuning Advisor’s General tab
Figure 15
shows a simple example. With more complicated examples that include
data from other databases that are being run based on the workload
supplied by a trace, you would have more databases selected. In this
case, it’s a single query against a single database.
From here, you can move to the Tuning Options
tab to adjust the possible changes being proposed by the DTA. For this
example, we’ll let the default values work. On the DTA toolbar, click
the Start Analysis button, and a new tab showing the progress of the
analysis will open. Once the analysis completes, another new tab
showing recommendations from the DTA will open. Figure 16 shows the recommendations for the query.
Figure 16. Database Engine Tuning Advisor Recommendations tab
For the query supplied, the DTA ran
through the information in the query and the information in the tables,
and it arrived at three indexes that it thinks will achieve a 70
percent increase in speed. You should take any and all recommendations
from the DTA and test them prior to implementing them in production. It
is not always right.
