SQL Server 2014 – Are Unfixable Spatial Index Corruptions Normal?

dbcc-checkdbspatialsql serversql server 2014

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.

Best Answer

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)

Related Solutions

SQL Server – Fix DBCC CHECKDB Unfixable Corruption in Indexed View

The query processor can produce an invalid execution plan for the (correct) query generated by DBCC to check that the view index produces the same rows as the underlying view query.

The plan produced by the query processor incorrectly handles NULLs for the ImageObjectID column. It incorrectly reasons that the view query rejects NULLs for this column, when it does not. Thinking that NULLs are excluded, it is able to match the filtered nonclustered index on the Users table that filters on ImageObjectID IS NOT NULL.

By producing a plan that uses this filtered index, it ensures that rows with NULL in ImageObjectID are not encountered. These rows are returned (correctly) from the view index, so it appears there is a corruption when there is not.

The view definition is:

SELECT
    dbo.Universities.ID AS Universities_ID, 
    dbo.Users.ImageObjectID AS Users_ImageObjectID
FROM dbo.Universities
JOIN dbo.Users
    ON dbo.Universities.AdminUserID = dbo.Users.ID

The ON clause equality comparison between AdminUserID and ID rejects NULLs in those columns, but not from the ImageObjectID column.

Part of the DBCC generated query is:

SELECT [Universities_ID], [Users_ImageObjectID], 0 as 'SOURCE'
FROM [dbo].[mv_Universities_Users_ID] tOuter WITH (NOEXPAND) 
WHERE NOT EXISTS
( 
    SELECT 1 
    FROM   [dbo].[mv_Universities_Users_ID] tInner
    WHERE 
    (
        (
            (
                [tInner].[Universities_ID] = [tOuter].[Universities_ID]
            ) 
            OR 
            (
                [tInner].[Universities_ID] IS NULL
                AND [tOuter].[Universities_ID] IS NULL
            )
        )
        AND
        (
            (
                [tInner].[Users_ImageObjectID] = [tOuter].[Users_ImageObjectID]
            ) 
            OR 
            (
                [tInner].[Users_ImageObjectID] IS NULL 
                AND [tOuter].[Users_ImageObjectID] IS NULL
            )
        )
    )
)
OPTION (EXPAND VIEWS);

This is generic code that compares values in a NULL-aware fashion. It is certainly verbose, but the logic is fine.

The bug in the query processor's reasoning means that a query plan that incorrectly uses the filtered index may be produced, as in the example plan fragment below:

Erroneous plan

The DBCC query takes a different code path through the query processor from user queries. This code path contains the bug. When a plan using the filtered index is generated, it cannot be used with the USE PLAN hint to force that plan shape with the same query text submitted from a user database connection.

The main optimizer code path (for user queries) does not contain this bug, so it is specific to internal queries like those generated by DBCC.

Sql-server – database corruption

First things first, I hope you have a backup, this is a serious error and you should do a restore, even if you lose some data as that way you will end up with a consistent database but the second best option would be this.

You can peek into the data pages to see what is stored there and maybe, just maybe you can get most of the data from the non damaged tables. Now before we start you should at least read Paul Randal's Inside the Storage Engine: Anatomy of a page and How to use DBCC PAGE. and you should really watch his video on Advanced Data Recovery Techniques

First to make sure what is on the damaged page.

dbcc traceon (3604); 
GO
dbcc page (5,1,73703,0);

This will dump the page header which you can use to decipher what is on the page. From the error message posted there seems to be errors in the GAM/SGAM/PFS for pages 72792-80879 so you can look at which object is stored there by dumping the headers and check the object_id. The syntax for DBCC PAGE is dbcc page (database_id,File_id,PAGE_ID,0); The zero is for dumping the page header but you can dump the whole page by changing that last flag

dbcc page (5,1,72792,0);
GO
dbcc page (5,1,72793,0); 
...

and for each page find to which object it belongs

When you have that information you can hopefully copy the non damaged data from the database into another.

Best Answer

Related Solutions

SQL Server – Fix DBCC CHECKDB Unfixable Corruption in Indexed View

Sql-server – database corruption

Related Question