(Republishing, or using this info in a commercial product/website, is prohibited without permission. All other uses are permitted. If in doubt, please ask.)
DBCC_MULTIOBJECT_SCANNER
Description:
This latch class is when a thread is waiting for access to the DBCC MultiObjectScanner data structure that DBCC consistency checks use to drive scanning through the database. In this case, the latch controls the access necessary to request the next page (or range of pages) to scan for consistency problems. If there are no pages left to process from the current batch, the DBCC CHECK_TABLE_INIT latch is acquired, and the MultiObjectScanner is loaded with the allocation units from one or more objects (multiple objects can be checked simultaneously). You can read more about this architecture in this blog post.
(Books Online description: none)
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:
The map_key value in sys.dm_xe_map_values is 43 in 2008 and 2008 R2, and 37 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:
From SQL Server 2012 onward, this latch should not be a noticeable contention point. Before SQL Server 2012, this latch may be a large contention point when DBCC check commands run using multiple threads, which you can address on SQL Server 2008 and SQL Server 2008 R2 using the trace flags described in KB article 2634571).
The contention issue is that when this code was written (SQL Server 2000/2005), each parallel thread in a DBCC check would acquire the latch in EX mode to then access the MultiObjectScanner to get the next data file page to process for consistency checking information. As the number of processor cores, and database size, increased over time, the fact that this latch could only be acquired in EX mode, and needed to be acquired by each thread for every data file page being processed, made the latch a huge contention point that massively slowed down DBCC check commands.
Work was done between SQL Server 2008 R2 and SQL Server 2012 (and back-ported to SQL Server 2008 and SQL Server 2008 R2) to change the operation of the MultiObjectScanner so that whenever a thread calls in to get the next page to process, it’s given 100 pages to process. This means the latch is now acquired 100 times less frequently, thus removing it as a major contention point. You can read more about this change in this blog post.
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):
- Getting the next set of pages to process during a consistency check (as one of the parallel threads performing the consistency checks)
This is a SQL Server 2014 call stack, which will be the same on 2012 as well. Call stacks for this latch before SQL Server 2012 will be slightly different due to the rewrite of this code – see below for more details.
Abbreviated call stacks (list number matches known occurrences list):
- XeSqlPkg::latch_suspend_end::Publish+138
LatchBase::Suspend+16b8
LatchBase::AcquireInternal+415
MultiObjectScanner::GetNextPageAndBatch+2b4
CheckTables::ProcessNextData+1bb
CheckAggregateSingleInstance::GetNextFact+28e
CTRowsetInstance::FGetNextRow+3c
CQScanRmtScanNew::GetRowHelper+3b5
CQScanXProducerNew::GetRowHelper+366
CQScanXProducerNew::GetRow+15
FnProducerOpen+57
FnProducerThread+8c3