Disks - Queue Length alarm
The Disk Queue Length alarm becomes active when the maximum disk queue length of any disk exceeds a threshold. Sustained high disk queue length is a good indicator that you have a disk subsystem bottleneck, and usually means that I/O times will be degraded.
Since this is a Windows-based alarm, it can be impacted by I/O that was performed by non-SQL Server processes.
The occurrence of this alarm does not necessarily indicate a problem with the SQL Server instance, however, SQL Server (and anything else on the server that performs disk I/O) will feel the impact of slower disk throughput.
When the alarm is raised
- The SQL Server | SQL Activity Drilldown | Summary page to see if SQL Server is generating high amounts of disk activity. The SQL Server IO chart shows the rate (pages per second) for each type of I/O that SQL Server is performing. If SQL Server is not generating a lot of I/O activity, then it is most likely being caused by some other Windows process, or by Windows itself.
- The Windows Server | Disks Drilldown | Logical Disk Activity page to see which disk(s) are being hit the hardest. This, and the list of disks that contain SQL Server databases (from the SQL Server | Databases Drilldown) may help to determine if SQL Server is generating the I/O.
- The SQL Server | SQL Activity Drilldown | Sessions page to see what SQL Server processes are executing at the time, and the SQL currently being executed.
- Consider moving database files to faster disks. If you are not using hardware RAID, consider purchasing a RAID subsystem. If you are using RAID-5 for write-intensive files (such as Database Logs or heavily updated database files), consider moving to a faster RAID implementation (RAID-0 or RAID-10).
- In some cases, you can speed up all disk I/O by reviewing the RAID options on your RAID controllers. One example is to enable disk-write caching, as long as your disk subsystem is protected by battery backups or UPS.
If the Checkpoint process is generating a lot of I/O (see the Ckpt writes statistic on the SQL Server | SQL Activity Drilldown | Summary page | SQL Server IO chart) review your Recovery Interval setting in the SQL Server | Configuration Drilldown.