SQL 2014 Learning Series # 11 – New DMV – sys.dm_exec_query_profiles (Part 1)
Posted by blakhani on June 10, 2014
How many time you have been asked to find out to know what is the exact piece in the query plan is being executed currently? or Imagine a situation where a query running for hours or query never finishes? Well, the new DMV can provide a new way to troubleshoot the problem. So far, you might have heard two kind of execution plans: estimated execution plan and actual execution plan. As the name says estimated plan can be seen without execution of the query whereas for actual execution plan we need to wait till query execution completes.
From SQL Server 2014 onwards, product has added functionality so that we can monitor query progress in the real time (i.e. while the query is being execution) using the DMV sys.dm_exec_query_profiles. In other words, we can monitor the progress of a query execution and determine where the query uses the most time. As per book online
Monitors real time query progress while the query is in execution. Use this DMV to monitor the progress of a query execution and determine where the query uses the most time. Join this DMV with other system DMVs using the columns identified in the description field. Join this DMV with other performance counters (such as Performance Monitor, xperf) by using the timestamp columns.
To serialize the requests of sys.dm_exec_query_profiles and return the final results to SHOWPLAN XML, use SET STATISTICS PROFILE ON; or SET STATISTICS XML ON;,
It must be noted that unless we enable any other kind of actual execution plan (i.e. statistics profile, statistics xml) we will NOT be able to use sys.dm_exec_query_profiles. If we don’t enable them, SQL Server will not capture data to populate into the DMV.
Let us see a quick demo.
set nocount on go Create database QPDemo go use QPDemo go CREATE TABLE Balmukund (iID INT, cName char(4000) DEFAULT 'Name') go DECLARE @loop INT SET @loop = 1 WHILE @loop <= 100000 BEGIN INSERT INTO Balmukund (iID) VALUES (@loop) SET @loop = @loop + 1 END go CREATE CLUSTERED COLUMNSTORE INDEX CCI_Balmukund on Balmukund go
I have created new database, a table and a column store clustered index on that table. Now, if we run below query, it would take little time to finish.
Select * from Balmukund where iID > 50
While the query is running, we can execute below
Select physical_operator_name, row_count, estimate_row_count, * from sys.dm_exec_query_profiles
As I said earlier, we would NOT get the output. unless actual plan settings are enabled. Now, run below from query window.
set statistics profile on go Select * into NewTable from Balmukund where iID > 50 go set statistics profile on go
Now, if we check, the data in the DMV would be populated.
If we run again, we can see row_count is getting increased.
There are other useful columns which we would discuss in next blog.