SQL 2014 Learning Series # 12 – New DMV – sys.dm_exec_query_profiles (Part 2)
Posted by blakhani on June 12, 2014
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.