监控游标，有哪些好的查询/脚本可以做到这一点？

Question

我需要向管理层提供证据，证明一组使用游标的现有存储过程是造成我们大部分性能问题的原因。有人可以指出我正确的方向来查找脚本和查询来完成此任务吗？比如，如何监控和测量游标等。使用SQL Server 2005。

谢谢。

========更新============

管理层需要弹药来带回第3方供应商，告诉他们以很少或没有成本的方式改变他们的程序。由于这些是影响我们会计系统的第三方进程，我没有任何方法可以先重写它们。

除了痕迹（已经在做）之外，我还能做其他事情吗？我发现使用 sys.dm_exec_cursors(0) 可以让我获得现有游标的快速列表。还有其他类似的事情吗？

Answer 1

因此，您进行了严格的测量并收集了执行时间和统计数据，表明问题程序是使用光标的程序，对吧？那么收集到的信息就是证明你的情况的绝佳论据。如果你没有...那么你怎么知道是光标？

首先查看 sys.dm_exec_query_stats 并收集按工作时间 (CPU)、运行时间（持续时间）和 I/O 划分的最昂贵的查询。这些应该足以指出罪魁祸首并查明问题是否确实是由光标引起的。

如果游标确实是一个问题，也有专门的 DMV，sys.dm_exec_cursors

例如，最昂贵的CPU频繁执行的语句：

select top(10) substring(Text,
  statement_start_offset/2, 
  (statement_end_offset-statement_start_offset)/2) as Statement
  , *
from sys.dm_exec_query_stats q
cross apply sys.dm_exec_sql_text(sql_handle)
where execution_count > 100
order by total_worker_time/execution_count desc

Answer 2

最好的事情（时间允许）是将一些过程重写为基于集合的语句，然后将两者与等待分析进行比较（http://technet.microsoft.com/en-us/library/cc966413.aspx 有一篇关于如何执行此类操作的很好的论文）。如果没有前后对比，你的对手可能只会说“基于集合不会更好:-)”

Answer 3

您可以运行 SQL Profiler 并捕获有问题的存储过程的跟踪（这将为您提供重要的测量值，例如读取数、CPU、持续时间）。

一个好主意是以其中一个为例，它很容易重写为基于集合的方法，运行它并捕获分析器跟踪。通过这种方式，您可以显示现实世界中的性能差异。

如果可能的话（即不在生产环境中），您应该在运行存储过程的每个版本之前清除执行计划和数据缓存，以进行公平的比较。

此外，您还可以获得游标版本和基于集合的版本的执行计划。

归根结底，底线统计数据不言而喻，因此对“之前”和“之后”进行比较将是有益的。

Answer 4

性能监视器（perfmon.exe）是实时分析SQL Server性能的优秀工具。