Are unfixable spatial index corruptions considered normal?

Question

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 repair_rebuild.

Dropping and recreating the index does not remove these corruption reports. Without EXTENDED_LOGICAL_CHECKS but with DATA_PURITY the error is not reported.

Also, 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 geographydata.

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.

Answer 1

I could not immediately reproduce this on 2014 - 12.0.4213.0 but do see it on SQL Server 2016 (CTP3.0) - 13.0.700.242.

On the 2014 build (with no DBCC errors) the plan looks as follows.

And on the 2016 build (with DBCC errors reported) like this.

The second plan has a single row coming out of the merge anti semi join, the first plan zero rows.

The join predicates are different with respect to what is matched to the pk0 column in the spatial index.

The first one correctly maps it to the table Primary Key, The second maps it to the Id column returned from the TVF.

According to the SQL Server 2012 internals book this is a binary(5) value for the Hilbert number of the cell so this predicate certainly is incorrect (If the Id of the single row in the base table is set to 1052031049 instead of 20171 I no longer see any DBCC errors as this happens to correspond to this value of 0xa03eb4b849).

---

On 2014 - 12.0.4213.0 after re-creating the table as follows I could reproduce the problem.

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)
)

(Note the change from ID to Id)

My 2014 instance is installed with Case Sensitive collation. So it looks as though this may have prevented the column confusion before.

So I guess a potential workaround might be to rename the column in Cities as CityId for example.

Connect Item (Microsoft bug report)