Share via

DBCC CHECKDB Slow on SQL Server 2019 Standard Edition

eyal golombek 21 Reputation points
2022-01-06T10:59:18.043+00:00

DBCC CHECKDB Slow on SQL Server 2019 Standard compared to that on same DB in SQL server 2012 Enterprise.

Same number of virtual processes on both servers (16) , but on the 2019 Server there are 4 sockets and on the 2012 Server there are 2 Sockets.

MAXDOP on both servers is 4.

SQL Server | Other
SQL Server | Other

Additional SQL Server features and topics not covered by specific categories

Answer accepted by question author

Ronen Ariely 15,221 Reputation points
2022-01-06T11:53:02.333+00:00

Hi,

Let's start from the most relevant and common point

DBCC CHECKDB Slow on SQL Server 2019 Standard compared to that on same DB in SQL server 2012 Enterprise.

In Standard Edition, CHECKDB is single-threaded, which directly lead to such behavior.

Using Enterprise Edition, CHECKDB respects the maximum degree of parallelism.

----- More information -----

This information is documented but very poorly and not well in my opinion

https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkdb-transact-sql?view=sql-server-ver15#checking-objects-in-parallel

If you will check the official documentation regarding DBCC CHECKDB, then you will notice that there is not explicitly information about the fact that standard edition does not support Parallel consistency check.

The only information it provide for some reason, is only in the comment: This feature is not available in every edition of SQL Server.

But if you go to the mentioned document about the components of the different editions of SQL Server then the information is explicitly documented in one word: no

https://learn.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2016?view=sql-server-ver15#RDBMSM

162855-image.png

Was this answer helpful?

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. CathyJi-MSFT 22,431 Reputation points Microsoft External Staff
    2022-01-07T02:25:16.127+00:00

    Hi @eyal golombek ,

    Welcome to Microsoft Q&A forum.

    Agree with pituach. In SQL server Standard Edition, CHECKDB is single-threaded. SQL server Enterprise Edition has parallel corruption checking.

    If you want to run DBCC CheckDB faster, suggest you reading below blog.

    3 Ways to Run DBCC CHECKDB Faster

    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Was this answer helpful?

    1 person found this answer helpful.

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.