sys.dm_exec_query_profiles - SQL 2014 , new way to troubleshot queries

SQ: server 2014 has many silent features which are very unique and can help a lot to developers
one of it is sys.dm_exec_query_profiles

This DMV gives real time query execution details while query is running.
This is very helpful for troubleshooting queries.
The counters are per operator per thread. The data is serialized into the showplan xml when query finishes.



When we run this query its not giving any result.
To get data at first hand either we have to
1. Enable actual execution plan in SSMS
2. SET STATISTICS PROFILE ON
3. SET STATISTICS XML ON

This will gather  data which will be used by the DMV to give result

Lets look at example which will give you better idea.

I have a table which has single Integer column  and 15M rows.

CREATE TABLE t

(id INT)

CREATE CLUSTERED COLUMNSTORE INDEX idx on t

DECLARE @i  INT

SET @i = 1

WHILE @i <= 15000000

BEGIN

       INSERT INTO t VALUES (@i)

       SET @i = @i + 1

END

Now we will run simple insert query on this table  and check data in DMV

first we have to set following command.

SET STATISTICS PROFILE ON

INSERT INTO T SELECT top 3000000 * from t

In other window we will run the query

SELECT * FROM SYS.DM_EXEC_QUERY_PROFILES

Lets see its result

We will run the query multiple times so that we can get idea about query progress.

As we can see in the first result two operators are running for node id 2 and 3

node 2 - top rows selection
node 3- clustered index scan

Operators in node 0 and 1 are not started

From row_count we can see how many rows are scanned and estimate_row_count shows how many it need to scan.


Now lets go to second result

Here we can node 2 and 3 completed.
We can see its close_time is  not 0
and elapsed_time shows 1595 MS took for top count and 1130 for index scan.
Node 1 and 2 are in progress and row_count shows how many rows completed.
Also it shows that clustered index  insert took highest time among all operators.


This is very path breaking feature and will change the ways query troubleshooting in future.
An experienced developers can use this feature smartly to troubleshoot his queries.