I have a spatial index for which
DBCC CHECKDB reports corruptions:
DBCC CHECKDB(MyDB) WITH EXTENDED_LOGICAL_CHECKS, DATA_PURITY, NO_INFOMSGS, ALL_ERRORMSGS, TABLERESULTS
The spatial index, XML index or indexed view 'sys.extended_index_xxx_384000' (object ID xxx) does not contain all rows that the view definition produces. This does not necessarily represent an integrity issue with the data in this database.
The spatial index, XML index or indexed view 'sys.extended_index_xxx_384000' (object ID xxx) contains rows that were not produced by the view definition. This does not necessarily represent an integrity issue with the data in this database.
CHECKDB found 0 allocation errors and 2 consistency errors in table 'sys.extended_index_xxx_384000' (object ID xxx).
Repair level is
Dropping and recreating the index does not remove these corruption reports. Without
EXTENDED_LOGICAL_CHECKS but with
DATA_PURITY the error is not reported.
CHECKTABLE takes 45 minutes for this table although its CI is 30 MB in size and there are about 30k rows. All data in that table is point
Is this behavior expected under any circumstances? It says "This does not necessarily represent an integrity issue". What am I supposed to do?
CHECKDB is failing which is a problem.
This script reproduces the issue:
CREATE TABLE dbo.Cities( ID int NOT NULL, Position geography NULL, CONSTRAINT PK_Cities PRIMARY KEY CLUSTERED ( ID ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ) GO INSERT dbo.Cities (ID, Position) VALUES (20171, 0xE6100000010C4E2B85402E424A40A07312A518C72A40) GO CREATE SPATIAL INDEX IX_Cities_Position ON dbo.Cities ( Position )USING GEOGRAPHY_AUTO_GRID WITH ( CELLS_PER_OBJECT = 16, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO
This is version 12.0.4427.24 (SQL Server 2014 SP1 CU3).
I scripted the table with schema and data, fresh DB, execute. Same error. CHECKDB also has this incredible runtime of 45min. I captured the CHECKDB query plan using SQL Profiler. It has a misguided loop join apparently causing excessive runtime. The plan has quadratic runtime in the number of rows of the table! Doubly nested scanning loop joins.
Clearing all non-spatial indexes does not change anything.