SQL Server Latches and their indication of performance issues

Question

I am trying to understand a potential performance issue with our database (SQL 2008) and in particular one performance counter, SQLServer:Latches\Total Latch Wait Time Total Latch Wait Time (ms). We are seeing a slow down in DB response times and the only correlating spike that I can match it with is a spike in Total Latch Wait Time and Latch Waits/sec. I am not seeing any particular bottleneck in disk IO, CPU usage or memory.

The common explanation of a SQLServer latch is that it is a lightweight lock, but I am trying to get a more detailed understanding of what a latch is, how it differs from a lock and what the high amount of them that I am seeing may be an indicator for.

What changed? Any configuration changes, hardware modifications, application code changes, schema changes? — Remus Rusanu, Dec 14 '09 at 22:17
Nothing has changed as far as code, configuration or load from the day before. Don't see any file growth activity, backups running, external processes running etc. — John Lemp, Dec 14 '09 at 22:37
these posts have been extremely helpful in understanding Latches better: http://blogs.msdn.com/psssql/archive/2009/07/08/q-a-on-latches-in-the-sql-server-engine.aspx http://blogs.msdn.com/psssql/archive/2009/01/28/hot-it-works-sql-server-superlatch-ing-sub-latches.aspx — John Lemp, Dec 17 '09 at 01:49

score 11 · Answer 1 · answered May 30 '13 at 01:14

11

This maybe a really basic error to professional DBA... but this is what I found with our high latch problem, and this thread ranks very high in search results. I thought I'd share our bit that it may help someone else.

on newer dual / multi processor server using NUMA memory architecture, the max degree of parallelism should be set to the actual core number per processor. in our example we had dual xenon with 4 cores each, and with hyper threading it appears as 16 logical processors to SQL.

Locking this value from the default 0 to 4 cut the high latch on some queries down immediately.

Our latch ran 1000ms+ up to 30,000ms on some occasions.

answered May 30 '13 at 01:14

michael x

111
1
2

1

Thank you for this answer. Set the max degree of parallelism to 8 from 0, SQL thought we were on 16 cores. Latch locking went from ~15,000 average to <1,000 – Tom Gullen Sep 10 '14 at 13:43
@TomGullen Where is this `max degree of parallelism` setting? Is it the `maximum worker threads` option that I see on SSMS->RightClick Instance->Properties->Processors? – user2173353 Jul 16 '15 at 09:49
No. Go to Advanced settings and in the "Parallelism" section, look for "Max Degree of Parallelism". Change it to 4 or 8 depending on how many cores you have. I would also advise that you don't change it in production environment before testing that in a matching QA environment. – Nicolas de Fontenay Aug 26 '15 at 20:10

score 9 · Accepted Answer · answered Dec 14 '09 at 22:15

I recommend you looke into sys.dm_os_latch_stats and see what type of latches have increased contention and wait types, compared to previous base-line.

If you see a spike in the BUFFER type latches it means it is driven by updates conflicting to modify the same page. Other latch types have also short explanation in the MSDN and can guide you toward the problem root cause. For those marked 'internal use only', you're going to have to open a support case with MS, as a detailed explanation of what they mean is on the verge of NDA.

You should also look into sys.dm_os_wait_stats. If you see an increase of PAGELATCH_*, then it is the same problem as the BUFFER type latch above, contention in trying to modify same page, aka. as an update hot-spot. If you see an increase PAGEIOLATCH_*then your problem is the I/O susbsytem, it takes too long to load the pages in memory when they are needed.

score 0 · Answer 3 · answered Jan 08 '17 at 19:27

Reference taken from this blog:

Using sys.dm_db_index_operational_stats:

SELECT 
    OBJECT_NAME(object_id)
    ,page_latch_wait_count
    ,page_latch_wait_in_ms
    ,tree_page_latch_wait_count
    ,tree_page_latch_wait_in_ms  
    ,Page_io_latch_wait_count
    ,Page_io_latch_wait_in_ms
FROM sys.dm_db_index_operational_stats (DB_ID(), NULL, NULL, NULL)

Using sys.dm_os_latch_stats:

SELECT * FROM sys.dm_os_latch_stats  
WHERE latch_class = 'buffer'

score -4 · Answer 4 · edited Aug 19 '15 at 17:59

-4

sp_configure 'max degree of parallelism', 8
go
reconfigure
go

edited Aug 19 '15 at 17:59

Nate Barbettini

51,256
26
134
147

answered Aug 19 '15 at 17:52

Sudhakar

1

1

Can you add an explanation as to why this will help the OP? – Nate Barbettini Aug 19 '15 at 17:59

SQL Server Latches and their indication of performance issues

4 Answers4

Linked