Microsoft® SQL Server® 2012 Bible

1032

Part VII: Performance Tuning and Optimization

■ (^) If the predicate includes a function, such as a string function, a scan is required
so that every row can be evaluated with the function before the fi nal criteria is
applied to the function output.
SELECT WorkOrderID, StartDate
FROM Production.WorkOrder
WHERE DateName(dw, StartDate) = 'Monday';
SQL Server 2008 does include some optimizations that can avoid the scan when working
with the Date data type when conversions are included in the predicate
The type of access (index scan versus index seek) not only impacts the performance of reading data from the single
table, but it also impacts join performance. The type of join chosen by SQL Server depends on whether the data is
ordered (among other things). Merge joins require ordered result sets as inputs. If the optimizer determines that a
merge join is the most effi cient join method to satisfy a query, a sort operation may be required to sort the inputs. In
such a case, a memory grant is required and potentially tempdb space to store the intermediate result sets. This is
another example of why indexing is important.

A Comprehensive Indexing Strategy

An index strategy deals with the overall application rather than fi xing 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 fi ve queries. Those top queries

demand fl at-out performance, whereas the other queries might afford a bookmark lookup.

To identify those top queries, follow these steps:

1. Create a profi ler trace to capture all queries or stored procedures:
   Profi ler Event: T-SQL SQL:StmtCompleted and RPC:Completed
   Profi ler Columns: TextData, ApplicationName, CPU, Reads, Writes,
   Duration, SPID, EndTime, DatabaseName, and RowCounts.
   Do NOT fi lter the trace to capture only long-running queries. (A common sugges-
   tion is to set the fi lter to capture only queries with a duration > 1 sec.) Every query
   must be captured.


2. Test the trace defi nition using Profi ler for a few moments; then stop the trace. Be
   sure to fi lter out applications or databases not being analyzed.


3. In the trace properties, add a stop time to the trace defi nition (so it can capture a
   full day’s and night’s workload), and set up the trace to write to a fi le.

c45.indd 1032c45.indd 1032 7/31/2012 10:16:43 AM7/31/2012 10:16:43 AM

http://www.it-ebooks.info