Database Reference
In-Depth Information
To learn how you can use the Database Engine Tuning Advisor to get index recommendations on a set of
problematic queries, say you have a simple query that is called rather frequently. Because of the frequency, you want a
quick turnaround for some tuning. This is the query:
SELECT soh.DueDate,
soh.CustomerID,
soh.Status
FROM Sales.SalesOrderHeader AS soh
WHERE soh.DueDate BETWEEN '1/1/2008' AND '2/1/2008';
To analyze the query, right-click it in the query window and select Analyze Query in the Database Engine Tuning
Advisor. The advisor opens with a window where you can change the session name to something meaningful. In
this case, I chose Report Query Round 1 - 1/16/2014. The database and tables don't need to be edited. The first tab,
General, will look like Figure
10-6
when you're done.
Because this query is important and tuning it is extremely critical to the business, I'm going to change some
settings on the Tuning Options tab to try to maximize the possible suggestions. For the purposes of the example,
I'm going to let the Database Engine Tuning Advisor run for the default of one hour, but for bigger loads or
more complex queries, you might want to consider giving the system more time. I'm going to select the Include
Filtered Indexes check box so that if a filtered index will help, it can be considered. I'm also going to switch the
Partitioning Strategy to Employ setting from No Partitioning to Full Partitioning. Finally, I'm going to allow the
Database Engine Tuning Advisor to come up with structural changes if it can find any that will help by switching
from Keep All Existing PDS to Do Not Keep Any Existing PDS. Once completed, the Tuning Options tab will look
like Figure
10-7
.
