Help: SQL Server

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.