Optimizing queries isn't always straight forward. Sometimes you think you've created a great index but the query optimizer just won't agree. You update stats, try maxdop hints, clear caches, and maybe even curse a little but nothing works.
You suck it up and admit you're going to have to make an exception to what you know is right and use an index/table/locking hint.
Well in doing that today I made extensive use of a few options most people may or may not be aware of. These are:
SET STATISITICS IO ON;
SET STATISTICS TIME ON;
SET STATISTICS PROFILE ON;
The output of the first two options are pretty easy to understand, but the third (which is AMAZINGLY valuable!) can be tricky.
I suggest taking a look at this blog post on the subject as it gives great detail on the use of this excellent tool.
http://blogs.msdn.com/queryoptteam/archive/2006/08/29/730521.aspx
