Fantastic DMV in SQL Server 2014, sys.dm_exec_query_profiles

You can check the progress of query execution using this DMV, you can get information of each operator used in Actual Query Plan in detail through this dmv.

Lets play around with dm_exec_query_profiles.

Test 1

I opened a SSMS and run my query in session 1

-- My Long query in session 1

select * from sys.all_columns

CROSS JOIN sys.objects

GO

At the same time I opened another query window(another session, session 2) and run my dm_exec_query_profiles

select * from sys.dm_exec_query_profiles

(0 row(s) affected)

What I found is zero record though a heavy query is running in session1 but it didn’t capture the query progress.

Test 2

I found that to get the result from this DMV you need to trigger this. And to trigger this DMV you need to SET STATISTICS PROFILE ON

Again in session1

SET STATISTICS PROFILE ON

-- My Long query in session 1

select * from sys.all_columns

CROSS JOIN sys.objects

GO

Session 2

select * from sys.dm_exec_query_profiles

select session_id,physical_operator_name,node_id,row_count,

estimate_row_count,first_row_time,last_active_time,cpu_time_ms

from sys.dm_exec_query_profiles

So here lot of interesting information we captured through this DMV, all the information and progress of running query including the time taken by each physical operator, row count etc.

But only point of disappointment is it need either actual Query plan on or Set profiler on the running query for which we want to capture the progress of query.

My code is my fire. Fire and individual cannot sleep, when I will get prospect of sleep!!!

 

Post Reference: Vikram Aristocratic Elfin Share