In this example, we see two wait types, plus the query text and plans for the associated requests.įigure 3 – the ASYNC_NETWORK_IO and PAGEIOLATCH waits Querying the sys.dm_os_waiting_tasks dynamic management view (DMV) reveals any currently-executing requests that are waiting for resources, joining to other DMVs for details of the associated sessions and requests, as demonstrated, for example, by Pinal Dave. This free eBook, by Jonathan Kehayias and Erin Stellato, provides an excellent overview of wait statistics, and good descriptions of some of the most common wait types, including those covered in this article. SQL Server Performance Tuning using Wait Statistics: A Beginner's Guide By analyzing the waits, we can identify the resources that are main causes of contention and queues on a busy server. But where do you start? Digging deeper Wait statisticsĪ good place to start is with an analysis of SQL Server wait statistics, indicating the most prevalent reasons why SQL Server sessions were forced to pause processing (wait) before gaining access to a resource required to fulfill the request. You need to collect further data that will confirm the cause of the problem. 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. Math.Understanding Disk I/O
0 Comments
Leave a Reply. |