![]() The excessive disk IO could easily have its root cause elsewhere. You may have evidence that a particular bottleneck exists, in this case SQL Server disk IO, but it's dangerous to conclude that inadequate capacity or configuration of the disk subsystem is the cause of the problem. If you focus on only one particular performance metric, then it is easy to misdiagnose SQL Server performance problems. What action should you take to resolve the problem? Before you contact your system administrator to see if there is anything to be done to increase disk capacity, it's wise to drill deeper. It seems that the disk subsystem is becoming a bottleneck it is struggling to keep up with the number of requests, SQL Server disk IO performance is deteriorating, and queues are forming. It suggests periods of high latency associated with reading data from disk, accompanied by a sharp rise in the average disk queue length. Can you fix it?"Īs the DBA, you decide to spin up Windows Resource Monitor and immediately notice an abnormal pattern of disk activity on the server.įigure 1 – Windows Resource Monitor showing an unusual patttern of disk activity on the serverĬoncerned to see the disk subsystem spiking to maximum capacity, you take a snapshot view of some disk-related counters in Performance Monitor (PerfMon).įigure 2 – Average Disk Queue Length and sec/Read in SQL Server Performance Monitor In fact, using a single metric will often lead to an incorrect diagnosis of a problem.Ĭomplex interdependencies exist between patterns of CPU, IO, and memory use in SQL Server, and we need to see the full picture before taking 'knee-jerk' corrective action, such as adding more memory, or more disk throughput capacity, or changing configuration settings. When attempting to diagnose poor performance in SQL Server, it's a mistake to leap to conclusions regarding the root cause, based on any single piece of diagnostic data, such as CPU usage, or SQL Server disk IO capacity. Understanding Disk I/O & How To Resolve Bottlenecks | Redgate Skip to content
0 Comments
Leave a Reply. |