Lowell (6/7/2011)
thanks for the clarification Gail!you said "... If a plan is never cached, it won't appear in the query stats DMV at all...", can you clarify?
I thought every query got a query plan that is created and thus was cached, but might not ever be reused by another subsequent sql, due to ad hoc char by char differences and stuff...can you think of an example where a plan doesn't get cached at all?
If a query includes the recompile hint it won't be stored in cache.
Another option that people should plan on starting to learn is using extended events to capture query executions in the same way as we used to capture trace events. It's going to be a bigger deal in Denali. May as well start practicing.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning