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.

  • 4
    XML in SQL Server is not exactly the most efficient thing in the world - it probably has to process a lot of that in memory, evicting all the real stuff that needs memory, and virtually all of it will be serial IIRC so 24 or 240 or 960 cores won't help you. Get more memory or dump the DMVs and process the XML queries elsewhere. Feb 17 '15 at 0:16

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!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.