Database Reference
In-Depth Information
Figure 6-10.
Sys.dm_exec_query_stats results
Unfortunately,
sys.dm_exec_query_stats
returns information only about queries with execution plans cached.
As a result, there is no statistics for the statements that use a statement-level recompile with
option (recompile)
.
Moreover,
execution_count
data can be misleading if a query was recently recompiled. You can correlate the
execution_count
and
creation_time
columns to detect the most frequently-executed queries.
■
Note
We will discuss plan Cache in greater detail in Chapter 26, “plan Caching.”
Starting with SQL Server 2008, there is another DMV
sys.dm_exec_procedure_stats
, which returns similar
information about stored procedures that have execution plans cached. Listing 6-16 shows a query that returns a list
of the 50 most expensive procedures in terms of average I/O. Figure
6-11
shows the results of the query on one of the
production servers.
Listing 6-16.
Using sys.dm_exec_procedure_stats
select top 50
s.name + '.' + p.name as [Procedure]
,qp.query_plan as [Plan]
,(ps.total_logical_reads + ps.total_logical_writes) /
ps.execution_count as [Avg IO]
,ps.execution_count as [Exec Cnt]
,ps.cached_time as [Cached]
,ps.last_execution_time as [Last Exec Time]
,ps.total_logical_reads as [Total Reads]
,ps.last_logical_reads as [Last Reads]
,ps.total_logical_writes as [Total Writes]
,ps.last_logical_writes as [Last Writes]
,ps.total_worker_time as [Total Worker Time]
,ps.last_worker_time as [Last Worker Time]
,ps.total_elapsed_time as [Total Elapsed Time]
,ps.last_elapsed_time as [Last Elapsed Time]
