Database Reference
In-Depth Information
Unfortunately,
sys.dm_exec_query_stats
does not return any information for the queries, which do not have
compiled plans cached. Usually, this is not an issue because our optimization targets are not only resource intensive, but
they also frequently executed queries. Plans of those queries usually stay in cache due to their frequent re-use. However,
SQL Server does not cache plans in the case of a statement-level recompile, therefore
sys.dm_exec_query_stats
misses them. You should use SQL Trace and/or Extended events to capture them. I usually start with queries from the
sys.dm_exec_query_stats
function output and crosscheck the optimization targets with Extended Events later.
■
Query plans can be removed from the cache and, therefore, not included in the
sys.dm_exec_query_stats
results in cases of a SQl Server restart, memory pressure, and recompilations due to a statistics update and in a few
other cases. it is beneficial to analyze the
creation_time
and
last_execution_time
columns in addition to the number
of executions.
Note
SQL Server 2008 and above provides stored procedure-level execution statistics with the
sys.dm_exec_procedure_stats
view. It provides similar metrics with
sys.dm_exec_query_stats
, and it can be used
to determine the most resource-intensive stored procedures in the system. Listing 27-6 shows a query that returns the
50 most I/O intensive stored procedures, which have plan cached at the moment of execution.
Listing 27-6.
Using
sys.dm_exec_procedure_stats
select top 50
db_name(ps.database_id) as [DB]
,object_name(ps.object_id, ps.database_id) as [Proc Name]
,ps.type_desc as [Type]
,qp.query_plan as [Plan]
,ps.execution_count as [Exec Count]
,(ps.total_logical_reads + ps.total_logical_writes) /
ps.execution_count as [Avg IO]
,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 / 1000 as [Total Elapsed Time]
,ps.last_elapsed_time / 1000 as [Last Elapsed Time]
,ps.last_execution_time as [Last Exec Time]
from
sys.dm_exec_procedure_stats ps with (nolock)
cross apply sys.dm_exec_query_plan(ps.plan_handle) qp
order by
[Avg IO] desc
option (recompile)
There are plenty of tools available on the market to help you automate the data collection and analysis process
including the SQL Server Management Data Warehouse. All of them help you to achieve the same goal and find
optimization targets in the system.
