Database Engine Tuning Advisor - SQL Server Query Performance Tuning - page 172

Figure 10-11. A successful tuning session applied
After you generate recommendations, you may want to, instead of applying them on the spot, save the
T-SQL statements to a file and accumulate a series of changes for release to your production environment during
scheduled deployment windows. Also, just taking the defaults, you'll end up with a lot of indexes named something
like this: _dta_index_SalesOrderHeader_5_1266103551__K4_6_11. That's not terribly clear, so saving the changes to
T-SQL will also allow you to make your changes more human readable. Remember that applying indexes to tables,
especially large tables, can cause a performance impact to processes actively running on the system while the index
is being created.
Although getting index suggestions one at a time is nice, it would be better to be able to get large swaths of the
database checked all at once. That's where tuning a trace workload comes in.
Tuning a Trace Workload
Capturing a trace from the real-world queries that are running against a production server is a way to feed meaningful
data to the Database Engine Tuning Advisor. (Capturing traces will be covered in Chapter 17.) The easiest way to
define a trace for use in the Database Engine Tuning Advisor is to implement the trace using the Tuning template.
Start the trace on the system you need to tune. I generated an artificial load by running queries in a loop from the
PowerShell sqlps.exe command prompt. This is the PowerShell command prompt with the SQL Server configuration
settings. It gets installed with SQL Server.
To find something interesting, I'm going to create one stored procedure with an obvious tuning issue.
CREATE PROCEDURE dbo.uspProductSize
AS
SELECT p.ProductID,
p.Size
FROM Production.Product AS p
WHERE p.Size = '62';