Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

  • Blog Stats

    • 1,806,831 hits
  • Select GETDATE()

    September 2020
    M T W T F S S
     123456
    78910111213
    14151617181920
    21222324252627
    282930  

Posts Tagged ‘dm_exec_query_profiles’

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)

image

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.

image

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.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • Posted in SQL 2014 Learning Series, SQL Server 2014 | Tagged: , , , | Leave a Comment »

    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.

    image

    If we run again, we can see row_count is getting increased.

    image

    There are other useful columns which we would discuss in next blog.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • Posted in SQL 2014 Learning Series, SQL Server 2014 | Tagged: , , , | 2 Comments »