SQLSACT (7/31/2012)
Grant Fritchey (7/31/2012)
The DMV stores data for queries that are currently in cache. As soon as a plan ages out of cache all the information in that DMV is lost. So it really depends on how volatile the plan cache on your machine is for how much information is available to you through that DMV. For a complete measure of query performance, I'd go with extended events.I could be wrong but I remember reading somewhere that when plans are flushed out of cache or when they are flushed too quickly, it affects performance of SQL Server
Am I on the right track here?
Thanks
Plans flush out of cache all the time. It doesn't necessarily affect performance at all. It's when they are constantly flushing, or never being reused that you have an issue. You can get an idea of how active your cache is by simply looking at the oldest date within that DMV.
"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