您是否应该始终预见到参数嗅探引起的问题？
使用sql server 2008，我有一个简单的存储过程，其内容是
DELETE FROM [ABC].[dbo].[LookUpPermissions]
WHERE Code = @Code
在最近的代码审查中，DBA说我应该“添加参数嗅探”，我相信这意味着我应该考虑参数嗅探。我过去从未这样做过，而且查询没有任何性能问题，所以我认为这是不必要的。
虽然我认为答案可能是用户偏好，但考虑参数嗅探是最佳实践吗？如果在小型数据集上调用存储过程、不经常使用并且没有性能问题，是否有必要？
编辑
这是否仅适用于 WHERE 子句中使用的参数，或者例如，您是否可能需要考虑 INSERT 语句中的所有参数？
Using sql server 2008, I have a simple stored procedure, the contents of which are
DELETE FROM [ABC].[dbo].[LookUpPermissions]
WHERE Code = @Code
In a recent code review, the DBA said I should "add parameter sniffing" which I believe means I should account for parameter sniffing. I've never done this in the past and I am not having any performance issues with the query so I would think it is unnecessary.
While I presume the answer may be user preference, would it be best practice to account for parameter sniffing? Is it necessary if the stored procedure is called on a small dataset, used infrequently and is having no performance issues?
edit
Is this only applicable to parameters used in a WHERE clause, or for example, would you possibly need to account for all parameters in an INSERT statement?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
像这样对单个值的简单搜索不应该容易受到参数嗅探的影响。当传入的参数导致截然不同的结果并且最佳执行计划与之前生成的计划不同时，更值得关注。
例如，考虑一个查询，该查询查找日期列位于 @start_date 和 @end_date 之间的行。使用 2 天的日期范围调用该过程可能会生成/缓存一个对于 1 年的日期范围而言不是最佳的执行计划。
A simple search for a single value like this shouldn't be vulnerable to parameter sniffing. It's more of a concern when the parameters passed in result in widely different results and the optimal execution plan varies from the one that has been previously produced.
As an example, think of a query that looks for rows where a date column is between a @start_date and an @end_date. Calling the procedure with a date range of 2 days may produce/cache an execution plan that is not optimal for a date range of 1 year.
参数嗅探是另一个内置的“聪明的东西”（记住内联拼写/语法检查），微软用它来优化 SQL 查询。通过嗅探输入参数，SQL Server 可以对哪个缓存的查询计划是最佳使用计划进行最好的猜测。它并不总是做出正确的选择。
阅读此内容以了解如何操作的信息欺骗 SQL 使其不使用 prmater 嗅探。
Parameter Sniffing is another built-in "smarty thingy" (remember inline spell/grammar cheking) that Microsoft uses to optimize SQL queries. By sniffing the input parameters SQL Server makes it's best educated guess about which cached query plan would be the best plan to use. It does not always make the correct choice.
Read this for information on how to trick SQL into not using pramater sniffing.