What is the performance impact of queries against DM objects using DMVs in SQL Server 2008?

Question

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.

Answer 1

There are other DMVs that can help you quantify this. sys.dm_exec_sessions has columns cpu_time, memory_usage and total_scheduled_time amongst 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!