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.

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.

SQLSACT (7/31/2012)

---

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

SQLSACT (7/31/2012)

---

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

Grant Fritchey (7/31/2012)

---

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.

Grant Fritchey (7/31/2012)

---

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.

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