基于使用从这篇文章中获得的这段非常有用的 SQL 来获取查询执行统计信息最常执行的存储过程 - 代码日志 https://stackoverflow.com/questions/1942726/most-executed-stored-procedure/1942752#1942752:
SELECT TOP 100
qt.TEXT AS 'SP Name',
SUBSTRING(qt.text, qs.statement_start_offset/2, CASE WHEN (qs.statement_end_offset = -1) THEN LEN(qt.text) ELSE (qs.statement_end_offset - qs.statement_start_offset)/2 END) AS actual_query,
qs.execution_count AS 'Execution Count',
qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',
qs.total_worker_time AS 'TotalWorkerTime',
qs.total_physical_reads AS 'PhysicalReads',
qs.creation_time 'CreationTime',
qs.execution_count/DATEDIFF(Second, qs.creation_time, GETDATE()) AS 'Calls/Second'
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.dbid = (SELECT dbid
FROM sys.sysdatabases
WHERE name = 'BSP')
ORDER BY qs.total_worker_time/qs.execution_count DESC
我该如何彻底清除这些执行统计数据并从头开始?
这将特别有用,因为开发错误和测试导致例程通常被调用大量次,从而使真实的使用级别无效。
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)