(Republishing, or using this info in a commercial product/website, is prohibited without permission. All other uses are permitted. If in doubt, please ask.)
Description:
This latch class, along with NESTING_TRANSACTION_READONLY, is used to control access to the transaction description structure (called an XDES) for the parent transaction of a parallel nested transaction. A query that involves a parallel operator must start a sub-transaction for each parallel thread that is used, so these transactions are sub-transactions (called parallel nested transactions) of the main transaction. The _FULL is for a transaction that’s “active”, i.e. it’s changed the database (e.g. for an index build/rebuild or parallel bulk load).
(Books Online description: “Internal use only.”)
Questions/comments on this latch class? Click here to send Paul an email, especially if you have any information to add to this topic.
Added in SQL Server version:
pre-2005/2005
Removed in SQL Server version:
N/A
Extended Events latch_class value:
Maps to the TRAN_NESTING_FULL map_value in sys.dm_xe_map_values.
The map_key value in sys.dm_xe_map_values is 113 in 2008 and 2008 R2, and 110 in 2012 and 2014 RTM. After 2014 RTM, you must check the DMV to get the latest value as some map_key values have changed in later builds.
Other information:
These latches indicate that parallelism is happening, so for contention issues, follow the general steps for troubleshooting parallelism problems (see the CXPACKET wait type). I’ve heard anecdotal evidence that these latches can be problematic under certain circumstances when RCSI (read-committed snapshot isolation) is enabled, when inserting concurrently into a table with a clustered columnstore index, and there is a known bug (fixed) in SQL Server 2012 and 2014 that is described in KB 2806979.
This latch can also be a performance bottleneck on systems with many schedulers and lots of high-DOP queries doing parallel inserts or parallel SELECT … INTO operations. This blog post presents an excellent investigation into those cases.
See the LATCH_EX wait type for more information about latches in general and how to troubleshoot them.
Known occurrences in SQL Server (list number matches call stack list):
- Writing a log record through the parent transaction (in this case, while setting an entire extent of pages to be allocated in their PFS bytes, as part of bulk load into a heap)
- Writing a log record through the parent transaction (in this case, to log the heap bulk-operation lock that is held to prevent log backups occurring while a bulk load into a heap is running)
And many, many more similar call stacks. Most of the call stacks for this latch class are around generating log records.
Abbreviated call stacks (list number matches known occurrences list):
- XeSqlPkg::latch_suspend_end::Publish+138
LatchBase::Suspend+16b8
LatchBase::AcquireInternal+415
ParNestedXdes::GenerateLogRec+93
PFSPageRef::ModifyPFSRow+5e9
ChangeExtStateInPFS+297
AllocationReq::AllocateExtent+305
AllocationReq::AllocatePages+1970
AllocationReq::Allocate+f3
ExtentAllocator::PreAllocateExtents+435
ExtentAllocatorSingleAlloc::PreAllocate+72
ExtentAllocatorSingleAlloc::AllocateExtents+25c
CBulkAllocator::AllocateExtent+24d
CBulkAllocator::AllocatePageId+bc
CBulkAllocator::AllocateLinkedAndFormattedLeafPage+c2
CHeapBuild::Init+300
RowsetBulk::Init+782
RowsetBulk::WakeUp+153
CQScanRowsetNew::WakeUpRowset+329
CQScanUpdateNew::Open+2bf
CQScanNew::OpenHelper+41
CQScanXProducerNew::Open+c8
FnProducerOpen+44
FnProducerThread+8c3
SubprocEntrypoint+a7f
SOS_Task::Param::Execute+21e
SOS_Scheduler::RunTask+a8 - XeSqlPkg::latch_suspend_end::Publish+138
LatchBase::Suspend+16b8
LatchBase::AcquireInternal+415
ParNestedXdes::GenerateLogRec+93
XDES::LogLocks+8d
XDES::GetHoBtBulkOpLock+16e
CHeapBuild::Init+a4
RowsetBulk::Init+782
RowsetBulk::WakeUp+153
CQScanRowsetNew::WakeUpRowset+329
CQScanUpdateNew::Open+2bf
CQScanNew::OpenHelper+41
CQScanXProducerNew::Open+c8
FnProducerOpen+44
FnProducerThread+8c3
SubprocEntrypoint+a7f
SOS_Task::Param::Execute+21e
SOS_Scheduler::RunTask+a8