6

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.

  • 2
    Your system cannot afford a 3 minute down time to fix corruption? How long can it afford to run with the corruption unfixed? – datagod Oct 5 '17 at 12:28
3
+150

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.

  • 1
    To handle deletes and updates that may have happened since transfer of data to the new table, it’s possible that change tracking (along with a suitable merge) could be a lightweight but robust way of guaranteeing integrity of the finished data table. – Paul Holmes Oct 6 '17 at 8:09
1

Another option, that falls along the same lines as @Paweł_Tajs suggestion... I've got a table with 65 million rows pre-calculated date range values that's used to support a variety business/working days functions, all of which use schema binding... All of which are hitting that table way too often to be allow any real down time... Which sucks because it does need to be recalculated from time to time and takes between 8 and 10 hours every time it has to happen.

Long story short... It was pure dumb luck that was reading the solution to one of Itzik Ben-Gan's SQL Puzzles Solution to TSQL Table Truncation Challenge. Turns out that Itzik's Puzzle and my table had the same solution... Just do a partition switch. Super easy, no messing around removing schema binding from function no down time or user interruption and... best of all... It's minimally logged so no blowing up tempdb or the transaction logs.

Here's a video of Kendra Little giving a better explanation than I can: Why You Should Switch in Staging Tables Instead of Renaming Them

Hope this Helps. :)

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.