Chat now with support
Chat with Support

Spotlight on SQL Server Enterprise 11.7 - Release Notes

Disks - Queue Length alarm

Note: This alarm is disabled by default. It is superseded by the SQL Server I/O Stall Time Alarm. To enable the alarm see Configure | Alarms.

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

Look at:

  • 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.

Configuration

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating