Find sql queries matching wait types

23 November,2013 by Jack Vamvas

Question: How do you collate queries with wait types?  Finding the current waits is straightforward using the sys.dm_os_wait_stats  DMV , but how can you break down and associate with specific sql statements and the wait type?   A typical example  , maybe you’ve found CXPACKET as the main wait type.

 At this point, you want to find the queries being slowed down and the queries which are causing delays for other queries

Answer: This is a good question and is a common requirement in a tuning procedure. If you are using the waits and queues methodology , the first step would be to identify waits at the SQL Server Instance Level.  Once you’ve identified the waits  ( at SQL Server Instance Level) , you may want to identify the queries

 Options are to find queries  related to the wait type

1)     On a regular basis , extract a sample of the waiting queries and record queries waiting due to the wait type.  Use a  great script   which I’ve included below and is courtesy of TechNet script center

2)     Capture long running queries and  tune them

3)     Capture wait states on a procedure using the power of Extended Events.

4)     Use the sys.dm_os_waiting_tasks DMV – returning the wait queue of tasks waiting on a resource

 

if exists (select * from sys.objects where object_id = object_id(N'[dbo].[get_statements_from_waiter_list]') and OBJECTPROPERTY(object_id, N'IsProcedure') = 1) 
    drop procedure [dbo].[get_statements_from_waiter_list] 
go 
 
create proc get_statements_from_waiter_list (@wait_type nvarchar(60)=NULL) 
as 
select  
        r.wait_type 
        ,r.wait_time 
        ,SUBSTRING(qt.text,r.statement_start_offset/2,  
            (case when r.statement_end_offset = -1  
            then len(convert(nvarchar(max), qt.text)) * 2  
            else r.statement_end_offset end -r.statement_start_offset)/2)  
        as query_text 
        ,qt.dbid, dbname=db_name(qt.dbid) 
        ,qt.objectid 
        ,r.sql_handle 
        ,r.plan_handle 
FROM sys.dm_exec_requests r 
cross apply sys.dm_exec_sql_text(r.sql_handle) as qt 
where r.session_id > 50 
  and r.wait_type = isnull(upper(@wait_type),r.wait_type) 
go 
 
exec get_statements_from_waiter_list


Read More

SQL Server – AUTO CREATE STATISTICS - SQL Server DBA

SQL Server – Find high impact queries with sys.dm_exec_query_stats

SQL Server execution plan as text - SQL Server DBA

SQL Server Query Optimizer and Statistics - SQL Server DBA

SQL Performance tuning - Asking the right question


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Find sql queries matching wait types


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer