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.Thank You
So I would use that DMV as part of troubleshooting for a "SQL is slow NOW" situation
Where would I start for a "SQL has been slow erratically"
Thanks
Actually, if "SQL Server is slow NOW" I'd look first at sys.dm_exec_requests and then combine that, as needed with sys.dm_exec_query_stats. Again, depending on the volatility of your cache, this is a good place to look. It's just potentially not good because the queries do leave cache. For detailed, specific views, capture every call with extended events. Just be ready to deal with large amounts of data.
"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