Optimize With Unknown – SQL Server Option

This is an option that I have never personally used, however whilst studying for my certification exams I keep coming across it.

I found a good (but rather long ) explanation about it here.

However if you want the cliff notes version here it is.

Why Would You Use Optimize With Unknown Optimizer Hint?

Well normally you wouldn’t however if you were experiencing poor performance with a parametized stored procedure
because of differences in the input parameters causing the query plan to be inefficient you could use it.

The issue is that the query plan has stored the parameter from the last time it was compiled, which could of course be way different from what is now being used.

Generally you would not need to use this setting.

If you ever do, make sure you do plenty of performance testing to ensure it is the best option.

More reading on Optimize With Unknown

REF: Microsoft

Brent Ozar Blog

Exam Tip

If you see questions about parametized queries not performing very well, this could be one of the potential answers

Rob StGeorge
Senior SQL Server Database Administrator residing in Auckland, NZ

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.