9

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 received the exact same errors!

Finally, I downloaded "WideWorldImporters-Full.bacpac" (59.1 MB), cleared the "Mark of the Web" (MOTW) in properties, and successfully restored the database. This also resulted in the same dbcc checkdb errors shown above.

I clearly do not remember ever having received these errors, which makes me wonder could there be an "issue" in my current version of Windows, SSMS or SQL Developer that is contributing to these errors?

Thank You

Improve this question

2 Answers 2

Reset to default
9

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.

Improve this answer
1
6

I can reproduce your issue on 2019 CU15 Developer Edition. I initially had 2019 RTM installed and that dbcc came back clean.

Doing some investigation, it looks like these stats are associated with user-defined table types. I tried merely dropping/recreating the UDTs and encountered the same issue. Only when removing the memory optimized property from the UDTs (and changing the dependent procedures accordingly) did the checkdb come back clean.

I'd recommend raising an issue with MS and point them at their own sample database. Hopefully that shortens the troubleshooting process on their side. 😀

Improve this answer
2
  • Thank you, Ben, for your analysis. I will try to notify Microsoft of this issue. For my own learning, I followed your hint to drop the User-Defined Table Types (which required first dropping associated stored procedures), then recreating the User-Defined Table Types, after commenting out "WITH ( MEMORY_OPTIMIZED = ON )", and finally re-creating the stored procedures. Only one sproc, [Website].[RecordColdRoomTemperatures], gave me some extra trouble with NATIVE_COMPILATION, SCHEMABINDING and BEGIN ATOMIC, but I was able to replace the stored procedure. I then got a successful dbcc checkdb! Commented Feb 17, 2022 at 6:59
  • Those were my steps as well. :) Commented Feb 17, 2022 at 15:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.