As you probably know, this DMV is new in SQL Server 2014. It had been first announced in CTP1 but only in BOL. Now in CTP2 everyone can “play” with it. Since BOL is a little bit unclear (understatement detected), I’ve prepared this small FAQ as a result of discussion with Adam Machanic (blog | twitter) and Matan Yungman (blog | twitter).
Q: What did you expect from sys.dm_exec_query_profiles?
A: Expectations were very high – it promised, for the first time, ability to see _actual_ execution plan details and query execution progress in Production systems in real time. For example, to see difference between actual and estimated number of rows per iterator – one of the most important indicators of plan efficiency (or more important – inefficiency).
Q: I executed long and heavy query. And didn’t find it in sys.dm_exec_query_profiles. Actually, DMV was empty. What happened?
A: BOL says: Monitors real time query progress while the query is in execution. The counters are per operator per thread. The data collected is serialized into the SHOWPLAN XML when the query finishes. What Microsoft really wanted to say by it was that SHOWPLAN XML or any other sort of actual execution plan is a prerequisite. If actual execution plan isn’t required for your query, SQL Server won’t gather all this data at the first hand. And won’t show it in sys.dm_exec_query_profiles during runtime.
Q: How can I still use this DMV?
A: You can use it via Management Studio: ask for actual execution plan for your query – and it’ll appear in sys.dm_exec_query_profiles. That’s good for demos or for investigating particular heavy query because at least in CTP2 sys.dm_exec_query_profiles shows more data than SHOWPLAN XML. I hope, Microsoft would close this gap - if data is collected and presented in DMV anyway, there is no reason whatsoever to hide it in actual plan. If you want to be able to monitor progress and measure plan efficiency of all queries running on your server in real time, you can use plan event in Profiler or – the most simple and recommended way in my opinion – just create Extended Events trace with query_post_execution_showplan event and DMV would come to life. If you’re not interested in plan itself but only in DMV, use ring buffer as ax target and small buffer size – this way you won’t waste too much memory and at the same time you’ll spare IO required for writing plans to disk.
Q: So I can just create trace and leave it running for good?
A: Not recommended unless your server isn’t really busy. Event description for query_post_execution_showplan event says: “Occurs after a SQL statement is executed. This event returns an XML representation of the actual query plan. Using this event can have a significant performance overhead so it should only be used when troubleshooting or monitoring specific problems for brief periods of time”. Gathering all this actual execution-related data is resource-consuming process by itself, so it can effectively kill busy server with thousands transactions per second. If you’re interested in exact numbers of throughput degradation as a result of actual plan capturing, excellent benchmark is here (refers to SQL Server 2012 but, I believe, SQL 2014 won’t bring significant enhancements in this field).
Q: Now you’ve frightened me. So your recommendation is not to use in Production environment?
A: Didn’t say that. As Adam pointed, we probably won’t use it in busy OLTP databases but DW-like environments with small number of heavy and complicated transactions seem to be a good candidate for monitoring and troubleshooting using sys.dm_exec_query_profiles. For this matter it could be nice to have trace flag or flag in sys.configurations (i.e. managed by sp_configure). Trace flag could be especially effective because it enables high granularity – only for desired queries – using QUERYTRACEON hint.
Q: But what is so interesting in this DMV?
A: A lot. It presents data per iterator in execution plan and provides 2 distinct categories of counters: query execution related and storage engine related. Examples for the former: row_count, estimate_row_count; for the latter: physical_read_count, write_page_count etc. Queries based on sys.dm_exec_query_profiles deserve separate post(s) – that’s not primary topic of this FAQ article.