In my earlier post we have discussed about the use of the DMV. In this blog, we would see demo about the feature. In the last post we have learned that we must enable actual execution plan to get information about the query in sys.dm_exec_query_profiles. How would you do that if the queries are coming from application? You can’t ask application team to add “set statistics profile on” in their code. Well, there are multiple ways to handle such situation. (all of them might cause performance issue as we are doing additional work)

• Start a Server Side trace (profiler UI has more performance impact) with any one of below events (both not needed)

Here is the code which can be used to create server side trace. This code is auto generated by profiler. Event id 98 = “Showplan Statistics Profile”

-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5 

-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share

exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, NULL 
if (@rc != 0) goto error

-- Client side File and Table cannot be scripted

-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 98, 10, @on
exec sp_trace_setevent @TraceID, 98, 12, @on


-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

-- display trace id for future references
select TraceID=@TraceID
goto finish

error: 
select ErrorCode=@rc

finish: 
go

• Start an extended event session with event query_post_execution_showplan as shown in UI below.

We can send the data to a file (if we want the plans to be saved) or send them to ring buffer target.

CREATE EVENT SESSION [Capture_Query_Plan] ON SERVER 
ADD EVENT sqlserver.query_post_execution_showplan 
ADD TARGET package0.ring_buffer
WITH (STARTUP_STATE=OFF)
GO

Once we start any one of above, we should be able to profiler the query plan in the real time. If we use any of the method, it is going to cause performance issue so make sure we are running for limited amount of time. Here is the query which can be used to get the information once traces (profiler or XE) is running.

SELECT physical_operator_name, 
       row_count, 
       estimate_row_count, 
       elapsed_time_ms, 
       cpu_time_ms, 
       h.dbid, 
       h.objectid, 
       h.text, 
       ph.query_plan, 
       p.* 
FROM   sys.dm_exec_query_profiles p 
       CROSS apply sys.Dm_exec_sql_text(sql_handle) h 
       CROSS apply sys.Dm_exec_query_plan(plan_handle) ph 
WHERE  session_id <> @@SPID 
       AND session_id NOT IN (SELECT session_id 
                              FROM   sys.dm_exec_sessions 
                              WHERE  program_name LIKE 'SQL Server Profiler%') 
ORDER  BY session_id, 
          node_id   

To learn more, you can run below query, which should take little more time so that you get chance to look at real time data.

SELECT * 
FROM   sys.all_columns c 
       CROSS JOIN sys.all_objects o 
WHERE  type = 'P' 
ORDER  BY c.is_computed 

On my machine, it look takes little more than 4 minutes to complete and in the mean time, we can check data in DMV (using query provided earlier) and see that almost all the work finishes instantly but nested loop takes a lot of time here. And it’s purely due to amount of data I have thrown to nested loop join.

Hope this helped you in bringing your knowledge one step ahead than earlier.

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.