sql - 请问有人能解释为什么在SQL Server 2005中删除和重新创建存储过程会导致最初的速度下降比预期的要多得多吗？

Question

我在这里的第一篇文章，请保持温柔。 =)

我在一家继承了我们的客户之一使用的定制系统维护的公司工作。以前的开发人员(不再与我们合作)对所有数据库对象进行了加密(加密)。

在我们拥有该系统之前，该系统就一直受到各种超时问题的困扰，并且我们希望深入了解这些问题。

该数据库在生产中的SQL Express 2005上。我们希望在其上运行探查器，但是由于各种对象都已加密，因此大多数存储过程调用等都显示为“-加密文本”。

不太有用。我已经用C#编写了一个小的控制台应用程序来解密所有数据库对象，据我所知，它可以完美地工作

它找到数据库中的所有加密对象，然后为每个加密对象解密，删除with加密子句，删除原始对象并使用新的“未加密”文本重新创建它。

在尝试解密定义中使用的功能然后重新创建之前，有一些计算列已删除。

我发现的是，一旦所有内容解密，我就无法进入系统，因为存储过程等花费的时间太长，无法在首次调用时运行。执行计划是第一次编译，因此有些延迟是可以理解的，但是我们说的是1分钟以上.. 30秒后命中了命令超时，因此计划从未得到编译。

如果使用原始脚本删除并重新创建数据库对象(保留WITH ENCRYPTION子句)，也会遇到相同的问题。

因此，这里存在一些一致性。但是，绝对使我困惑的是，如果我从数据库的原始副本(该数据库是从生产数据库的备份创建的)中删除执行计划，则相同的存储过程会更快。首次通话10秒。据我所知，存储过程，函数等是相同的。

从我的测试中，我认为不是导致问题的特定过程或功能。似乎延迟是累积的，我放下并重新创建的对象越多，速度就越慢。

我在黑暗中采取了一些随机措施，重建了索引并更新了统计信息-这一点都没有效果。

我们可以编写一些东西来执行所有540个功能，触发器，存储等，以抢占用户的第一个实际调用，但是一旦SQL Server重新启动(并且我们的客户确实不时重新启动其服务器)，执行计划将被删除，我们需要再次运行相同的工具。对我来说，这似乎不是一个可行的选择(也不增加CommandTimeout属性)，我想知道为什么我会看到这种行为。

我一直在使用sys.dm_exec_query_plan和sys.dm_exec_sql_text查看执行计划，并在测试中使用了DBCC DROPCLEANBUFFERS和DBCC FREEPROCCACHE。

我完全迷住了，在我跳出办公室 window 之前，请帮助我。

提前致谢，

安迪

- 编辑 -

我不太清楚我是怎么错过它的，但是 Activity 监视器显示 session 被表值函数的重新编译阻止了。编译时间太长，阻塞的查询达到超时。

我不明白为什么在原始数据库版本(从客户站点的备份中还原)中，编译需要大约10秒钟，但是在同一数据库中删除并重新创建这些对象后，表值函数需要大约一分钟的时间编译。

我尝试截断日志，但没有任何效果。我仍然需要查看文件大小。

-另一个编辑-

TVF返回一个临时表，并在查询中具有12个外部联接，全部在sys.server_principals或sys.database_role_members上。

我似乎还记得阅读过有关重新编译和临时表的内容，我将不得不再次检查。

Answer 1

您说自己(已计算)列已删除。表格中是否可能还有其他内容被操纵？如果是这样，您可能希望使用以下命令来重新索引表(这也会更新表的统计信息):

Exec sp_msForEachTable @COMMAND1= 'DBCC DBREINDEX ( "?")'

...虽然听起来您已经做了类似的事情。无论哪种方式，我都建议您在对所有这些对象进行如此大的更改后再执行。

第三条建议:
在等待proc执行时，请在数据库上运行sp_who2以确保没有任何内容阻塞查询。您很有可能会发生一些您没有考虑到的长期交易。

第四条建议:
确保您的服务器有足够的内存。确保在所有这些大索引和对象更新之后，事务日志文件和数据文件没有自动增长。这可能会永远发生，尤其是在配置欠佳的硬件(例如您可能正在运行SQL Express)上。

第五条建议:
对数据库运行SQL Server Profiler跟踪，并查看哪些语句专门开始以及哪些语句超时。 “放大”这些内容，并对其进行逐段分析，看看发生了什么。要完全理解，这可能需要花费大量的辛苦工作。

总而言之，如果最初建立的统计信息和索引与现在的数据和索引足够相似，那么删除和重新创建proc本身的行为就不会导致这种速度下降。您可能会发现发生了其他事情，这不一定与更改proc定义本身直接相关。