有趣的Server性能调优问题

Question

一个多星期以来，我一直在研究存储过程性能问题，并且与我在Stackoverflow 这里上的另一篇文章有关。让我给你一些背景资料。

我们有一个夜间进程，它运行并由一个存储过程启动，该存储过程调用许多其他存储过程。很多被调用的存储过程调用了其他的，我看过一些被调用的procs，里面有各种各样的复杂的东西，比如XML字符串处理，不必要的过度使用游标，NOLOCK提示过度使用，很少使用基于集合的处理等等--这个列表还在继续，非常可怕。

在我们的生产环境中，这个夜间的过程平均需要1:15才能运行。它有时需要两个小时的运行，这是不可接受的。我已经在与生产相同的硬件上创建了一个测试环境并运行了proc。我第一次运行时花了45分钟。如果我将数据库恢复到完全相同的位置并再次运行，则需要更长的时间:实际上，如果我多次重复此操作(恢复和重新运行)，则proc需要的时间逐渐延长，直到在大约2小时内达到稳定状态。这真的让我很困惑，因为我每次都会将数据库恢复到完全相同的位置。服务器上没有其他用户数据库。

我想了两条线索去调查：

1. 查询计划和参数欺骗
2. Tempdb

作为一项测试，我重新启动了Server，以清除缓存和tempdb，并使用相同的数据库恢复重新运行proc。程序花了45分钟。我重复了几次，以确保它是可重复的-再次，它花了45分钟一次。然后，我开始了几个测试，试图隔离SQL Server未重新启动时运行时间的令人费解的增加：

1. 使用重新编译运行初始存储过程
2. 在运行过程之前，执行DBCC FREEPROCCACHE清除过程缓存。
3. 在运行该过程之前，先执行检查点，然后是DBCC DROPCLEANBUFFERS，以确保缓存是空的和干净的。
4. 执行以下脚本以确保将所有存储过程标记为重新编译： 声明@proc_schema SYSNAME声明@proc_name SYSNAME为选择specific_schema声明prcCsr游标，从INFORMATION_SCHEMA.routines声明specific_name，其中routine_type = 'PROCEDURE‘打开prcCsr FETCH，然后从prcCsr获取到@proc_schema，@proc_name声明@stmt (最大)，而@@FETCH_STATUS =0 BEGIN SET @stmt = N'exec sp_recompile‘+ @proc_schema + '.’.‘+ @proc_name +’‘- PRINT @stmt -调试EXEC ( @stmt )从prcCsr到@proc_schema，@proc_name END

在所有上述测试中，使用相同的数据库还原运行该过程所需的时间越来越长。我现在真的不知所措，不知该怎么做。在这一点上查看代码是一种选择，但实际上需要3-6个月的时间才能将其优化，因为那里还有很多改进的余地。我真正感兴趣的是，为什么每次执行数据库恢复时，即使清除了过程和缓冲区缓存，proc执行时间也会变长？

我还研究了tempdb，并试图像我在其他堆栈溢出帖子中描述的那样清除其中的旧表，但是我无法手动清除由表变量手工创建的临时表，而且它们似乎不想自己消失(即使在离开它们24小时之后)。

对进一步测试有任何见解或建议将不胜感激。我正在Windows2003WindowsEnt上运行Server 2005 SP3 64位企业版.编辑集群。

你好，马克。

Answer 1

造成这种情况的一个原因是，如果流程泄漏XML文档。这将导致Server使用更多内存，其中部分可能会写入磁盘上的页文件，从而导致进程减速。

创建XML文档的代码如下所示：

EXEC sp_xml_preparedocument @idoc OUTPUT, @strXML

如果没有相应的信息，它就会泄漏：

EXEC sp_xml_removedocument @idoc

XML文档是存储在配置的Server内存之外的COM对象。即使将Server设置为最多使用5GB，泄漏XML文档也会增加内存使用量。

Answer 2

回顾到目前为止所有的帖子和相关的问题，听起来你最强大的线索就是这些tempdb对象背后的神秘。一些主要问题：

• 在重新开始之后，在运行进程之后，在tempdb中有多少对象？每次重新开始后都是同一个号码吗？
• 在“连续”运行之后，这些数字会增长吗？它们的生长速度相同吗？
• 你能确定它们是否占据空间吗？
• 因此，当您的进程每次连续运行时，您的tempdb文件都会增长吗？

我跟踪了链接，但没有发现任何参考讨论的实际问题。您可能想在Microsoft论坛这里上提出这个问题--它们可以很好地处理抽象的内容。(如果所有这些都失败了，您可以在MS技术支持下打开一个箱子。这可能需要几天的时间，但他们解决问题的可能性是很大的。如果是MS错误，他们会退还你的钱！)

您已经说过重写代码不是一种选择。但是，如果临时表滥用是一个因素，那么首先识别和重构代码的这些部分可能会有很大帮助。若要查找可能是哪一个，请在执行进程时运行。这种工作，唉，是主观的和高度迭代的(这意味着你几乎从来没有得到正确的计数器在第一次通过)。一些想法：

• 从跟踪SP开始:已启动，以跟踪正在调用的存储过程。
• showing可用于对数据进行分组；这很尴尬，我不知道如何仅用文本来描述它，但配置得当，您将得到一个Profiler显示，显示每个过程的次数。理想情况下，这将显示最常用的procs，您可以根据需要分析它们是否滥用临时表和重构。
• 如果没有任何跳转，您可以跟踪SP:StmtStarting，并对单个语句执行相同的操作。这里的问题是，在2+/-小时的意大利面代码运行中，您可能会耗尽磁盘空间，而分析100 MB的跟踪数据可能是一场噩梦。(提示:将其加载到表中，生成索引，然后仔细删除cruft。)同样，目标将是确定要重构的过度使用/滥用的临时表代码。

Answer 3

马克-

因此，完全重写这个过程可能需要3-6个月的时间，但这并不意味着您不能进行一些相对快速的性能优化。

有些例程我必须支持运行30hrs+，我会欣喜若狂，让他们在2小时内运行！！您对这些例程所做的优化与正常的OLTP数据库略有不同：

1. 捕获整个过程的跟踪，确保捕获SP:StmtCompleted和SQL:StmtCompleted事件。确保在持续时间(>10 to或其他什么)上设置一个过滤器，以消除所有快速、不重要的语句。
2. 将此跟踪拖到表中，并进行一些筛选/排序/分组，重点关注持续时间和读取。您可能会遇到以下两种情况之一： (A)程序大部分时间由少数个别查询/陈述负责(好消息) (B)许多类似的陈述每次都需要很短的时间，但加在一起就会有很长的时间。

在场景(A)中，只需将注意力集中在这些查询上即可。使用索引或其他标准技术优化它们。我强烈推荐Dan的"SQL调优“一书，因为它提供了一种强大的技术来优化查询，特别是复杂连接的混乱查询。

在场景(B)中，后退一步，将语句集合作为一个整体来查看。它们在某种程度上都很相似吗？您能在一个键的通用表上添加一个索引来改进它们吗？您能否消除执行10,000个动态查询的循环，而只执行一次基于集合的查询？

我想，还有另外两种可能性：

(C) 15,000个完全不同的动态SQL语句，每个语句都需要自己的艰苦优化。在这种情况下，尝试将重点放在服务器级的优化上，例如基于I/O的改进，这将使它们都受益。

(D) TempDB发生的其他奇怪的事情，或者服务器上配置错误的东西。除了找出问题并解决它外，我在这里能说的不多！

希望这能有所帮助。