Check SQL Wait Types to identify potential problems on sql servers

Production DBAs often end firefighting issues on live servers and this can easily side track the DBA’s from the actual problems underlying a Database or at sql server instance level. This is where my Sproc will come in handy.  I have been working with sql wait types and identifying the problem areas.

The sproc is written in a way that once you deploy the sproc to your management database you can run this sproc from a sql agent job daily or weakly which sends an email to the DBA team or individual.

To stay focused on the subject, we will assume you have Database mail setup with profile ‘SqlHealthCheck’

/*======================================================

— Author:  Suleman Mohammed
— Create date: 21-02-2012
— Description:

use this Stored procedure to identify potential problems on the sql server based on wait types; Sproc is designed to send a report which the DBA can analyze and take action against.

========================================================

— Version Control 1.0 – 21-02-2012 Created Sproc – SM
========================================================*/

CREATE PROCEDURE [Healthcheck].[uspCheckWaitTypes]
AS
SELECT  wait_type ,
waiting_tasks_count ,
wait_time_ms ,
max_wait_time_ms ,
signal_wait_time_ms ,
CASE WHEN wait_type = ‘OLEDB’
THEN ‘Optimize the query on the linked server’
WHEN wait_type = ‘ASYNC_NETWORK_IO’
THEN ‘Check clients submitting queries that produce large result sets’
WHEN wait_type = ‘CXPACKET’
THEN ‘Queries running in parallel and therefore expensive, Identify queries and attempt to tune’
WHEN wait_type = ‘PREEMPTIVE_OS_WRITEFILEGATHER’
THEN ‘Long Autogrow events, try plan and manually grow data and log files’
WHEN wait_type = ‘PREEMPTIVE_OS_AUTHENTICATIONOPS’
THEN ‘Problem talking to a Domain Controller, check with Infrastructure team to fix connectivity issues’
WHEN wait_type = ‘WRITELOG’
THEN ‘Poor I/O System performance writing to t-log, try tune the log file and check with Infrastructure team to improve IO system’
WHEN wait_type = ‘SOS_SCHEDULER_YIELD’
THEN ‘High Concurrency and competition for CPU time, Tune application code or Request Infrastructure to add more cores’
WHEN wait_type LIKE ‘LCK_M_%’
THEN ‘Look at Index Tuning and Isolation Levels’
WHEN wait_type LIKE ‘PAGEIOLATCH_%’
THEN ‘I/O Latency, Check with Infrastructure team to investigate and improve’
ELSE ‘Check with PROD DBA what this means’
END AS [Action Required]
INTO    #CheckWaitTypes
FROM    sys.dm_os_wait_stats
WHERE   ( wait_type IN ( ‘PREEMPTIVE_OS_WRITEFILEGATHER’,
‘PREEMPTIVE_OS_AUTHENTICATIONPS’,
‘ASYNC_NETWORK_IO’, ‘CXPACKET’, ‘OLEDB’,
‘SOS_SCHEDULER_YIELD’, ‘WRITELOG’ )
OR wait_type LIKE ‘LCK_M_%’
OR wait_type LIKE ‘PAGEIOLATCH_%’
)
AND wait_time_ms <> 0
ORDER BY wait_time_ms DESC

DECLARE @cnt INT
SELECT  @cnt = COUNT(1)
FROM    #CheckWaitTypes

IF ( @cnt > 0 )
BEGIN

DECLARE @strsubject VARCHAR(100)
SELECT  @strsubject = ‘Identified Problem areas to fix on ‘
+ @@SERVERNAME

DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML = N'<H1>Problem areas to address on – ‘
+ @@SERVERNAME + ‘</H1>’ + N'<table border=”1″>’
+ N'<tr><th>wait_type</th>’ + N'<th>waiting_tasks_count</th>’
+ N'<th>wait_time_ms</th>’ + N'<th>max_wait_time_ms</th>’
+ N'<th>signal_wait_time_ms</th>’
+ N'<th>Action Required</th></tr>’
+ CAST(( SELECT td = wait_type ,
” ,
td = waiting_tasks_count ,
” ,
td = wait_time_ms ,
” ,
td = max_wait_time_ms ,
” ,
td = signal_wait_time_ms ,
” ,
td = [Action Required]
FROM   #CheckWaitTypes
FOR
XML PATH(‘tr’) ,
TYPE
) AS NVARCHAR(MAX)) + N'</table>’ ;

EXEC msdb.dbo.sp_send_dbmail @from_address = ‘sqlhealthcheck@sqlish.com’,
@recipients = ‘suleman@sqlish.com’, @subject = @strsubject,
@body = @tableHTML, @body_format = ‘HTML’,
@profile_name = ‘SqlHealthCheck’
END

DROP TABLE   #CheckWaitTypes

/*======================================================================*/

Sample Report here:-

Problem areas to address on – <ServerName>

wait_type waiting_tasks_count wait_time_ms max_wait_time_ms signal_wait_time_ms Action Required
CXPACKET 1642681274 1889936999 56152 166557463 Queries running in parallel and therefore expensive, Identify queries and
attempt to tune
ASYNC_ 

NETWORK

_IO

45997676 91777437 6168 860414 Check clients submitting queries that produce large result sets
SOS_ 

SCHEDULER

_YIELD

261620858 35060407 695 34782131 High Concurrency and competition for CPU time, Tune application code or
Request Infrastructure to add more cores
LCK_M_S 5153 19583179 56704 756 Look at Index Tuning and Isolation Levels
WRITELOG 2308330 2032527 1466 81792 Poor I/O System performance writing to t-log, try tune the log file and
check with Infrastructure team to improve IO system
PAGEIOLATCH 

_UP

59867 1141321 2311 2030 I/O Latency, Check with Infrastructure team to investigate and
improve
PAGEIOLATCH 

_SH

64532 677919 2055 2379 I/O Latency, Check with Infrastructure team to investigate and
improve
OLEDB 62998784 637935 5862 0 Optimize the query on the linked server
PAGEIOLATCH 

_EX

43572 338466 1801 901 I/O Latency, Check with Infrastructure team to investigate and
improve
LCK_M_SCH_S 336 131688 11747 33 Look at Index Tuning and Isolation Levels
LCK_M_IS 38 76991 6835 22 Look at Index Tuning and Isolation Levels
LCK_M_SCH_M 95 35974 12105 0 Look at Index Tuning and Isolation Levels
PREEMPTIVE 

_OS_

WRITE

FILEGATHER

25 5122 4788 0 Long Autogrow events, try plan and manually grow data and log
files
LCK_M_X 71 3521 1092 2 Look at Index Tuning and Isolation Levels
LCK_M_IX 16 1912 810 3 Look at Index Tuning and Isolation Levels
LCK_M_U 2 2 1 0 Look at Index Tuning and Isolation Levels

 

This entry was posted in SQL Server, T-SQL Scripts, Tricks, WaitTypes and tagged , . Bookmark the permalink.

2 Responses to Check SQL Wait Types to identify potential problems on sql servers

  1. Adeci says:

    your content made me want to read more and more. congratulations for the work. thanks!

    • Suleman says:

      Thanks Adeci. I try to keep it bite sized so that its easier to grasp and stays on the mind and reminds a savvy to go learn more about the technology.

Leave a Reply