请问任何人为什么解释在SQL Server 2005中删除和重新创建存储过

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

在我们取得它的所有权之前,该系统一直受到各种超时问题的困扰,我们希望深入了解这些问题.

该数据库正在生产中的SQL Express 2005上.我们想在其上运行探查器,但因为各种对象都是加密的,所以大多数存储过程调用等都显示为“ – 加密文本”.

不是很有用.我在C#中编写了一个小的控制台应用程序来解密所有的数据库对象,据我所知,它完美地工作

它查找数据库中的所有加密对象,并为每个加密对象解密,删除with encryption子句,删除原始对象并使用新的“无加密”文本重新创建它.

在尝试解密其定义中使用的函数之前,有一些计算列被删除,然后重新创建.

我发现的是,一旦所有内容都被解密,我就无法进入系统,因为存储过程等……在第一次调用时运行时间太长.执行计划是第一次编译,所以一些延迟是可以理解的,但是我们正在谈论1分钟加上……在30秒之后命令超时被命中,所以计划永远不会被编译.

如果我使用原始脚本删除并重新创建数据库对象(保持WITH ENCRYPTION子句),我也会遇到同样的问题.

所以那里有一些一致性.然而,令我绝对神秘的是,如果我从数据库的原始副本(从生产数据库的备份创建)中删除执行计划,则相同的存储过程要快得多.第一次通话10秒.据我所知,存储过程,函数等都是一样的.

从我的测试来看,我不认为这是导致问题的特定程序或功能.似乎延迟是累积的,我丢弃的物体越多.重建速度较慢的东西.

我在黑暗中采取了一些随机刺,重建索引和更新统计数据 – 这根本没有任何影响.

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

我一直在使用sys.dm_exec_query_plan和sys.dm_exec_sql_text来查看执行计划,并使用DBCC DROPCLEANBUFFERS和DBCC FREEPROCCACHE作为我测试的一部分.

我完全难过了,请在我跳出办公室窗户之前帮助我.

提前致谢,

安迪.

– 编辑 –

我不太清楚我是如何错过它的,但是Activity Monitor显示会话被重新编译表值函数阻塞.编译需要太长时间,阻止的查询会超时.

我不明白为什么在原始版本的数据库中(从客户站点获取的备份恢复),编译大约需要10秒钟,但在同一数据库中删除并重新创建这些对象后,表值函数需要将近一分钟编译.

我试过截断日志,没有任何影响.我仍然需要查看文件大小.

– 另一个编辑 –

TVF返回一个临时表,在查询中有12个外连接,全部在sys.server_principals或sys.database_role_members上.

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

解决方法

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

……虽然听起来像你做过这样的事情.无论哪种方式,我建议您在对所有这些对象进行如此大的更改后再进行此操作.

第三条建议：
在等待执行proc的同时,在数据库上运行sp_who2以确保没有任何内容阻止您的查询.您很可能会遇到某种长期存在的交易,而您没有考虑到这种交易.

第四项建议：
确保您的服务器有足够的内存.在所有这些大型索引和对象更新之后,确保您的事务日志文件和数据文件不会自动增长.这可能会永远发生,特别是在你可能已经运行SQL Express的规格不足的硬件上.

第五条建议：
对数据库运行SQL Server Profiler跟踪,并查看具体开始的语句以及哪些语句超时. “放大”那些并逐一分析它们,看看是什么.这可能只是需要很多努力才能完全理解.

总之,如果最初建立的统计数据和索引与现在相似,那么删除和重新创建过程本身的行为不应该导致这种减速.很可能你会发现其他东西发生的事情并不一定与改变proc定义本身直接相关.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!