绕过 SQL Server 2005 中的缓存计划
有人可以解释一下为什么这有效吗？这是场景。我有一个存储过程，它开始运行缓慢。然后我选择一个参数并声明一个变量来存储其值，并在过程中使用声明的变量而不是参数。然后，该过程将大大加快。
我认为这与缓存的计划和统计数据有关，但我不确定。随着数据库的增长和变化，统计数据是否会过时，以便缓存的计划根据数据的过去状态（与数据的当前状态不同）进行优化？
谢谢。
Can someone please explain why this works. Here is the scenerio. I have a stored proc and it starts to run slow. Then I pick a parameter and declare a variable to house its value and in the proc use the declared variable instead of the parameter. The proc will then speed up considerably.
I think it has something to do with the cached plan and statistics, but I am not sure. Is it that statistics get out of date as the database grows and changes so that the cached plan is optimized on a past state of the data which is different from the present state of the data?
thanks.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您所描述的内容通常称为 参数嗅探 ，这似乎是 SQL Server 独有的问题 - Oracle IME 上从未出现过此问题，我也不知道 MySQL 上有此问题。我给出的链接很好地解决了这个问题。
请注意，优化器使用的统计信息与数据更改不同步 - 您可能需要运行 偶尔也会更新统计数据。
What you describe is commonly referred to as parameter sniffing, and it seems to be a SQL Server only issue - never had it on Oracle IME, nor am I aware of the issue on MySQL. The link I gave breaks down the issue well.
Mind that the statistics used by the optimizer aren't sync'd with data changes - you might need to run UPDATE STATISTICS occaissionally too.
当您更改 ddl 时，存储过程执行计划将从缓存中删除，但正如 OMG Ponies 所说，优化器不会跟踪数据更改。
解决该问题的一种方法是使用“With Recompile”选项，每次运行该过程时都会对其进行编译。另一种可能的解决方案是定期运行 sp_recompile，这会将存储过程标记为重新编译。
When you change ddl the stored procedure execution plan is removed from the cache but as OMG Ponies has said the optimizer does not track data changes.
One way to get around the issue is to use With Recompile option and the procedure will be compiled every time you run it. Another possible solution is to run sp_recompile periodically which marks the stored procedure for recompilation.