1044
Part VIII: Performance Tuning and Optimization
The DMV sys.dm_exec_query_stats provides a statement-level view of cached SQL state-
ments. This DMV is a wonderful source of information as to how often statements execute,
and how expensive they are. (This is one of the fi rst DMVs I will look at when investigating
performance problems.)In addition to providing wonderful statement-level cached plan statistics, sys.dm_exec_query_
stats provides two fi elds that enable you to determine statements and execution plans that
are approximately the same. Use the query_hash column in this DMV to identity cached state-
ments that are of a similar shape. If many statements have the same query_hash, consider
parameterizing these statements if possible so that the usage of literal values in the queries do
not bloat the plan cache and can be reused. Storing and keeping track of the query_plan_hash
is useful because it enables you to see when a plan changes for a specifi c cached statement.
Changes for well-known plans can be a cause for concern, which you should investigate.Plan Lifetime
All the query plans in memory stay in the Plan Cache until SQL Server experiences memory
pressure. SQL Server begins to age-out query plans based on how long it has been since
they were last executed and how complex they are. A complex query plan with a high com-
pilation cost stays in memory longer than a simple query with a lower compilation cost.Query Plan Execution
When the Query Processor needs to execute a query, it fi rst checks to see if the query can
reuse an execution plan already in the Plan Cache. It’s much faster to fi nd and reuse a
Query Execution Plan than to generate a new plan, even though that option is not always
the best solution.You should always schema-qualify objects in your SQL statements. Doing so aids SQL Server to perform the initial
object name to objectID lookup as well as prevent a second Plan Cache lookup with compile lock when checking to
see if the cached procedure plan can be reused. Fully qualifi ed objects provide more opportunities for plan reuse.If the query qualifi es for reuse, the Query Processor seeks the Plan Cache for an identical
SQL statement (minus parameters). If the Query Processor fi nds a match, then an Execution
Context holds the data and variables for that instance of the execution plan. This enables
multiple users to simultaneously execute the same Query Execution PlanQuery Recompiles
Because the Execution Plan is based on a combination of the SQL statement, the indexes,
and the data, a number of changes might cause SQL Server to decide that the execution
plan is no longer valid, or no longer the optimal plan. SQL Server then marks the Execution
Plan as invalid and generates a new Query Execution Plan the next time it executes.c46.indd 1044c46.indd 1044 7/31/2012 10:22:58 AM7/31/2012 10:22:58 AM
http://www.it-ebooks.info