Stored Procedures and OPTIMIZATION FOR UNKNOWN

Question

I read the prompt prompt for OPTIMIZE FOR UNKNOWN SQL Server 2008. I understand how this works.

However, I have a question where and when to use it. It cannot be specified inside UDF. It can be specified inside a stored procedure. However, it was reported in the MSDN blog reads as follows:

4. Creating a query into a stored procedure can lead to a separate procedural context and there may be a good way to get this value visible to the optimizer (Note: this works in SQL 2000)

It seems to me that any parameter passed to the stored process will “sniff”, thereby helping SQL Server make an optimal execution plan. This implies that the cached plan will be revised / recompiled (not sure about this mechanism). However, this is confusing because it negates the whole need for OPTIMIZE for UNKNOWN.

The MSDN article on query prompts does not apply to my question.

Could someone answer this question for me, ideally with a pointer to something from Microsoft that clears this. Thank.

Answer 1

SQL- , SP, (. 2 3 MSDN SP). , - .

MSDN, , , ; , 4 ( ) , SQL.

OPTIMIZE FOR UNKNOWN avoid ; , , . 2 , ;

2 , , "" , , , .

, , .

OPTIMIZE FOR UNKNOWN , 2 - , - , .