23

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.

DBCC execution plan

Clearing all non-spatial indexes does not change anything.

25
+500

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.

enter image description here

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

enter image description here

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.

enter image description here

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)

  • 4
    This is a fantastic bug :) Might also explain the crazy loop joins because they might simply be a good choice for this possibly higher-cardinality join condition. – boot4life Dec 28 '15 at 9:35
  • 7
    @boot4life The Id confusion also causes what should be a seek to be a scan. Great catch, Martin. It only appears to affect the AUTO_GRID option. I can reproduce the bug on 2014 SP1 CU4 with a case insensitive collation. SQL Server builds the extended checks query incorrectly. – Paul White Reinstate Monica Dec 28 '15 at 9:44

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.