如何清除 SQL Server 2005/2008 中的查询执行统计信息
基于使用从这篇文章中获得的这段非常有用的 SQL 来获取查询执行统计信息 执行次数最多的存储程序 - 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
FROM sys.sysdatabases
WHERE name = 'BSP')
ORDER BY qs.total_worker_time/qs.execution_count DESC
我如何完全清除这些执行统计信息并从头开始？
这将特别有用，因为开发错误和测试导致例程通常被调用大量次，从而使真实的使用级别无效。
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
FROM 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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
如果您使用 Microsoft SQL Server Management Studio，对我有用的是“查询”→“重置客户端统计信息”。
我会谨慎地告诉人们从接受的答案中执行 DBCC 命令，对我来说，由于政策限制，它不起作用（这可能是最好的）。
If you use Microsoft SQL Server Management Studio, what worked for me was "Query" → "Reset Client Statistics".
I'd be cautious telling people to execute the
DBCCcommands from the accepted answer, and for me due to policy restrictions it did not work (which is probably for the best).