title: Stored procedures and OPTIMIZE FOR UNKNOWN
body: <p>I've read up on the SQL Server 2008 OPTIMIZE FOR UNKNOWN query hint. I understand how it works.</p>
  
  <p>However, I have a question on <em>where</em> and <em>when</em> to use it. It cannot be specified inside a UDF. It can be specified inside a stored proc. However, <a href="http://blogs.msdn.com/b/queryoptteam/archive/2006/03/31/565991.aspx" rel="nofollow">this</a> MSDN blog post states the following:</p>
  
  <blockquote>
    <p>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)</p>
  </blockquote>
  
  <p>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.</p>
  
  <p>The MSDN article on query hints doesn't cover my question.</p>
  
  <p>Can someone answer this for me, ideally with a pointer to something from Microsoft that clears this up. Thanks.</p>