Running the usual consistency checks on a database (with a new release code applied from an ISV) I noticed slower than usual timings. I started to dig into wait stats.

OLEDB I expected as that is what is used by CHECKDB under the covers. However LATCH_EX Wait was the 2^nd highest on the list with high wait counts and high Avg_wait_S. I then decided to move into latch DMV and saw this.

SELECT * FROM sys.dm_os_latch_stats
ORDER BY waiting_requests_count desc

All the signs of CHECKDB Latch contention.

DBCC – OBJECT – METADATA this latch can be a major bottleneck for DBCC consistency checks when indexes on computed columns exist.  As a side note DBCC_Multiobject scanner  is used to get the next set of pages to process during a consistency check.

The call stack that I captured for DBCC – OBJECT – METADATA looked like:

XeSqlPkg::latch_suspend_end::Publish+e2 [ @ 0+0x0
LatchBase::Suspend+1002 [ @ 0+0x0
LatchBase::AcquireInternal+1ff [ @ 0+0x0
CheckCrossRowset::ProcessDataRecordInternal+3d1 [ @ 0+0x0
CheckCrossRowset::ProcessDataRecord+9c [ @ 0+0x0
CheckCrossRowset::ProcessRecord+a3 [ @ 0+0x0
CheckTables::ProcessDataPage+2af [ @ 0+0x0
CheckTables::ProcessPage+31c [ @ 0+0x0
CheckTables::ProcessNextData+297 [ @ 0+0x0
CheckAggregate::GetNextFact+113 [ @ 0+0x0
CTRowsetInstance::FGetNextRow+49 [ @ 0+0x0
CUtRowset::GetNextRows+7c [ @ 0+0x0
CQScanRmtScanNew::GetRowHelper+3e1 [ @ 0+0x0
CQScanXProducerNew::GetRowHelper+28f [ @ 0+0x0
FnProducerOpen+58 [ @ 0+0x0
FnProducerThread+4df [ @ 0+0x0</value>

For more information on what the above means see Paul’s Wait/Latch library for this type @ https://www.sqlskills.com/help/latches/dbcc_object_metadata/

I have captured call stacks many times to provide data for the library and it is alot of fun. (Maybe I need to get out more?)

When I disabled the indexes on the persisted computed columns I saw no LATCH based waits and the time taken to complete went down to literally couple of minutes(as opposed to 15) . Just having a glance into the latch DMV I did not see the DBCC – OBJECT – METADATA latch.

Oh the only thing to remember is to rebuild those indexes after the consistency check; I guess you could just offload the check to a different server if disabling is not a feasible option.