Database Reference
In-Depth Information
■
Note
We will discuss extended events in greater detail in Chapter 28, “extended events.”
SQL Server tracks execution statistics for queries and exposes them via
sys.dm_exec_query_stats
DMV.
Querying this DMV is, perhaps, the easiest way to find the most expensive queries in the system. Listing 6-15 shows an
example of a query that returns information about the 50 most expensive queries in a system in terms of average I/O
per execution.
Listing 6-15.
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]
,qs.execution_count as [Exec Cnt]
,(qs.total_logical_reads + qs.total_logical_writes)
/ qs.execution_count as [Avg IO]
,qp.query_plan as [Plan]
,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 Elps Time]
,qs.last_elapsed_time/1000 as [Last Elps Time]
,qs.creation_time as [Compile Time]
,qs.last_execution_time as [Last Exec Time]
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)
The query result, shown in Figure
6-10
, helps you quickly find optimization targets in the system. In our example,
the second query in the result set executes very often, which makes it an ideal candidate for optimization, even
though it is not the most expensive query in the system. Obviously, you can sort the result by other criteria, such as the
number of executions, execution time, and so on.
