SQL Server 2008 Monitor Resource Waiting Resource Waiting Resource: Does the Latch Contain a Processor or Only an IO Disk?

Question

In SQL Server 2008 Activity Monitor, I can see that the latency latency (not the buffer latch) increases at a speed of more than 10,000 ms / s. The average number of waiters is less than 10, but this is by far the highest expectation area in a very busy system. The IO drive is almost zero, and the page has a lifespan of more than 80,000, so I know that this is not slowed down by the disk hardware and does not even affect the SAN cache. Does this mean that SQL Server is waiting for the CPU (i.e., it allows blocking the byollion) or is it waiting for data from the local server cache to be processed?

Reference Information. The system is a 48-core SQL Server 2008 Enterprise with a capacity of up to 64 GB. Requests are less than 100 ms during the response - at the moment - but I'm trying to understand the bottlenecks before they reach the level of 100 times.

Class Count Sum Time Max Time ACCESS_METHODS_DATASET_PARENT 649629086 3683117221 45600 BUFFER 20280535 23445826 8860 NESTING_TRANSACTION_READONLY 22309954 102483312 187 NESTING_TRANSACTION_FULL 7447169 123234478 265 

Answer 1

Some latches are IO, some are CPU, others are others. It depends on what type of latch you see. sys.dm_os_latch_stats will show which latches are hot in your deployment.

I would not worry about the last three points. The two nesting_transaction processes look very healthy (low medium, low maximum). The buffer is also fine, more or less, although the maximum time of 8s is a bit.

The AM_DS_PARENT latch is associated with parallel queries / parallel scans. Its average is ok, but a maximum of 45 s is quite high. Can't say too many details. I can say that a long wait time for this type of latch indicates that your IO subsystem may run into spikes (and a fixation delay of BUFFER max 8s confirms this).