THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Michael Zilberstein

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.

image

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!

image

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.

Published Thursday, October 17, 2013 4:34 AM by Michael Zilberstein
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Matan Yungman said:

Hi Michael,

Pretty disappointing. I guess we still have to wait..

What I do find is that when including the actual execution plan in one session and executing a query, querying sys.dm_exec_query_profiles from another session does show information.

To make it interesting, I ran a long parallel query, and as promised, I saw a few lines of interesting information about it in sys.dm_exec_query_profiles.

Still promising. I hope RTM will have the complete feature...

October 17, 2013 3:01 AM
 

Michael Zilberstein said:

Hi Matan,

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.

October 17, 2013 3:08 AM
 

Matan Yungman said:

Agreed. It will be very disappointing if it will stay that way in RTM..

October 17, 2013 3:21 AM
 

Adam Machanic said:

I just ran four heavily parallel queries at once -- to use up lots of threads -- and still got nothing in the view, not even my own SELECT from the view :-(

What were you doing when you actually managed to get a row back?

October 17, 2013 9:26 AM
 

Adam Machanic said:

Just read Matan's comment. Excellent catch! Of course I also agree with Michael -- this makes the feature basically useless in production. Still a cool toy, though.

October 17, 2013 9:29 AM
 

Michael Zilberstein said:

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.

October 17, 2013 9:54 AM
 

Adam Machanic said:

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...

--Adam

October 17, 2013 10:05 AM
 

Adam Machanic said:

By the way, I don't see much of the promised data in the SHOWPLAN XML at all. Physical Read Count and CPU, for example, are nowhere to be found.

October 17, 2013 10:09 AM
 

Adam Machanic said:

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.)

October 17, 2013 10:21 AM
 

Michael Zilberstein said:

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.

October 17, 2013 10:31 AM
 

Michael Zilberstein said:

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).

October 17, 2013 10:38 AM
 

Adam Machanic said:

Unfortunately filtering for the Xevent actual plan is processed *after* the plan is generated so it winds up being very expensive and can greatly degrade throughput of a busy system.

October 17, 2013 11:20 AM
 

Michael Zilberstein said:

At least you can spare memory and IO by filtering all plans out. So the data is collected (ergo query_profiles DMV should contain it). Collecting is expensive but writing it down is expensive either.

October 17, 2013 11:32 AM
 

Michael Zilberstein said:

As you probably know, this DMV is new in SQL Server 2014. It had been first announced in CTP1 but only

October 21, 2013 8:36 AM
 

Ronen Ariely - pituach said:

Hi,

You can just use

SET STATISTICS PROFILE ON

try this: open 2 session in the SSMS

on session 1 run any long time query:

SET STATISTICS PROFILE ON

-- your query come here, for example:

select * from sys.all_columns

CROSS JOIN sys.objects

GO

on session 2 run:

select * from sys.dm_exec_query_profiles

October 26, 2013 8:49 AM
 

Ronen Ariely - pituach said:

This is funny (or maybe sad) that we put a lot of time figuring it out and using reflection/Guesses while the developers of the feature could give us the information in no time :-)

October 26, 2013 9:12 AM

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement