Experience using OPTIMIZE for UNKNOWN

Question

I read the theory and views behind the SQL Server 2008 query option “OPTIMIZE FOR UNKNOWN”. I understand that he is doing well enough.

I did some limited experiments and found that with a warm cache, it had an advantage of> 100 thousand lines. However, this was on a simple table and query without joins, filtering, etc. In a cold cache, a picture would undoubtedly be much more profitable.

I currently do not have a production system for the bench before / after. Therefore, I wonder if someone made before / after testing and made any useful discoveries regarding when to use this option and when not.

UPDATE:

I created a table with 3 cols, PK on UID and index on Col2 (int). All processing was against Col2. The number of lines and time is indicated (DATEDIFF * 1,000,000):

Type 1,000 5,000 20,000 100,000 Normal 0.3086 6.327 26.427 144.83, 141.126 Recompile 117.59 584.837 For Unknown 0.8101 6.52 26.89 143.788, 143.248 

Answer 1

You will use it when your data is distorted enough that the plan generated with one parameter value will be completely unsuitable for another potential parameter value. that is, to fix a problem with sniffing options.

The rest of your question does not seem to have much to do with the purpose of the prompt or the responsibility of IMO.