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