SQL: Connection response time


Symptoms : Disk Read/Write response times exceed threshold

Impact : Medium

High levels of disk I/O may result in slow disk response times. This may create a bottleneck, degrading the user experience, resulting in poor efficiency of your organization’s operations. A long-term consequence of over-activity in disk hardware may be a reduction in hardware life, or even catastrophic disk crashes in real-time.

Expected behavior :

There is no standard metric for response times. Our recommendation is for disks to respond within the set threshold.

Possible causes

IO subsystem level problem  Priority : Medium
Failure of some hardware component handling data traffic or storage is the most likely reason for this error. The elements involved are:

  • Drives
  • Controllers
  • Incorrect Drivers

Recommended action :
Identify the faulty component and repair/replace.

Operating system conflict  Priority : Medium
Besides SQL database functions, the server performs functions relating to other operating system activities, such as anti-virus scan, disk clean-up, OS updates etc. If an unusually high level of these coincides with high database activities, there may be excessive load on the disk from competing elements.
Recommended action :
If possible, move OS traffic onto a second network. Otherwise, schedule backups and scans to occur at periods of lowest user activity.

Running out of disk space  Priority : Medium
If the program calls for output to the disk ( I/O ) and the disk is nearly full (generally the optimal threshold is below 90% of total capacity), the disk will start to slow down as it searches for free space. This will cause the program to wait for progressively longer periods.
Recommended action :
Examine the disk free-space reporting from AimBetter Dashboard > Resources > Disk. If necessary, working with operating system reports, identify whether there is sufficient space in unnecessary files (for example, old or redundant copies of data), to delete these files and run a disk clean-up. If there is still not enough, further disk capacity must be added.)

SQL queries with high disk I/O  Priority : Medium
See our explanation of coding problems causing deadlocks here.
Recommended action :
Optimize code. Possible actions :

  • Redesign program to maximize use of indexed data.
  • Redesign table structures to match the requirements of the programs by building indexes.
  • Make use of temporary tables.

Missing or corrupt indexes  Priority : Medium
Missing indexes will cause extensive data searching
Recommended action :
See our explanation for missing/corrupt indexes here.

Background

This alert means that the hard disk is too busy, therefore cannot apply the input/output operations at the pace that the programs demand. Since demand for disk activity may be coming both from the operating system and SQL, use AimBetter QAnalyzer to identify any queries that are performing high levels of physical I/O or CPU usage.Disk I/O overload can also manifest itself as exceptionally high CPU load. A task waiting for disc IO will be taken off the CPU until it has been answered by the scheduler. What this means is that the CPU is 100% busy but in power saving mode because of all the waiting while longer query queues are forming. This will slow all CPU-dependent processes, resulting in overall degradation in server performance.

Microsoft SQL Server uses Microsoft Windows operating system input/output (I/O) calls to perform read and write operations on your disk. SQL Server manages when and how disk I/O is performed, but the Windows operating system performs the underlying I/O operations. The I/O subsystem includes the system bus, disk controller cards, disks, tape drives, CD-ROM drive, and many other I/O devices. Disk I/O is frequently the cause of bottlenecks in a system.