Plan Caching - Pro SQL Server Internals - page 507

The sys.dm_exec_query_stats , sys.dm_exec_procedure_stats , and sys.dm_exec_trigger_stats views
provide aggregated performance statistics for queries, procedures, and triggers that have cached plans. They return
one row for every cached plan per object, as long as the plan stays in the cache. These views are extremely useful
during performance troubleshooting. We will discuss their use in depth in the next chapter.
Sys.dm_exec_query_stats is supported in SQL Server 2005 and above. Sys.dm_exec_procedure_stats and
sys.dm_exec_trigger_stats were introduced in SQL Server 2008.

■ You can find more information about execution-related DMOs at: http://technet.microsoft.com/en-us/
library/ms188068.aspx .

Note

Summary
Query Optimization is an expensive process, which increases CPU load on busy systems. SQL Server reduces such
load by caching plans in a special part of memory called plan cache. It includes plans for T-SQL objects, such as stored
procedures, triggers, and user-defined functions; ad-hoc queries and batches, and a few other plan-related entities.
SQL Server reuses plans for ad-hoc queries and batches only when there is a character-for-character match of the
query/batch texts. Moreover, different SET options and/or reference to unqualified objects could prevent plan reuse.
Caching plans for ad-hoc queries can significantly increase plan cache memory usage. It is recommended that
you enable the server-side “Optimize for Ad-hoc Workloads” configuration setting if you are using SQL Server 2008
and above.
SQL Server sniffs parameters and generates and caches plans, which are optimal for parameter values at the time
of compilation. In cases of uneven data distribution, this could lead to performance issues when cached plans are not
optimal for the typically submitted parameter values. You can address such issues with a statement-level recompile or
OPTIMIZE FOR query hints.
You can specify hints directly in queries. Alternatively, you can use plan guides, which allow you to apply hints or
force specific execution plans without changing the query text.
Cached plans should be valid for every possible combination of parameters. This can lead to suboptimal
plans when a query has OR conditions to support optional parameter values. You can address such issues with a
statement-level recompile, or by building SQL dynamically and omitting OR conditions.