GilaMonster (9/7/2016)
If you've analysed and tested, and have confirmed through careful testing and consideration of alternatives, that the hint is the best way to solve the specific problem you have (most likely bad parameter sniffing), then go ahead and use it.If you're trying stuff at random, don't.
If you've got access to Pluralsight, I have a course there on bad parameter sniffing and Optimise for Unknown is one of the solutions I discuss.
https://www.pluralsight.com/courses/identifying-fixing-performance-issues-caused-parameter-sniffing
Oh, thanks for posting that, I'll have a look.
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.