CHECKDB And Indexes On Persisted Computed Columns

Kevin Feasel

2016-09-01

DBCC

Arun Sirpal diagnoses a slower-than-usual DBCC CHECKDB run:

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.

Read on for the details and Arun’s solution.

Related Posts

Dealing With CheckDB Error Message 824 Level 24

Steve Stedman has a post on fixing a database which has experienced an incorrect pageid error: Msg 824, Level 24, State 2, Line 1 SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:2806320; actual 0:0).  It occurred during a read of page (1:xxxxx) in database ID 5 at offset 0x00000xxxxx0000 in file […]

Read More

When Using DBCC DROPCLEANBUFFERS

Dan Guzman shares words of wisdom with using DBCC DROPCLEANBUFFERS for testing query performance in SQL Server: One can make the argument that DBCC DROPCLEANBUFFERS might not be particularly valuable for testing. First, the storage engine in SQL Server Enterprise Edition (or Developer Edition, which is often used when testing) behaves differently with a cold cache versus […]

Read More

Categories

September 2016
MTWTFSS
« Aug Oct »
 1234
567891011
12131415161718
19202122232425
2627282930