在您使用 SQL Server 的职业生涯中的某个时刻，参数嗅探是否会突然跳出并进行攻击? [英] At some point in your career with SQL Server does parameter sniffing just jump out and attack?

问题描述

今天，我又遇到了一个与 SQL Server 2005 中的参数嗅探有关的重大问题.

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.

我在我的 WHERE 子句中添加了一个条件 - 这是代码生成的，所以它是一个文字常量:

I add a criteria to my WHERE clause - this is code-generated, so it's a literal constant:

WHERE DATA_DT_ID = 20081231 -- 这是多余的，因为现在所有的 DATA_DT_ID 都是 20081231.

WHERE DATA_DT_ID = 20081231 -- Which is redundant because all DATA_DT_ID are 20081231 right now.

性能提升.从 7 秒比较大约 150 万行到 2 小时，没有完成.直接在 SSMS 中运行生成的 SQL - 没有 SP.

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.

我使用 SQL Server 已经 12 年了，自从 10 月以来，我从未在这台生产服务器上遇到过如此多的参数嗅探问题(构建版本 9.00.3068.00).并且在每种情况下，这都不是因为它是第一次使用不同的参数运行或表已更改.这是一个新表，它仅使用此参数运行，或者根本没有 WHERE 子句.

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.

而且，不，我没有 DBA 访问权限，而且他们没有给我足够的权限来查看执行计划.

And, no, I don't have DBA access, and they haven't given me enough rights to see the execution plans.

我不确定我是否能够将这个系统交给只有几年经验的 SQL Server 用户.

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 事实证明，虽然统计数据声称是最新的，但运行 UPDATE STATISTICS WITH FULLSCAN 可以解决问题.

UPDATE Turns out that although statistics claim to be up to date, running UPDATE STATISTICS WITH FULLSCAN clears up the problem.

FINAL UPDATE 即使使用 WITH RECOMPILE 和 UPDATE STATISTICS 重新创建 SP，结果还是必须以不同的方式重写查询以使用 NOT IN 而不是 LEFT JOIN with NULL检查.

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.

推荐答案

不太好回答，但我会分享我的经验.

Not quite an answer, but I'll share my experience.

参数嗅探花了几年的 SQL Server 来咬我，当我离开主要从事 DBA 工作后回到开发人员 DBA 时.我对引擎、SQL 的工作原理、最好留给客户的东西等有了更多了解，而且我是一个更好的 SQL 编码员.

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.

例如，动态 SQL 或 CURSOR 或只是简单的错误 SQL 代码可能永远不会遭受参数嗅探.但是更好地设置编程或如何避免动态 SQL 或更优雅的 SQL 更有可能.

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.

无论如何，我更喜欢参数屏蔽而不是 WITH RECOMPILE.无论如何更新统计信息或索引都会强制重新编译.但是为什么总是重新编译呢?我已经在别处回答了您的一个问题，其中有一个链接提到编译期间会嗅探参数，所以我也不相信它.

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.

参数屏蔽是一种开销，是的，但它允许优化器逐个评估查询，而不是全面重新编译.尤其是SQL Server 2005的语句级重编译

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 在 SQL Server 2008 中似乎也与屏蔽完全相同.我和我的 SQL Server MVP 同事花了一些时间调查并得出了这个结论.

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.

这篇关于在您使用 SQL Server 的职业生涯中的某个时刻，参数嗅探是否会突然跳出并进行攻击?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！