New DMV–yes… no… that’s complicated

Remember the excitement about new sys.dm_exec_query_profiles DMV? It promised to be a gamechanger, providing query visibility at a runtime and easily extractable information about heavy iterators in execution plan. So it has been announced but missing. Now in CTP2 it is finally here. So, singing one of my favorite Queen songs “… It finally happened - I’m slightly mad…”, I tried to observe query execution data at a runtime. And… nothing. Query is running, DMV is empty. That’s really disappointing unless I’m missing something of course. BOL doesn’t specify any configuration parameter that turns it on; so, I guess, it goes step by step: in CTP1 we could only see it (in BOL), in CTP2 we can smell – hopefully in CTP3 it would finally happen. Otherwise I will be not so slightly mad.

Update:

Caught something. But this time it is even more funny – while I had 2 concurrent sessions executing queries, all that I’ve caught was my own query – from sys.dm_exec_query_profiles!

Update 2:

Looks like query appears in the DMV only when actual execution plan is requested. So if you execute query from Management Studio and ask for an actual plan – you’ll be able to trace query progress in sys.dm_exec_query_profiles. Otherwise wait for next CTP or RC or RTM – whatever. Thanks to Matan Yungman (see his comment below) for an idea.

you probably have a point with execution plan. If you look at the second screenshot - the one where I've caught my one query to sys.dm_exec_query_profiles - it happened when I added actual plan to the query (wanted to see if there're interesting underlying objects :-)). But if actual plan is the trigger then it is still can be used for demos only.

Adam, I'm afraid this behavior is sort of documented or "by design". BOL contains following statement: "The data collected is serialized into the SHOWPLAN XML when the query finishes". Translating from Microsoftish to plain English, it can mean that at the first place data for actual plan should be _required_ - only in this case it is gathered, presented in sys.dm_exec_query_profiles during runtime and in actual plan (SHOWPLAN XML) after that. It makes this DMV pretty much useless - for demos you can find same data at actual plan after query finishes. The breakthrough I was hoping this DMV would bring was the ability to see actual plan's data for queries running in production. Not the estimated but actual - gap between estimated and actual rows etc. It appears that in order to be able to do that we would have to request actual plans - via Profiler or Extended Events - in order to force SQL Server to collect this data at the first place.

Michael, your hopes and mine are totally aligned. Unfortunately after further reflection I think I understand why it was designed this way:

This data is undoubtedly very expensive to collect and maintain. If you had a system with 10,000 batch requests/second, the overhead of collecting this data for every single query would bring it to its knees.

So instead we, as users, need to tell the query engine "yes, I know it's going to slow down my query -- please collect anyway." And they decided to use the SHOWPLAN as the vehicle for that.

This makes sense in a lot of ways, but is also really annoying. It would be cool if they could provide a TF or other knob to turn this on, especially for, e.g., servers running DW workloads that might not have a lot of concurrency but do have gigantic queries that we might want to monitor in real time...

One other thing: Did you try enabling either the SQL Trace or Xevent events for actual plan? That might also trigger the DMV to work. Not that those events are very good for throughput, but at least it might be possible to "remotely" monitor some stuff. (Alas, probably only if you flip on the event BEFORE the query runs, which probably defeats the whole purpose that most of us have in mind for this thing.)

Adam, as I thought, it is enough to create xEvents session with query_post_execution_showplan event and DMV comes to life.

I agree with your thoughts as well as with your idea that it could be especially useful for DWH-like workloads where queries aren't that frequent but heavy and use multiple threads - and for that case TF or setting in sys.configurations could be much more useful than forcing us to define trace.

I think, this discussion deserves separate post because BOL is very unclear. Sort of summary - how to use this DMV what are its limitations. Will try to write something.

BTW, what I did was creating xEvents session using ring buffer targe, small buffer size low number of events to keep - this way at least we can spare large memory allocation or IO for writing plan to disk. I think, for production system it can be sort of best practices. Next thing to try is what happens when some sort of filter is applied on trace - whether plan data is collected for all events or filtered only (actually, the question is when filter is applied in SQL Server code - before collecting performance data or after). For example, could be very useful to be able to filter by object (stored procedure).