Sql-server – Help with wait type CHECK_TABLES_THREAD_BARRIER

dbcc-checkdbsql serversql-server-2016wait-types

I have a 2016 SQL Server freshly built – 13.0.5149.0 – OS 2016 (Microsoft Windows NT 6.3 (14393)). The environment is VMWare. No SQL Server HA configuration.

It has a problem where CheckDB "stops" with a wait type of CHECK_TABLES_THREAD_BARRIER. It has happened twice on 2 different databases. I don't see much listed on online resources for what to do with this wait. Microsoft lists "TBD" on information. This is a non production proof of concept environment for an upgrade.

Can anyone provide any insight or next steps?

Best Answer

The wait types are sometimes sort of self-documenting, in this case I would piece together that something is causing CHECKDB to use too many threads. One way to attempt to minimize or eliminate this is to restrict CHECKDB to using a single core with the MAXDOP option:

DBCC CHECKDB ('database') WITH MAXDOP = 1;

I have other ideas for minimizing impact of CHECKDB here (granted it was written long before the MAXDOP option was added, so I need to update the post):

If you want an official answer from Microsoft, I suspect you'll have to open a support case. It may be that there is a bug leading to this symptom, in which case the support call will be free, but I wouldn't go through that just to get the explanation of what CHECK_TABLES_THREAD_BARRIER means, especially when you know you have a viable workaround.

Related Question