Sql-server – Rebuilding a spatial index with minimum downtime

spatialsql serversql server 2014

We just upgraded from SQL Server 2008 to 2014.
It went fairly well except for a problem we have on a spatial index.
On this table we receive the error

Cannot insert duplicate key row in object 'sys.extended_index_1527780600_384000' with unique index 'lu_unit__geolocation'. – duplicate key value:(0x20330a3504, 95469304).

A spatial index can't have a unique constraint, something is wrong.

I would think the safer way forward is to rebuild the index. I experimented a bit and I find it takes about 50s to rebuild the index on 1.6 million rows.
The production table is about 5.5 million rows, making it at least 3min when the base table won't be accessible since a spatial index can't be built online.

Does anybody have experience with rebuilding spatial indexes with minimum down time? We can do with 30s but not 3min.

Best Answer

Concept of this solution is to prepare copy of the data with the same structure and swap with origial table using sp_rename. It is preety the same for other big changes in a table structure.

Let's create table and populate it with geometry data.

CREATE TABLE SpatialTable (id int IDENTITY(1,1) primary key, geometry_col geometry);  
CREATE SPATIAL INDEX SIndx_SpatialTable_geometry_col1   
   ON SpatialTable(geometry_col)  
   WITH ( BOUNDING_BOX = ( 0, 0, 500, 200 ) ); 
GO

INSERT INTO SpatialTable (geometry_col)  
SELECT TOP 1000000 geometry::STGeomFromText('LINESTRING (100 100, 20 180, 180 180)', 0)
FROM sys.all_columns a,sys.all_columns b;
GO

Creating copy of the data and preparing appropriate indexes. Those operations don't harm original table and should be fast except spattial index creation.

SELECT * INTO SpatialTable2 FROM SpatialTable
GO

ALTER TABLE SpatialTable2 ADD CONSTRAINT PK_SpatialTable_Id PRIMARY KEY CLUSTERED (id); 
GO

CREATE SPATIAL INDEX SIndx_SpatialTable_geometry_col1   
   ON SpatialTable2(geometry_col)  
   WITH ( BOUNDING_BOX = ( 0, 0, 500, 200 ) ); 
GO

Now let's simulate some insert on the base table.

INSERT INTO SpatialTable (geometry_col)  
SELECT TOP 1000 geometry_col FROM SpatialTable

Below is the trick which should take only short time.

BEGIN TRANSACTION

    SET IDENTITY_INSERT dbo.SpatialTable2 ON

    INSERT INTO SpatialTable2 (id,geometry_col)  
    SELECT id,geometry_col FROM SpatialTable sp WITH (TABLOCKX)
    WHERE NOT EXISTS (SELECT ID FROM SpatialTable2 sp2 where sp2.id=sp.id)

    SET IDENTITY_INSERT dbo.SpatialTable2 OFF

    EXEC sp_rename 'dbo.SpatialTable', 'SpatialTable_old';
    EXEC sp_rename 'dbo.SpatialTable2', 'SpatialTable';

COMMIT TRANSACTION

Now we have new table and new spatial index structures with the old data. Of course it is only proposition of steps and you should apply it to your requirements and data structure, especially UPDATE and DELETE operations if they occur in your case.

After this operation you can drop the old table to clean database or use it for any other purpose.

Related Question