NESTING_TRANSACTION_FULL

(Republishing, or using this info in a commercial product/website, is prohibited without permission. All other uses are permitted. If in doubt, please ask.)

(Back to main page…)

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):

  1. 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)
  2. 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):

  1. 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
  2. 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