SQLTeam.com | Weblogs | Forums

CHECKTABLE is running very long for a small table


Hey guys,
I have an issue with DBCC CHECKTABLE; I am checking a very small table (17 rows, and fit in 1 page). The process takes almost 4 minutes and has lots of read. What do you think the issue might be?


The table is probably a heap (that is, there is no clustered index on the table). Create a clustered index on the table and try again.


Download and run sp_BlitzIndex to check if you have a ton of forwarded records on that table. This occurs on heaps when updates don't fit on the page. Adding a clustered index resolves the issue.


Please check this link, but not sure it would be completely helpful for you: https://blogs.msdn.microsoft.com/sqlserverstorageengine/2007/01/24/checkdb-part-7-how-long-will-checkdb-take-to-run/


If you are checking a single small table that fits in one page as you say it is, unless you are using the TABLOCK option when running the DBCC CHECKTABLE command, I don't have any theories as to why it is taking 4 minutes. Does it report any errors or issues when it completes?