SQL Server sample database WideWorldImporters fails dbcc checkdb

Problem Statement

I just ran dbcc checkdb on the sample WideWorldImporters database. I received (4) errors about statistics corruption that I do not remember having seen in the past when I ran this command. Although this is a sample database only, I am curious as I'd hate to see a similar error in a real database.

SSMS and Windows Versions

SSMS: 18.10 (15.0.18390.0) / Windows 10: Version 21H1 (OS Build 19043.1526)

SQL Server Developer Version

Microsoft SQL Server 2019 (RTM-CU13) (KB5005679) - 15.0.4178.1 (X64) Sep 23 2021 16:47:49 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Pro 10.0 (Build 19043: )

Command Issued in SSMS:

dbcc checkdb ([WideWorldImporters]) with all_errormsgs, data_purity, extended_logical_checks , no_infomsgs;  
go

Error Messages

Msg 9122, Level 16, State 201, Line 10

The statistics 'sys.TT_OrderIDList_22AA2996.PK__TT_Order__C3905BAE80B57D6F' is corrupt.

Msg 9122, Level 16, State 201, Line 10

The statistics 'sys.TT_OrderLineList_24927208.IX_Website_OrderLineList' is corrupt.

Msg 9122, Level 16, State 201, Line 10

The statistics 'sys.TT_OrderList_25869641.PK__TT_Order__288FD689F5006DE2' is corrupt.

Msg 9122, Level 16, State 201, Line 10

The statistics 'sys.TT_SensorDataList_276EDEB3.PK__TT_Senso__88385F3043E9E8D9' is corrupt.

CHECKDB found 0 allocation errors and 4 consistency errors in database 'WideWorldImporters'.

My attempts to fix

I quickly learned that I do not have the required privileges to delete sys.* indexes. I tried restoring the database from my last backup, but this produced the same exact error messages.

Next, I opened this web page to download a fresh copy:

https://github.com/Microsoft/sql-server-samples/releases/tag/wide-world-importers-v1.0

I downloaded "WideWorldImporters-Full.bak" (121 MB), cleared the "Mark of the Web" (MOTW) in properties, and successfully restored the database. However, upon running the same dbcc checkdb command shown above, I receive

The error message is a little misleading. It's not that the statistics object associated with the primary key index is corrupt; rather the DBCC check for statistics corruption encounters an assertion failure when EXTENDED_LOGICAL_CHECKS is specified and the code tries to get the index size for a Hekaton table type index. These objects are skipped as unsupported when EXTENDED_LOGICAL_CHECKS is not specified.

It is a bug that only Microsoft can fix of course, and I do realise this isn't a problem with a real-world database. Nevertheless, the obvious workaround is to skip EXTENDED_LOGICAL_CHECKS.

If that isn't desirable, it is also possible to skip the problematic checks by temporarily disabling automatic statistics updates for Hekaton objects while the DBCC CHECKDB statement runs. There is no documented way to do this, but global trace flag 9823 does perform that function:

DBCC TRACEON (9823, -1);

DBCC CHECKDB (WideWorldImporters) 
WITH 
    ALL_ERRORMSGS, 
    DATA_PURITY, 
    EXTENDED_LOGICAL_CHECKS, 
    NO_INFOMSGS;  

DBCC TRACEOFF (9823, -1);

If you don't have a Microsoft Support agreement, or don't want to go through that process, you can leave feedback at http://aka.ms/sqlfeedback.

This bug was fixed in SQL Server 2019 CU17.

StackExchange Database Administrators Q#307609, answer score: 9