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.