SQL Server Customer Advisory Team - SQL Server Best Practices

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

Whitepapers

Diagnosing and Resolving Latch Contention on SQL Server