Last month I wrote SQL Server Wait Types and Queues series SQL SERVER – Summary of Month – Wait Type – Day 28 of 28. I had great fun to write the series. I learned a lot and I felt this has created some deep interest on the subject with others. One reader wanted to find latch statistics, and SQL Server already has a DMV for it.
I recently received very interesting question from one of the reader after reading SQL SERVER – PAGELATCH_DT, PAGELATCH_EX, PAGELATCH_KP, PAGELATCH_SH, PAGELATCH_UP – Wait Type – Day 12 of 28 that if they can know what kind of latches are waiting and what is their count.
Absolutely! SQL Server team has already provided DMV which does the same.
-- Latch
SELECT *
FROM sys.dm_os_latch_stats
ORDER BY wait_time_ms DESC
Above script will return you details about how many latches were waiting for how long.
After going over this script I feel like going deep into the subject further. I will post a blog post on the subject soon.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
