Diagnosing and Resolving Latch Contention on SQL Server

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 https://go.microsoft.com/fwlink/?LinkId=223367