在您 SQL Server 职业生涯的某个阶段，参数嗅探是否会突然出现并发起攻击？
今天，我又遇到了一个关于 SQL Server 2005 中的参数嗅探的重大问题。
我有一个查询，将一些结果与已知的良好结果进行比较。 我在结果和已知的良好结果中添加了一列，这样每个月，我都可以在两侧加载新的月份结果，并仅比较当前月份。 新列位于聚集索引中的第一个列，因此新的月份将添加到末尾。
我向我的 WHERE 子句添加了一个条件 - 这是代码生成的，因此它是一个文字常量：
WHERE DATA_DT_ID = 20081231 - 这是多余的，因为所有 DATA_DT_ID 都是 20081231 正确的现在。
性能决定一切。 从 7 秒比较大约 150 万行到 2 小时却什么也没完成。 直接在 SSMS 中运行生成的 SQL - 无 SP。
我已经使用 SQL Server 12 年了，自 10 月份以来，我在参数嗅探方面从未遇到过如此多的问题（构建版本 9.00.3068.00）。 在每种情况下，这并不是因为第一次使用不同的参数运行或表发生了更改。 这是一个新表，仅使用此参数运行，或者根本不使用 WHERE 子句。
而且，不，我没有 DBA 访问权限，而且他们也没有给我足够的权限来查看执行计划。
我不确定是否能够将这个系统交给只有几年经验的 SQL Server 用户。
UPDATE 事实证明，尽管统计信息声称是最新的，但运行 UPDATE STATISTICS WITH FULLSCAN 可以解决问题。
最终更新 即使使用WITH RECOMPILE 和UPDATE STATISTICS 重新创建SP，结果仍然必须以不同的方式重写查询，以使用NOT IN 而不是带有NULL 检查的LEFT JOIN。
Today again, I have a MAJOR issue with what appears to be parameter sniffing in SQL Server 2005.
I have a query comparing some results with known good results. I added a column to the results and the known good results, so that each month, I can load a new months results in both sides and compare only the current month. The new column is first in the clustered index, so new months will add to the end.
I add a criteria to my WHERE clause - this is code-generated, so it's a literal constant:
WHERE DATA_DT_ID = 20081231 -- Which is redundant because all DATA_DT_ID are 20081231 right now.
Performance goes to pot. From 7 seconds to compare about 1.5m rows to 2 hours and nothing completing. Running the generated SQL right in SSMS - no SPs.
I've been using SQL Server for going on 12 years now and I have never had so many problems with parameter sniffing as I have had on this production server since October (build build 9.00.3068.00). And in every case, it's not because it was run the first time with a different parameter or the table changed. This is a new table and it's only run with this parameter or no WHERE clause at all.
And, no, I don't have DBA access, and they haven't given me enough rights to see the execution plans.
It's to the point where I'm not sure I'm going to be able to handle this system off to SQL Server users with only a couple years experience.
UPDATE Turns out that although statistics claim to be up to date, running UPDATE STATISTICS WITH FULLSCAN clears up the problem.
FINAL UPDATE Even with recreating the SP, using WITH RECOMPILE and UPDATE STATISTICS, it turned out the query had to be rewritten in a different way to use a NOT IN instead of a LEFT JOIN with NULL check.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
不完全是答案，但我会分享我的经验。
当我离开主要从事生产 DBA 工作后又回到开发 DBA 时，参数嗅探花了几年的 SQL Server 时间才让我受不了。 我对引擎、SQL 的工作原理、什么最好留给客户等有了更多的了解，并且我是一个更好的 SQL 编码员。
例如，动态 SQL 或 CURSOR 或者只是简单的错误 SQL 代码可能永远不会遭受参数嗅探。 但更好的设置编程或如何避免动态 SQL 或更优雅的 SQL 更有可能。
我注意到它用于复杂的搜索代码（大量条件）和复杂的报告，其中参数默认值影响了计划。 当我看到经验不足的开发人员如何编写这段代码时，它就不会遭受参数嗅探。
无论如何，我更喜欢参数屏蔽而不是WITH RECOMPILE。 无论如何，更新统计信息或索引都会强制重新编译。 但为什么每次都要重新编译呢？ 我在其他地方回答了你的一个问题，其中有一个链接提到参数在编译过程中被嗅探，所以我也不相信它。
是的，参数屏蔽是一种开销，但它允许优化器逐个评估查询，而不是全面重新编译。 特别是在 SQL Server 2005 的语句级重新编译的情况下，
SQL Server 2008 中的 OPTIMIZE FOR UNKNOWN 似乎也执行与屏蔽完全相同的操作。 我和我的 SQL Server MVP 同事花了一些时间进行调查并得出了这个结论。
Not quite an answer, but I'll share my experience.
Parameter sniffing took a few years of SQL Server to come and bite me, when I went back to Developer DBA after moving away to mostly prod DBA work. I understood more about the engine, how SQL works, what was best left to the client etc and I was a better SQL coder.
For example, dynamic SQL or CURSORs or just plain bad SQL code probably won't ever suffer parameter sniffing. But better set programming or how to avoid dynamic SQL or more elegant SQL more likely will.
I noticed it for complex search code (plenty of conditionals) and complex reports where parameter defaults affected the plan. When I see how less experienced developers would write this code, then it won't suffer parameter sniffing.
In any event, I prefer parameter masking to WITH RECOMPILE. Updating stats or indexes forces a recompile anyway. But why recompile all the time? I've answered elsewhere to one of your questions with a link that mentions parameters are sniffed during compilation, so I don't have faith in it either.
Parameter masking is an overhead, yes, but it allows the optimiser to evaluate the query case by case, rather than blanket recompiling. Especially with statement level recompilation of SQL Server 2005
OPTIMISE FOR UNKNOWN in SQL Server 2008 also appears to do exactly the same thing as masking. My SQL Server MVP colleague and I spent some time investigating and came to this conclusion.
我怀疑你的问题是由于数据统计不足引起的。 由于您没有服务器的 DBA 访问权限，因此我鼓励您询问 DBA 上次更新统计信息的时间。 这会对性能产生巨大影响。 听起来您的表索引也不是很好。
基本上，这“感觉”不像参数嗅探问题，而更像是“健康”数据库问题。
本文介绍了如何确定上次更新统计信息的时间：
统计更新时间
I suspect your problem is caused by out of data statistics. Since you do not have DBA access to the server, I would encourage you to ask the DBA when the last time statistics were updated. This can have a huge impact on performance. It also sounds like your tables are not indexed very well.
Basically, this does not "feel" like a parameter sniffing issue, but more of a "healthy" database issue.
This article describes how you can determine the last time statistics were updated:
Statistics Update Time
我赞同有关检查统计信息的评论 - 我见过几个查询性能急剧下降的实例，特别是因为统计信息已过时。
具体来说，如果您的 PK 中有一个日期，并且 SQL Server 认为在特定日期之后只有 10 或 100 条记录，而实际上有数千条记录，它可能会选择效率极低的查询计划，因为它认为数据集要小得多比实际情况更重要。
HTH，
I second the comment about checking the statistics - I have seen several instances where a query's performance has fallen off a cliff specifically because the statistics are out of date.
Specifically, if you have a date in your PK, and SQL Server thinks there are only a 10 or 100 records which after a specific date when in fact there are thousands, it may choose terribly inefficient query plans because it thinks the dataset is much smaller than it really is.
HTH,
我遇到了与此完全相同的生产问题。 应用程序中调用存储过程的选项卡不会显示。 我对特定过程进行了跟踪并看到了调用。 应用程序在 30 秒内超时，过程将花费近 40 - 50 秒才能完成（完全按照跟踪调用的方式运行过程）。
下一步是找出哪个语句导致了我在程序执行过程中注意到的扫描。 因此，我编写了过程脚本，删除了过程语法和声明的变量，并在查询分析器中运行。 3秒就跑完了！！！
我写这篇文章是为了让那些寻找答案的人知道这可能发生在 SQL 中。 它源于参数嗅探问题。 我无法找到这个线程，因为我将原因确定为错误的缓存查询计划！ 我读过一些帖子，他们说这种情况发生在一个特定的用户/值上。 但它可以发生在任何值上，并且一旦开始，它就可以是一个连续的事情。
对我来说，解决方案是编写过程脚本并再次运行它。 是的。 就这么简单。 改变效果很好。 无需删除并重新创建。 这会导致 SQL 刷新缓存的计划，一切都很好。 我还没有弄清楚如何在服务器级别禁用此功能。 清理所有的进程太麻烦了。 希望这可以帮助
I had a production issue exactly like this. A tab in the application which called a stored proc would not show. I ran a trace for the specific proc and saw the call. The application times out in 30 secs and the proc would take close to 40 - 50 secs to complete (ran the proc exactly as called from the trace).
Next step was to figure out which statement was causing the scans I notice in the execution of the procedure. So I scripted out the proc, removed the procedure syntax and declared variables and ran in query analyser. It RAN in 3 secs!!!
I'm writing this to let anyone out there looking for answeres know that this can happen in SQL. It stems from the parameter sniffing issue. I was able t ofind this thread because I pin-pointed the cause as a faulty cached query plan! I've read posts where they said it happens to one specific users/ value. But it can happen to any value and once it starts, it can be a continuous thing.
The solution for me was to script out the proc and run it again. yeah. that simple. An alter works fine. No need to drop and re-create. This causes SQL to refresh the cached plan and things were fine. I have not figured out how to disable this at a server level. It is too cumbersome to clean up all the procs. Hope this helps