A question arose asking – “What does the LPE_BATCH spinlock type represent?”
You can see the LPE_* series of spinlocks using the DMV query “select * from sys.dm_os_spinlock_stats“
A spinlock is a lightweight, user mode synchronization object used to protect a specific structure. The goal of the spinlock is to be fast and efficient. So the ultimate goal is to see is 0 collisions and 0 spins. SQL Server 2008 contains 150+ spinlock objects to protect the various internal structures during multi-threaded access.
It will sound redundant but the LPE_BATCH spinlock protects the batch. For example:
select * from authors – Batch #1
select * from titles – Batch #2
I looked at a few places where LPE_BATCH is used in the code.
1. Deadlock monitor can retrieve the task and needs synchronized access to the batch’s ‘Task’
2. Possible state changes on the batch – for example RUNNING, IDLE, ENQUEUED
3. Enter and Exit transaction states like DTC or MARS
If you are seeing collisions or spins on the LPE_BATCH spinlock I suggest you look at the before mentioned areas.
Bob Dorr – Principal SQL Server Escalation Engineer