on August 16, 2016
The NOINDEX option does change the behavior of DBCC CHECKDB (even if you’re already using PHYSICAL_ONLY). Here’s how the two options compare to one another, and how to see the difference yourself in a simple example.
This question came up on Twitter’s #sqlhelp recently.
PHYSICAL_ONLY is like having a doctor examine your body, but not run bloodwork
PHYSICAL_ONLY looks at page structures and makes sure something isn’t obviously wrong. If you have the database’s page verification option set to CHECKSUM (the default since SQL Server 2005, and a very good thing), it runs a checksum on the page and compares it to a checksum recorded in the header when the page was last modified.
When you use the PHYSICAL_ONLY option, you’re telling SQL Server to skip logical checks. It won’t do things like make sure the data in a clustered and nonclustered index on a table is consistent with one another.
NOINDEX is like telling the doctor to ignore some body parts altogether
NOINDEX tells SQL Server to skip nonclustered indexes on your user tables. It just ignores them.
What’s not obvious from the documentation is that it’s not just skipping the logical checks, it’s skipping the physical checks as well for nonclustered indexes on user tables. It won’t look at those handy checksums at all!
I don’t know about you, but I’d rather the doctor mention to me if my pinky finger is on fire, whether or not I can live without it.
Prove it! Corrupt a nonclustered index and then run CHECKDB three ways.
It’s simple to corrupt a nonclustered index in a database with a free Hex Editor. Here’s a post I wrote in 2011 with steps on how to corrupt a nonclustered index. The same steps still work just fine on SQL Server 2016.
After you have your corrupt nonclustered index, test these three commands:
- DBCC CHECKDB (CorruptMe) with PHYSICAL_ONLY;
- This will find the corruption - it sees the checksum mismatch on the physical page of the nonclustered index.
- DBCC CHECKDB (CorruptMe, NOINDEX) with PHYSICAL_ONLY;
- This will not find the corruption, NOINDEX tells it to skip the nonclustered index altogether.
- DBCC CHECKDB (CorruptMe, NOINDEX);
- This will also not find the corruption because NOINDEX tells it to skip the nonclustered index altogether.
REGISTER