Grant Fritchey (7/31/2012)
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.
Thanks
I've been doing some reading on the topic of performance, I've taken little pieces from different sections.
I've put this together for a "SQL is Slow Now" situation, am I on the right track here?
select ER.session_id,ES.login_time,ER.Command, ER.start_time,ER.blocking_session_id
,ER.wait_resource
,ER.wait_type
,ER.wait_time
,ER.reads
,ER.writes
,DB_NAME(ER.database_id)
from sys.dm_exec_requests ER
inner join sys.dm_exec_sessions ES
on ER.session_id = ES.session_id
inner join sys.dm_os_waiting_tasks WT
on ES.session_id = WT.session_id
where ES.is_user_process <> 0
Thanks