存储过程和优化未知 [英] Stored procedures and OPTIMIZE FOR UNKNOWN

问题描述

我已经阅读了SQL Server 2008 OPTIMIZE FOR UNKNOWN查询提示.我知道它是如何工作的.

但是，我对在位置和何时使用它有疑问.不能在UDF内部指定.可以在存储的proc中指定它.但是，此 MSDN博客文章指出以下内容:

4.将查询移到存储过程中可以将其放在单独的位置 程序上的背景，可以是一个很好的 使该值可见的方法 优化器(注意:这在SQL中有效 也是2000)

在我看来，这似乎是嗅探"传递给存储proc的任何参数，从而帮助SQL Server编译最佳执行计划.这意味着将重新访问/重新编译已缓存的计划(不确定该机制).但是，这很令人困惑，因为它否定了优化未知"的全部需求.

有关查询提示的MSDN文章未涵盖我的问题.

有人可以为我回答这个问题吗?理想情况下，可以使用指向Microsoft的一些东西的指针来解决此问题.谢谢.

解决方案

SQL编译器的默认行为是使用SP首次执行时给出的任何参数的值来帮助优化计划(请参见第2和第2段). 有关SP重新编译的MSDN文章中的3.然后将该计划缓存以供重复使用，直到离开缓存为止-有关计划缓存过程的许多详细信息

但编译器将选择完全忽略参数值，而不是选择平均值或代表值.

请考虑在第2项中给出的给定情况下使用OPTIMIZE FOR UNKNOWN-当同一查询由于在某些情况下计划不佳而导致非常可变的性能-通常是当查询中的参数过滤基数非常可变的列时. >

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.

解决方案

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 UNKNOWN hint 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;

2 If you find that the optimizer is picking different plans over time that have varying performance characteristics, consider using a parameter hint with a representative "average" value to get a good, common query plan that will work reasonably for all values.

but rather than selecting an average or representative value, the compiler will effectively ignore the parameter values entirely.

Consider using OPTIMIZE FOR UNKNOWN in 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.

这篇关于存储过程和优化未知的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！