Recently, I've been running queries against our production system on the DMVs for querying the plan cache. In particular, here's the from clause of the query:
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS batch(stmt)
CROSS APPLY stmt.nodes('.//IndexScan/Object[@Index=sql:variable("@IndexName")]') AS idx(obj)
There's roughly 28k rows in dm_exec_cached_plans.
When I do this, it's setting off alerts for PLE dropping very low and very high disk IO and latency (read/write stalls).
This comes as a surprise because I've never read about or experienced anything relating to performance issues running adhoc queries against the DMVs. This server has 24 cores, 64gb RAM and SSDs. I know there is always more to it than that, but I have a hard time believing these queries cause such a problem.
Can anyone explain exactly why this happens? I'm happy to provide more info about our configuration, but I was hoping there would be a simple explanation as well.
Best Answer
There are other DMVs that can help you quantify this.
sys.dm_exec_sessionshas columnscpu_time,memory_usageandtotal_scheduled_timeamongst others. While you're running your plan-getting query in one session you can interrogate this DMV in a second session to find how expensive the first is.Pleasingly, you can also quantify how expensive your second session is at the same time!