存储过程和针对未知进行优化
我已经阅读了 SQL Server 2008 OPTIMIZE FOR UNKNOWN 查询提示。我明白它是如何运作的。
但是，我对在哪里和何时使用它有疑问。不能在 UDF 内指定它。它可以在存储过程中指定。但是，此 MSDN 博客文章指出了以下内容:
4.将查询移入存储过程可以将其放入单独的 程序上下文并且可以是一个很好的 使该值可见的方法 优化器（注意：这适用于 SQL 2000 也是如此）
在我看来，这似乎是说传递给存储过程的任何参数都将被“嗅探”，从而帮助 SQL Server 编译最佳执行计划。这意味着缓存的计划将被重新访问/重新编译（不确定该机制）。然而，这很令人困惑，因为它否定了 OPTIMIZE FOR UNKNOWN 的全部需要。
MSDN 关于查询提示的文章没有涵盖我的问题。
有人可以帮我回答这个问题吗？最好能指出微软的一些东西来解决这个问题。谢谢。
I've read up on the SQL Server 2008 OPTIMIZE FOR UNKNOWN query hint. I understand how it works.
However, I have a question on where and when to use it. It cannot be specified inside a UDF. It can be specified inside a stored proc. However, this MSDN blog post states the following:
4.Moving a query into a stored procedure can put it into a separate
procedural context and can be a good
way to get that value visible to the
optimizer (Note: this works in SQL
2000 as well)
That seems to me to be saying that any parameter passed to a stored proc will be "sniffed", thereby helping SQL Server to compile an optimum execution plan. This is implying that the cached plan would be revisited/recompiled (not sure of that mechanism). However, this is confusing, because it negates the whole need for OPTIMIZE FOR UNKNOWN.
The MSDN article on query hints doesn't cover my question.
Can someone answer this for me, ideally with a pointer to something from Microsoft that clears this up. Thanks.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
SQL 编译器的默认行为是使用第一次执行 SP 时给出的任何参数的值来帮助优化计划（请参阅 这篇关于 SP 重新编译的 MSDN 文章）。然后，该计划将被缓存以供重复使用，直到它离开缓存 - 有关计划缓存过程的大量详细信息 此处。
您引用的 MSDN 博客指出了使编译器更轻松地完成此过程的方法；我认为第 4 项（在问题中引用）表明这是存储过程相对于即席 SQL 的优势。
OPTIMIZE FOR UNKNOWN提示指示编译器避免默认行为；它应该忽略第一次执行中给出的参数值并选择更通用的计划。这是问题中引用的博客文章末尾建议列表中第 2 项的更极端版本；但编译器不会选择平均值或代表值，而是实际上完全忽略参数值。
考虑在第 2 项中引用的情况下使用 OPTIMIZE FOR UNKNOWN - 当同一查询由于计划在某些情况下很差而提供非常可变的性能时 - 通常是当查询中的参数过滤基数非常可变的列时。
The default behaviour of the SQL compiler is to use the values of any parameters given in the first execution of an SP to help optimise the plan (see paragraphs 2 and 3 of this MSDN article on SP recompilation). That plan is then cached for re-use until it leaves the cache - lots of detail on the plan caching process here.
The MSDN blog you cite is noting ways to make this process easier for the compiler; I think item 4 (quoted in the question) is suggesting that this is an advantage of stored procedures over ad-hoc SQL.
The
OPTIMIZE FOR UNKNOWNhint instructs the compiler to aviod the default behaviour; that it should ignore the parameter values given in the first execution and select a more generalised plan. This is a more extreme version of item 2 in the list of suggestions at the end of the blog post cited in the question;but rather than selecting an average or representative value, the compiler will effectively ignore the parameter values entirely.
Consider using
OPTIMIZE FOR UNKNOWNin the circumstances given quoted in item 2 - when the same query gives very variable performance because the plan is poor in some circumstances - typically when parameters in the query filter columns of very variable cardinality.