Q: If I ever have to perform disaster recovery, I’m sure that running DBCC CHECKDB will be part of my disaster recovery plan. How can I estimate how long it will take for DBCC CHECKDB to run?
A: There’s a very simple answer to your question—run it and see how long it takes. However, there’s obviously a lot more to it than that, so let me explain.
Given that you’re going to be running DBCC CHECKDB, it’s a good idea to know how long it’s going to take to run for two reasons. First, if it takes longer than usual, that’s a sign that it has found corruption and is determining exactly where the corruption is. Second, if your boss comes along during the disaster recovery process and asks how long it will be before you know the extent of the damage, it’s far better to be able to say, “Well, it usually takes X hours to run” rather than “I have no idea, boss—I never run DBCC CHECKDB.”
As far as estimating how long DBCC CHECKDB will take to run on a given database, it’s very difficult to tell because there are so many variables involved. (I even have a hard time estimating for large databases, and I wrote DBCC CHECKDB!) The following are some factors that affect DBCC CHECKDB’s run time:
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
As you can see, there are too many factors involved to be able to make a good guess. The best way to know how long DBCC CHECKDB will take is to run it.