开发者

How to Clear down Query Execution Statistics in SQL Server 2005/2008

开发者 https://www.devze.com 2022-12-20 07:49 出处:网络
Based on getting Query Execution Statistics using this extremely useful piece of SQL obtained from this post Most Executed Stored Procedure - Stack Overflow:

Based on getting Query Execution Statistics using this extremely useful piece of SQL obtained from this post Most Executed Stored Procedure - Stack Overflow:

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
                FRO开发者_开发问答M sys.sysdatabases
               WHERE name = 'BSP')
ORDER BY qs.total_worker_time/qs.execution_count DESC

How would I completely clear out these execution statistics and start from scratch?

This would be particularly useful as development bugs and testing have caused routines to be called an usually large number of times thus invaliding the true usage levels.


DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号