You are here:   Blog
Register   |  Login

Blog Archive:

* Can be used in order to search for older blogs Entries

Search in blogs

Blog Categories:

* Can be used in order to search for blogs Entries by Categories

Blog Tags:

* Can be used in order to search for blogs by keywords


Awared MVP


Microsoft® Community Contributor 

Microsoft® Community Contributor

 Read this before you use the blog! Maximize

Recent Entries


Written by: ronen ariely
24/10/2013 15:21 RssIcon


In the last few weeks we are all talking about the upcoming next version of SQL Server 2014. In the process to the final release, Microsoft give us the CTP beta versions, which release with poor documentation. The second best feature I loved on the SQL 2014 (after the In-Memory OLTP) is by sure the new "extremely useful" DMV: sys.dm_exec_query_profiles. This DMV did not work in CTP1, and I understood that it should be ready on CTP2. Is it working, and is it extremely useful as expected?

I will try to answer my own questions, and bring some Insights from my own brief experience.

Self Exploration

Till this point of time i still have not found any post including the BOL, that explain How to use this DMV in depth. As the time pass i see more and more people that complain that this DMV is not working and it bring no data in the results set. I am rewriting this blog in order to bring things Chronological order as I understood them. this can help understand "a way of thinking maybe". i will try to explain how i got to my ideas, what did i tested and why, and i will bring some Insights from my own brief experience.

Let's start play a bit

First attempt: Executing this query do work in CTP2 (No error is raising as in earlier version), but nothing...
I had several queries running in the same time but the DMV is empty.

select * from sys.dm_exec_query_profiles

Playing around, i found out (probably by accident) that executing the same query above using the "include actual execution plan" in the SSMS will bring us back 1 record. The session_id we get in the result set teach us that we actually brought back our current query information. that is not helpful at all... but it is the start to make sure that there is something to do with the execution plan.

Some more playing around and searching the Net, i notice this sentence on BOL: "The data collected is serialized into the SHOWPLAN XML when the query finishes". OK... that is a good starting point as i do understand the meaning of SHOWPLAN XML.

Displaying Execution Plans by Using the Showplan SET Options can be done in several option as described in this link:

As I understand at this point of time (maybe I will get more information in the future): The 'sys.dm_exec_query_profiles' data is very expensive to collect. There was a need for event to "trigger" the DMV to start collecting the data. Moreover, most of the data which we need to get is already collect when we use SHOWPLAN. The SHOWPLAN is basically used by the query engine to turn this on. 

Next, Under this assumption, I got the idea of using "SET STATISTICS PROFILE ON". and this look like it is working great more or less. Furthermore we can use any cunfigurtion that will collect the information of our Execution Plans. Setting the value to ON of: STATISTICS PROFILE, STATISTICS XML and so... will do it.

Lab Test 01

Open 2 session in the SSMS

on session 1 run any long time query:

-- your Long query come here, for example:
select * from sys.all_columns
CROSS JOIN sys.objects a
CROSS JOIN sys.objects b
CROSS JOIN sys.objects c

on session 2 run:

select * from sys.dm_exec_query_profiles

And now we can get on session 2 the information from session 1 (or any session which use SET STATISTICS PROFILE ON). Is this the way we supposed to use it? i am note sure. 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. but hell, this is life :-)

Another option to trigger the DMV to start collecting the data is using SQL Trace (Profiler) or using Extended Event, and create a session with query_post_execution_showplan event (Thanks to Michael Zilberstein discussion with Adam Machanic).

CREATE EVENT SESSION [query_post_execution_showplan Overhead]
ADD EVENT sqlserver.query_post_execution_showplan

* using SQL Trace or Xevent events is highly cost option, but very easy to work with and might be the best solution for a Short-term monitoring. This will let us monitor a query which is already running. On the other hand, setting the configuration do not cost extra but have to be declared before the query start or in parallel command (SSMS by default open a new session for each new query window).

Just now that i feel like i have got the basic i will o back to chapter one :-) the theoretical background of the new DMV: sys.dm_exec_query_profiles


Theoretical Background

The dm_exec_query_profiles was first add to SQL 2014 CTP2. it supposed to monitors real time query progress while the query is in execution. the DMV bring back a record for each counter.

Number Of CountersThe counters are per operator per thread.

We can count number of operators using the "Estimated Execution Plan" before we start executing our query. The number of records we expected to get in the DMV is the Sum of (Operator * Numbers of threads used for this Operator).




Lab Test 02

In this small exercise we are going to use the DMV to monitor the progress of a query execution.

1. Open SSMS session one and run this query

* This is very heavy Query as the result of using several JOIN operations. You've been warned! Executing this can consume resources.

-- your Long query come here, for example:
select * from sys.all_columns
CROSS JOIN sys.objects a
CROSS JOIN sys.objects b
CROSS JOIN sys.objects c

2. Open New session in SSMS (new query editor window) and run this query

from sys.dm_exec_query_profiles

Execute this query every several sec and look at the progress of a query execution.

In this query we use row_count to get real number of row which already pass and estimate_row_count which is a constant. You might notice that sometime the actual number is higher the estimate number of row... this is not new, it is something that we know from looking at actual execution plan. You may use different columns from the DMV of course, this is just a small demonstration monitoring progress of a query execution.

You may use this procedure to determine where the query uses the most time and what is the cost of each Operator at any point of time. You can Join this DMV with other DMVs or Tables, Join this DMV with other performance counters (such as Performance Monitor, xperf) by using the timestamp columns in the QE Counters section and so on...

THIS IS JUST THE FIRST LOOK ON NEW DMV sys.dm_exec_query_profiles ON SQL 2014 CTP2

I hope this was helpful :-)

i will add more information as i will get it probably.

References & Resources

Displaying Execution Plans by Using the Showplan SET Options (Transact-SQL).

BOL: sys.dm_exec_query_profiles (Transact-SQL).