Maintenance Plan in SQL Server AlwaysOn Environment

I would recommend to Update statistics as well as compatibility level to the latest version of sql server (provided your application supports it and you have done proper full business life cycle testing). I have written about full list of steps when moving from lower version to higher version

As a side note, please don't use sp_MSforEachtable - Its not supported. There is a clever way of doing it.

Compatibility level matters - see What is the actual behavior of compatibility level 80? and Clinging to old compatibility levels form Aaron Bertrand

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)