Troubleshooting slow running query using Extended Events Wait info event

Extended Events introduced in SQL Server 2008 is a detailed event infrastructure for SQL Server. If your environment is running with more SQL Server's of version > 2008, it is the right time to learn about Extended events since they help you a lot in troubleshooting SQL Server performance issues.

In general there are two things possible in SQL Server: Running or Waiting. Most often the percentage of wait will be more than percentage of running in a given Environment. So you should start with minimizing the waits percentage and once that comes below the running percentage, start focussing on minimizing the running percentage by tuning the query (query hints, plan guide etc..). So, I'm more interested in the troubleshooting methodology of using Waits info in case of SQL Server peformance issues because you fix the main culprit first. This whitepaper talks about the methodolody of troubleshooting performance issues based on Waits and Queues. (Note: Sometimes you might have to troubleshoot both waits and running percentage parallely depening on the situation)

While we troubleshoot a performance issue based on waits, there is a limitation that all existing DMV's, DMF's provide server lever waits. So if you need to troubleshoot waits encountered by one particular session or a query... What is the option??. The option is using the Extended Events available in SQL Server 2008 and higher version.

Here is the XEvents query to track the waits information for a query being executed from particular session. I'm also attaching the t-sql script, if the browser is not rendering this page correctly.

This code is provided AS-IS with no implied warranty

 -- *** Change the Session ID values as per your need *** 



-- Check for existing session

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='Waits_of_Particular_Session')

  DROP EVENT session Waits_of_Particular_Session ON SERVER;

GO



-- Create a session and add events and actions

CREATE EVENT SESSION Waits_of_Particular_Session 

ON SERVER

ADD EVENT sqlserver.sql_statement_starting

(

    ACTION (sqlserver.session_id,

        sqlserver.sql_text,

        sqlserver.plan_handle)

    WHERE 

        sqlserver.session_id > 52 AND sqlserver.session_id < 54

),

ADD EVENT sqlos.wait_info 

(

    ACTION (sqlserver.session_id,

        sqlserver.sql_text,

        sqlserver.plan_handle)

    WHERE 

        sqlserver.session_id > 52 AND sqlserver.session_id < 54

),

ADD EVENT sqlos.wait_info_external

(

    ACTION (sqlserver.session_id,

        sqlserver.sql_text,

        sqlserver.plan_handle)

    WHERE 

        sqlserver.session_id > 52 AND sqlserver.session_id < 54

),

ADD EVENT sqlserver.sql_statement_completed

(

    ACTION (sqlserver.session_id,

        sqlserver.sql_text,

        sqlserver.plan_handle)

    WHERE 

        sqlserver.session_id > 52 AND sqlserver.session_id < 54

)

ADD TARGET package0.asynchronous_file_target

    (SET filename=N'C:\Temp\Waits_of_Particular_Session.xel')

WITH (MAX_DISPATCH_LATENCY = 5 SECONDS, EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS, TRACK_CAUSALITY=ON)

GO



-- Start the Session

ALTER EVENT SESSION Waits_of_Particular_Session ON SERVER

STATE = START

GO



-- Run the query from the SPID 



-- Code to stop the session once the query execution completes

ALTER EVENT SESSION Waits_of_Particular_Session ON SERVER

STATE = STOP

GO



-- Parse the XML to show wait details

SELECT

    event_xml.value('(./@name)', 'varchar(1000)') as Event_Name,

    event_xml.value('(./data[@name="wait_type"]/text)[1]', 'nvarchar(max)') as Wait_Type,

    event_xml.value('(./data[@name="duration"]/value)[1]', 'int') as Duration,

    event_xml.value('(./data[@name="opcode"]/text)[1]', 'varchar(100)') as Operation,

    event_xml.value('(./action[@name="session_id"]/value)[1]', 'int') as SPID,

    event_xml.value('(./action[@name="sql_text"]/value)[1]', 'nvarchar(max)') as TSQLQuery,

    event_xml.value('(./action[@name="plan_handle"]/value)[1]', 'nvarchar(max)') as PlanHandle

FROM    

    (SELECT CAST(event_data AS XML) xml_event_data FROM sys.fn_xe_file_target_read_file('C:\Temp\Waits_of_Particular_Session*.xel', 'C:\Temp\Waits_of_Particular_Session*.xem', NULL, NULL)) AS event_table

    CROSS APPLY xml_event_data.nodes('//event') n (event_xml)

WHERE  

    event_xml.value('(./@name)', 'varchar(1000)') IN ('wait_info','wait_info_external')   

    

-- Code to drop the session

DROP EVENT SESSION Waits_of_Particular_Session ON SERVER;

Start the XEVENTS session -> Run your queries from the SPID being monitored by this XEVENTS session -> Once the query execution completes, stop the XEVENT session -> Run the portion of query above which will parse the XML and show you the waits detail for the particular SPID monitored.

Here is a sample output when I ran it in my machine:

Refer https://blogs.msdn.com/b/sqlsakthi/archive/2011/02/08/different-status-of-a-spid-in-sql-server-and-what-do-they-mean.aspx for status of a SPID when waiting for a Resource.

Wait_of_Particular_Session.sql

Comments

• Anonymous
  February 21, 2011
  You can check the status of a SPID using any of these queries: SELECT status ,* FROM sys . sysprocesses