Diagnosing and Resolving Latch Contention on SQL Server

  • Comments 6
Authors: Ewan Fairweather, Mike Ruthruff
 
Contributors: Thomas Kejser, Steve Howard
 
Technical Reviewers:
Fabricio Voznika, Lindsey Allen, Alexei Khalyako, Prem Mehra, Paul S. Randal, SQLskills.com, Benjamin Wright-Jones, Marcel van der Holst, Ryan Stonecipher, Pranab Mazumdar, Gus Apostol, Simon Rapier
 
Published: July 2011 
 
Abstract:
This paper provides in-depth information about the methodology the Microsoft SQL Server Customer Advisory Team (SQLCAT) team uses to identify and resolve issues related to page latch contention observed when running SQL Server 2008 and SQL Server 2008 R2 applications on high-concurrency systems. 
 
Executive Summary:
Welcome to the Diagnosing and Resolving Latch Contention on SQL Server paper. While working with mission critical customer systems the Microsoft SQL Server Customer Advisory Team (SQLCAT) have developed a methodology which we use to identify and resolve particular resource contention issues observed when running SQL Server 2008 and SQL Server 2008 R2 on high concurrency systems.
We created this guide to provide in-depth information about how we use this methodology to identify and resolve resource contention issues related to page latch contention observed when running SQL Server 2008 and SQL Server 2008 R2 applications on high concurrency systems with certain workloads.  In recent years, the traditional approach of increasing computer processing capacity with faster CPUs has been augmented by building computers with multiple CPUs and multiple cores per CPU. As of this writing, the Intel Nehalem CPU architecture accommodates up to 8 cores per CPU, which when used in an 8 socket system provides 64 logical processors, which can then be doubled to 128 logical processors through the use of hyper-threading technology. As the number of logical processors on available to SQL Server increase so too does the possibility that concurrency related issues may occur when logical processors compete for resources.
The recommendations and best practices documented here are based on real-world experience during the development and deployment of real world OLTP systems.
To continue reading please download a copy of this guide in chm, pdf, or docx form, go to http://go.microsoft.com/fwlink/?LinkId=223367
Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • Post
  • Diagnosing and Resolving Spinlock Contention on SQL Server sqlcat.com/.../2011

  • Guys,

           First a BIG THANKS for the document. I'm sure is gonna help a lot of SQL users understand the functionality/behavior of latches.

    The document, on page 8 refers to sys.dm_os_latch_stats twice whereas it should have been refering to sys.dm_db_index_operational_stats.

    Cheers,

    Shankar

  • Pingback from  SQL Server: Diagnosing and Resolving Latch & Spinlock Contention … « notes from a software alchemist

  • Thanks Shankar, I've flagged this with our documentation team, they'll edit the typo and republish.  Glad you liked the paper.

    Ewan

  • Pingback from  New whitepapers on latches and spinlocks published | devblogging.com

  • this is an interesting article with some useful information for analysing latch contention. However, I think it the resolution techniques focus a little too heavily on physical DB reorganisation & could be balanced with more discussion on hardware optimisation, as this is very often a more practical approach than re-organising indexes, which very often isn't possible with vendor applications. The techniques demonstrated are certainly useful in some scenarios, but even with internal apps which you can control physical structures, changing the physical DB requires software testing (as you have recommended) where hardware upgrades require very little software testing and are often far, far more practical approaches. Eg, fixing poorly configured RAID, upgrading to SSD or increasing RAM, faster RAM, faster CPU etc. All up however, this is a useful article so thanks

Page 1 of 1 (6 items)
Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • Post