Professional SQL Server 2012 Internals and Troubleshooting

Chapter 7

Latches and Spinlocks

WHAT’S IN THIS CHAPTER?

• Recognizing the symptoms of latch and spinlock contention
• Describing the types of systems which are susceptible to latch and spinlock contention
• Descriptions of latch types and modes
• Common contention-prone scenarios and how to resolve them

WROX.COM CODE DOWNLOADS FOR THIS CHAPTER

The wrox.com code downloads for this chapter are found at www.wrox.com/remtitle.cgi?isbn=1118177657 on the Download Code tab. The code is in the Chapter 7 download and individually named according to the names throughout the chapter.

OVERVIEW

When I speak to database consultants, many of them are familiar with SQL Server’s locking behavior, and can even describe many of the principles covered in Chapter 6. They’re far less confident about latches, however. Similarly, comparatively few can talk at any length about spinlocks, which are a very related topic. In light of this, I consider this chapter to be an important inclusion in this book.

The idea of this chapter is to offer practical advice to users trying to resolve issues identified to be related to both latch and spinlock behavior. We begin by describing some of the scenarios you might find yourself in, and the kind of symptoms you might see that betray a latch or spinlock problem. Even if they all seem alien (you might have never yet experienced latch contention) it would be useful to have a passing familiarity with these symptoms so that you can recognize various problems when you see them. ...