DBCC CHECKDB is taking more time than normal - how to tune it?

Question

In the last 30 days, the database size has grown 5% and is now 1.6 Tb, and DBCC CHECKDB time has grown 100% taking 13 hours. However, the io path didn't change.

I compared the DBCC CHECKDB with the other VLDBs, and the DBCC CHECKDB time is 2 hours 10 minutes for 1,1 Tb

Answer 1

Hi @Miguel Gavinhos ,

The following are some factors that affect DBCC CHECKDB’s run time:

1. The size of the database.
   This one’s not so obvious—it’s not the size of the database that matters, it’s the amount of data that’s in it. A 1TB database with only 100MB of data can be consistency checked very quickly, but if the same database contained 1TB of data, it would take a lot longer for DBCC CHECKDB to process.
2. The load on the system.
   DBCC CHECKDB is extremely resource hungry—I like to say it’s the most resource-intensive operation you can run on SQL Server. Therefore, if the server is already heavily loaded, DBCC CHECKDB will be competing for resources and will take a lot longer to run.
3. The capabilities of the system.
   If the database being consistency checked is very large and structurally complicated, but the server and/or I/O subsystem are heavily underpowered, this will have a knock-on effect on the ability of the server to provide the resources DBCC CHECKDB needs, slowing it down.
4. The options specified.
   If the WITH PHYSICAL_ONLY option is specified, the amount of processing that DBCC CHECKDB does is drastically cut down, which usually leads to a significant reduction in run time. However, I wouldn’t recommend using this option during disaster recovery.
5. The complexity of the database schema.
   The more features that you use in the database, the more structures there are to be consistency checked, so DBCC CHECKDB will take longer to run.
6. The corruptions that are found.
   Some corruptions require deeper reprocessing of data to figure out exactly where the corruption is. This can lead to a much longer run time for DBCC CHECKDB.
7. The tempdb configuration.
   DBCC CHECKDB uses a lot of memory to store intermediate consistency checking data, and that storage usually spills out to the tempdb database. If tempdb isn’t configured well, it can be a bottleneck for DBCC CHECKDB and slow it down.

You can optimize for these points above, such as using WITH PHYSICAL_ONLY. please refer to this blog: https://www.brentozar.com/archive/2020/08/3-ways-to-run-dbcc-checkdb-faster/

Best regards,
Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

First question: have you checked the output? One reason that DBCC can take longer time is corruption, because in that case DBCC performs a lot of extra checks.

If DBCC produces a clean output, I am not aware of any tuning things as such, but you can reduce the level of ambition with the PHYSICAL_ONLY option.

Answer 3

Hi, thanks for your posts. The big question is why in one month, the dbcc checkdb time increased 100% for 5% increase in size.