如何确定 SQL Server 中正在编译的内容

在跟踪我的 SQL Server 2008 Std Edition 安装上的性能监视器时，我注意到每秒 SQL 编译数大约每五秒就会从 3 次激增到大约 50 次。

我们每秒的编译与批处理请求的比率也相对较高。我知道理想情况下这应该是 1/10 的比例，但我们现在的比例更像是 8/10。

该数据库支持具有大量应用程序的繁忙网站，因此很难确定导致过度编译的原因，尤其是 5 秒的峰值。几乎所有查询都是存储过程调用而不是嵌入式 SQL，并且我们拥有大量 (48GB) RAM。

有没有办法及时查看给定时刻当前正在编译哪些查询？如果是这样，我们可以找出是否有问题。

过去，当我不得不研究计划缓存/过度查询重新编译的问题时，我遵循了 Microsoft 白皮书中提供的指导“在 SQL Server 2008 中规划缓存” http://msdn.microsoft.com/en-us/library/ee343986%28v=sql.100%29.aspx我强烈建议阅读它，因为它涵盖了计划缓存、查询计划重用、重新编译的原因、识别重新编译和其他相关主题。

话虽如此，SQL Server Profiler（如果您将其作为客户端工具安装的一部分进行安装，则应位于 Microsoft SQL Server 2008 -> 性能工具下）公开了三个与查询编译直接相关的事件，这些事件可能对您有帮助：

• Cursor
  • 光标重新编译
• Performance
  • 用于查询编译的显示计划 XML
• Stored Procedure
  • SP：重新编译

您很可能正在使用存储过程，您只需要担心SP：重新编译 http://msdn.microsoft.com/en-us/library/ms187105(v=sql.100).aspx事件。每当重新编译存储过程、触发器或用户定义函数时，都会触发此事件。 TextData 列将显示导致语句重新编译的 tsql 语句的文本，EventSubClass 列将显示指示重新编译原因的代码。

SQL 2008 中 SP:Recompile 的事件子类代码

• 1 = 架构已更改
• 2 = 统计数据已更改
• 3 = 重新编译 DNR
• 4 = 设置选项已更改
• 5 = 临时表已更改
• 6 = 远程行集已更改
• 7 = 浏览权限已更改
• 8 = 查询通知环境已更改
• 9 = MPI 视图已更改
• 10 = 光标选项已更改
• 11 = 带重新编译选项

如果您监视以下 5 个事件，您将能够看到 SQL Server 上正在调用哪些存储过程和语句以及哪些正在触发重新编译：

• Store Procedures
  • SP：开始
  • SP:Stmt启动
  • SP：重新编译
  • SP：已完成
• Performance
  • 自动统计

我通常还设置 Profiler 跟踪来捕获这些事件的所有列。我想说的是，使用这 5 个事件设置跟踪，运行跟踪 30 到 60 秒，然后暂停它，然后您应该对导致重新编译的原因有一个很好的快照。

如果噪声太多，您可以开始向跟踪属性添加列过滤器以过滤输入/输出事件。例如，如果您发现大多数重新编译仅发生在一个数据库上，请在databaseID或databaseName列上设置列过滤器，以便仅针对该数据库运行的查询包含在跟踪中。

然后开始寻找重新编译查询的模式，并使用 Microsoft 的白皮书作为指导，了解为什么它们可能会触发重新编译。