In SQL Server, you can use the DBCC CHECKDB command to check and resolve consistency issues in the database. It performs logical and physical checks on all the objects, like tables and indexes, in the database file and ensures there are no corruption issues.
As the command thoroughly scans the complete database, it may take time to scan and repair the database, especially large-sized database. Also, during execution, you have to prevent other users from doing changes to the database until the command completes the process. So, it is important to estimate the time taken to run the command to prevent downtime for the users and to plan database maintenance effectively.
However, the execution time of the command is not fixed and depends on various factors. In this article, we will learn how to estimate DBCC CHECKDB execution time. And, we will also learn about a SQL repair tool that can help you repair and restore SQL database quickly, with complete integrity and in less downtime.
Factors Affecting DBCC CHECKDB Execution Time
Many factors can influence how long DBCC CHECKDB can take to run. Some of them are:
Database Size
When the DBCC CHECKDB command is executed, it reads every allocated page in the database. If the database is large, naturally it requires longer time. But, the execution time of the query on the database is based on the used space that is filled with records. To understand this, read this table:
| Database Size | Used Space | DBCC CHECKDB Execution Time |
|---|---|---|
| 100 GB | 100 MB | Quick (Less time) |
| 100 GB | 90 GB | Longer time |
This means that the execution time of the command depends not just on the database size but also on how much data is actually stored in it.
Concurrent I/O Load and CPU
DBCC CHECKDB uses CPU to sequentially read and process every page in the database. If other queries or processes are running on the server while you execute DBCC CHECKDB, they will compete for CPU, slowing the command execution.
| Concurrent I/O | Impact on DBCC CHECKDB Command |
|---|---|
| Multiple SELECT queries are running | Competes for I/O, slows command execution |
Severity of Corruption in Database
The DBCC CHECKDB command can take time if the non-clustered index corruption is detected in the SQL database, which requires re-scanning of the data.
Issue with the Disk where tempdb is Placed
DBCC CHECKDB uses a snapshot of the database and its version information from the tempdb in SQL Server. If the tempdb is slow or it is stored on a disk that is having issues or is out of space, it can slow down the query execution
Option Specified with the DBCC CHECKDB Command
The run time of DBCC CHECKDB command can vary with the options specified. Using DBCC CHECKDB WITH NOINDEX skips non-cluster indexes, resulting in faster execution time. Using the WITH PHYSICAL_ONLY option focuses on physical page integrity rather than logical checks. But running DBCC CHECKDB WITH ALLOW_DATA_LOSS slows down the execution time.
MAXDOP Setting
DBCC CHECKDB execution may depend upon the value of the max degree of parallelism. These settings are introduced in SQL Server 2016, helping to control the usage of CPU cores in parallel during query execution, including the DBCC CHECKDB command. You can set the MAXDOP setting according to the query and instance. Increasing the MAXDOP value speeds up the CHECKDB execution, while decreasing it increases the run time.
| DBCC CHECKDB Query | CPU Usage | Execution Time | Performance Impact |
|---|---|---|---|
| WITH MAXDOP = 8 | Uses up to 8 CPU cores in parallel | ~8 minutes | Faster execution |
| Without MAXDOP | Uses default CPU configuration | ~24–25 minutes | Slower execution time (3–4× slower) |
How to Estimate DBCC CHECKDB Execution Time?
As we already know that the run time of DBCC CHECKDB is not fixed, it can vary depending on the factors explained above. However, you can estimate how much time DBCC CHECKDB would take to run by following these methods.
1 - Check Historical Details
Admins usually run the DBCC CHECKDB command weekly or monthly to ensure data integrity. You can check and compare the time against past baselines to estimate the execution time. If you see any deviations, then you check the MAXDOP settings and other factors if applicable. You can check the SQL Server logs to check the DBCC CHECKDB completion logs for future time estimates.
2 - Estimate Time based on the Percentage
You can even check the current progress of the DBCC CHECKDB query using sys.dm_exec_requests while it is running. This helps you estimate how much longer the command will take. To check the command completion percentage, run the script below:
SELECT
session_id AS [Session ID],
command AS [Command],
start_time AS [Start Time],
percent_complete AS [Percent Complete]
FROM
sys.dm_exec_requests
WHERE
command LIKE 'DBCC%'
The command will display the output in the following columns:
• Session ID
• Command
• Start Time
• Percent Complete
What If DBCC CHECKDB Fails or suddenly Slows Down?
The DBCC CHECKDB may fail to respond, especially if:
• The MS SQL database file is severely corrupted
• Database contains non-clustered indexes
• Database is large and complex
Also, it may not guarantee complete data integrity, especially while repairing the pages in the SQL database. In the case where DBCC CHECKDB takes longer run time (hours for large database) or has repair risks, you can use an alternative database repair tool – Stellar Repair for MS SQL. This professional tool excels in repairing severely corrupted MDF/NDF files faster and with no data loss. It can easily recover clustered, non-clustered indexes, stored procedures, etc., and even deleted data from the database file. Also, it supports recovery of specific objects from the corrupt SQL database.
To know how it works, watch this video:
Conclusion
The DBCC CHECKDB command is and remain important for checking and repairing MS SQL database files, but it can take time to execute due to factors described in this article. Also, it can cause data loss when executed with the REPAIR_ALLOW_DATA_LOSS option. To prevent data loss and repair database within less time, you can rely on a professional SQL repair tool - Stellar Repair for MS SQL. It can repair SQL databases in any condition or state with complete integrity.
Top comments (0)