Microsoft SQL Server 2012 Bible

A Comprehensive Indexing Strategy

An index strategy deals with the overall application rather than fixing isolated problems to the detriment of the whole.

Identifying Key Queries

Analyzing a full query workload, which includes a couple of days of operations and nightly or weekend workloads, can likely reveal that although there may be a few hundred distinct queries, the majority of the CPU time is spent on the top handful of queries. I've tuned systems where 95 percent of the CPU time was spent on only five queries. Those top queries demand flat-out performance, whereas the other queries might afford a bookmark lookup.

To identify those top queries, follow these steps:

1. Create a profiler trace to capture all queries or stored procedures:

Profiler Event: T-SQL SQL:StmtCompleted and RPC:Completed

Profiler Columns: TextData, ApplicationName, CPU, Reads, Writes, Duration, SPID, EndTime, DatabaseName, and RowCounts.

Do NOT filter the trace to capture only long-running queries. (A common suggestion is to set the filter to capture only queries with a duration > 1 sec.) Every query must be captured.

2. Test the trace definition using Profiler for a few moments; then stop the trace. Be sure to filter out applications or databases not being analyzed.

3. In the trace properties, add a stop time to the trace definition (so it can capture a full day's and night's workload), and set up the trace to write to a file.

4. Generate a trace script using File → Export → Script Trace Definition ...