7

I have a database where DBCC CHECKTABLE on a number of small or empty tables taking over 15 minutes to run. When it finishes there are no failures or errors. The performance on everything else on the server is at a very acceptable form. There was nothing else running at the same time.

I have also tried DBCC CLEANTABLE and updated stats with fullscan.

I'm using SQL Server 2016 Enterprise Edition (13.0.5201.2)

Example table:

CREATE TABLE [Schema1].[Table1](
    [col1] [int] NOT NULL,
    [col2] [nvarchar](100) NOT NULL,
    [col3] [xml] NOT NULL,
 CONSTRAINT [PK_1] PRIMARY KEY CLUSTERED 
(
    [col1] ASC,
    [col2] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
6

I ended up opening an incident with Microsoft and it was determined that the sys.sysrscols (that can only be accessed via DAC) had over 200 million rows. After they did a review it was found as part of DBCC SQL Server always does checks on system tables even for DBCC CHECKTABLE. From the DBCC CHECKTABLE manual:

Integrity checks are always performed on all system table indexes.

My understanding is sys.sysrscols does some column tracking and the reason for the large size is a number of tables (200+) we have that have 2,000+ partitions and 163 columns. Almost all of them were temporary working tables that should have been dropped by the application but were not. I cleared them out and the process returned to a normal response time for an empty table of less than 1 minute.

They also noted it is listed in the documentation that partitions can impact DBCC checks. From Partitioned Tables and Indexes:

DBCC Commands

With a larger number of partitions, DBCC commands could take longer to execute as the number of partitions increases.

  • 1
    The OP sample code shows the table is ON [PRIMARY]. Was the actual table on a partition scheme? Or can you clarify that the existence of partitioned tableX caused the slowdown of CHECKTABLE(tableY)? – Peter Vandivier Jun 6 at 7:06
  • @PeterVandivier The above example was taken from one of the real tables with 0 rows that was running long. However this was not the table that was causing the impact as that table is not empty. The table with partitioning in question had 2000+ partitions but also had a large number of columns which I failed to include in my answer. This was compounded by the large number of temporary tables created on this same partitioning scheme with the same large number of rows impacted the size of sys.sysrscols. – Russ960 Jun 13 at 17:38
  • 1
    thanks for the info! 😄 I ask because given the description in your answer, I was unable to reproduce the behavior(link) as I interpreted from the answer. Would you be kind enough to take a look at the gist and comment on what I may have misinterpreted? Or perhaps revise your wording to clarify? I also kindly invite you to discuss at length in chat – Peter Vandivier Jun 13 at 17:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.