Does Adding NOINDEX Change DBCC CHECKDB with PHYSICAL_ONLY?

Turn your keyboardThis question came up on Twitter’s #sqlhelp recently.

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.

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.
Previous Post
Teach Yourself SQL Server Performance Tuning (Dear SQL DBA Episode 12)
Next Post
Developer Seeks DBA Career (Dear SQL DBA, Episode 13)

Related Posts

9 Comments. Leave new

  • I like the analogy. Thanks Kendra.

    Reply
  • Thank you very much! This is a great parameter to know about. Also, this post reminded me to run through the corruption and repair process again. Thanks!

    Reply
  • Every time from you, we learn new things.

    Thank you always.

    Reply
  • I’ve been giving this a lot of thought lately since we ran into massive corruption in a VLDB (1 TB) and had to script out the schema into a clean database then BCP the data over. In our case the maintenance windows make it difficult to run a full CHECKDB every night on all databases, especially the bigger ones. Some of these get full consistency checks weekly; this is what burned us when our database encountered significant corruption but the business line didn’t want to lose five days’ data to a restore.

    Are a lot of shops out there doing CHECKDB with NOINDEX or PHYSICAL_ONLY on a nightly basis then doing a full consistency check during the weekend maintenance window?

    Reply
    • As a consultant, I’ve seen a lot of shops not running CHECKDB at all, unfortunately. A lot of times it just gets missed, or isn’t known about.

      When I learned to be a DBA, our team had a lot of VLDBs and we did full CHECKDB once a month on one weekend, and CHECKDB with PHYSICAL_ONLY on the other weekends. It was the most we could get to run with job scheduling. (Edit: Company management was comfortable with the level of risk in that case, but of course not everyone is.)

      I have had clients who automate regular restores and full CHECKDB against the restored database on a rolling schedule. It’s important enough to them that there’s always a server doing a restore + CHECKDB as fast as it can.

      I have had some occasions with VLDBs where moving data into read only filegroups and read only databases helped as well, just for shortening the restore path should that data become corrupt.

      Reply
  • Hi Kendra,

    great blog post! It actually helped me to understand what PHYSICAL_ONLY omits.
    I am currently fighting IO warnings in the error log while CHECKDB runs and asked for help on Stack Overflow (http://stackoverflow.com/questions/42691266/sql-server-check-db-io-reqests-taking-longer-than-15-seconds-warning)

    I got an answer pointing to use CHECKDB just with PHYSICAL_ONLY. That worked to ease the load on the IO subsystem…however I am not comfortable using it as you said on brentozar.com it turns “off valid feature(s) in CheckDB that could really save your bacon”.

    Are there any other ways to get rid of the IO warnings and server slowdown?

    Thanks in advance

    Martin

    Reply

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Menu