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.