SQL Server Wait Stats analysis and Perfmon Counters recorded high ASYNC_IO_COMPLETION , IO_COMLETION  and  BACKUPIO on Production SQL Servers. At first, I thought just one server was underperforming. As more jobs starting delaying ,it was obvious more than one server was experiencing a slowdown.

 A typical job taking 34 minutes, was now taking 4 hrs 45 minutes. The common thread to the delays was writes to disk. In contrast backups to tape were fine

 To verify these claims I completed some SQL Server wait analysis. To maintain consistency I cleared the history collection  with dbcc sqlperf

 SQLPERF(‘sys.dm_os_wait_stats’,CLEAR)

 And stopped other jobs running on the server

 I issued a BACKUP to disk and then a RESTORE from that disk onto another disk . Both high IO – bound activities.

 During the BACKUP phase  wait stats recorded were:

BACKUPIO - is not specifically an IO wait performance measure. But indicates latency.

ASYNC_IO_COMPLETION – normally occurs for long –running IO bound ops – e.g BACKUP,CREATE DATABASE,ALTER DATABASE etc.

 During the RETORE phase the wait stats recorded were:

BACKUPIO

IO_COMPLETION

I then stopped SQL Server and attempted a file copy onto another disk on the same server. Very Slow!

 There wasn’t  significant  memory pressure

 I completed the same process on another server , the same result.

 To verify the waits analysis I completed some Perfmon analysis , which looks at Queues.

 The counters I looked at  Avg.Disk Sec/Write , focusing on disk latency.  The underlying disk system uses SAN so Average Disk Queue Length was out of the question .

 According to Microsoft these are the Avg.Disk Sec/Write guidelines

 Less than 10 ms - very good

Between 10 - 20 ms - okay

Between 20 - 50 ms - slow, needs attention

Greater than 50 ms – Serious I/O bottleneck

This is a serious IO bottleneck , which verified the Wait Stats analysis . I presented the information to the SAN administrator . Using this information as a basis , we were able to diagnose the underlying SAN related problem – and offer an alternative to users

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

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