Database Reference
In-Depth Information
Listing 27-5.
Using
sys.dm_exec_query_stats
select top 50
substring(qt.text, (qs.statement_start_offset/2)+1,
((
case qs.statement_end_offset
when -1 then datalength(qt.text)
else qs.statement_end_offset
end - qs.statement_start_offset)/2)+1) as SQL
,qp.query_plan as [Query Plan]
,qs.execution_count as [Exec Cnt]
,(qs.total_logical_reads + qs.total_logical_writes) /
qs.execution_count as [Avg IO]
,qs.total_logical_reads as [Total Reads]
,qs.last_logical_reads as [Last Reads]
,qs.total_logical_writes as [Total Writes]
,qs.last_logical_writes as [Last Writes]
,qs.total_worker_time as [Total Worker Time]
,qs.last_worker_time as [Last Worker Time]
,qs.total_elapsed_time / 1000 as [Total Elapsed Time]
,qs.last_elapsed_time / 1000 as [Last Elapsed Time]
,qs.last_execution_time as [Last Exec Time]
,qs.total_rows as [Total Rows]
,qs.last_rows as [Last Rows]
,qs.min_rows as [Min Rows]
,qs.max_rows as [Max Rows]
from
sys.dm_exec_query_stats qs with (nolock)
cross apply sys.dm_exec_sql_text(qs.sql_handle) qt
cross apply sys.dm_exec_query_plan(qs.plan_handle) qp
order by
[Avg IO] desc
option (recompile)
■
sys.dm_exec_query_stats
have slightly different columns in the result set in different versions of SQl Server.
the query in listing 27-5 works in SQl Server 2008r2 and above. You can remove the last four columns from the
SELECT
list to make it compatible with SQl Server 2005-2008.
Note
As you can see in Figure
27-8
, it allows you to define optimization targets easily based on resource usage and the
number of executions. For example, the second query in the result set is the best candidate for optimization due to
how frequently it runs.
Figure 27-8.
Query results
