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
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
This is only a preview. Your comment has not yet been posted.
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.
Posted by: |