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