The fastest and most memory efficient way to reset PK column values for a table with over 5 mililon records

Question

I have the following table schema:

CREATE TABLE [inputs].[source](
[Id] [int] IDENTITY(1,1) NOT NULL,
[CreatedOn] [datetime] NULL,
[ImportedOn] [datetime] NULL,
[Identifier_Value] [nvarchar](25) NULL,
[Identifier_PrefixLength] [int] NULL,
[Identifier_SuffixLength] [int] NULL,
[Identifier_CRC] [bigint] NULL,
[Code 1_Value] [nvarchar](max) NULL,
[Code 1_PrefixLength] [int] NULL,
[Code 1_SuffixLength] [int] NULL,
[Code 1_CRC] [bigint] NULL,
[Code 2_Value] [nvarchar](max) NULL,
[Code 2_PrefixLength] [int] NULL,
[Code 2_SuffixLength] [int] NULL,
[Code 2_CRC] [bigint] NULL,
[Code 3_Value] [nvarchar](max) NULL,
[Code 3_PrefixLength] [int] NULL,
[Code 3_SuffixLength] [int] NULL,
[Code 3_CRC] [bigint] NULL,
[Code 4_Value] [nvarchar](max) NULL,
[Code 4_PrefixLength] [int] NULL,
[Code 4_SuffixLength] [int] NULL,
[Code 4_CRC] [bigint] NULL,
[Number_Value] [decimal](19, 5) NULL,
[Approval_Value] [nvarchar](max) NULL,
[Approval_NumberValue] [decimal](19, 5) NULL,
[Volume_Value] [decimal](19, 5) NULL,
[Program_Value] [nvarchar](max) NULL,
[Program_NumberValue] [decimal](19, 5) NULL,
[Info_Value] [nvarchar](max) NULL,
[Info_PrefixLength] [int] NULL,
[Info_SuffixLength] [int] NULL,
[Info_CRC] [bigint] NULL,
[UpdatedOn] [datetime] NULL,
[FacilityTimeZoneId] [nvarchar](80) NULL,
[ParsingTimeZoneId] [nvarchar](80) NOT NULL,
[CreatedOnUtc] [datetime] NULL,
[ImportedOnUtc] [datetime] NULL,
PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON 
[PRIMARY]) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

For tests, I've added 5 million rows into the table. Imagine you've been using this table for some time and [Id] column right now has 'gaps' between values for example (100, 120, 300, 3400 and so on).

I know that it should not be the case, but because of some bad design desicions made before I need to update [Id] column to have values in sequence (1, 2, 3, 4 and so on). I've found the following solution:

1. Disable all nonclustered indexes on [inputs].[source] table
2. Create [inputs].[source_temp] table with the same schema but without identity on [Id] column
3. Switch to [inputs].[source_temp] table
4. Remove PK from [inputs].[source_temp]
5. Update [Id] column in [inputs].[source_temp]
6. Return PK to [inputs].[source_temp]
7. Switch back to [inputs].[source]
8. Rebuild disabled indexes

Here is the script:

DECLARE @EnableOrRebuild as nvarchar(20) 
DECLARE @Sql AS nvarchar(MAX)='';
DECLARE @TableName as nvarchar(200) = 'source'
DECLARE @SchemaName as nvarchar(200) = 'inputs'
SET @EnableOrRebuild = 'DISABLE'

SELECT @Sql = @Sql + N'ALTER INDEX ' + quotename(i.name) + N' ON ' + quotename(s.name) + '.' + quotename(o.name) + ' ' + @EnableOrRebuild + N';' + CHAR(13) + CHAR(10)
  FROM sys.indexes i
 INNER JOIN sys.objects o ON i.object_id = o.object_id
 INNER JOIN sys.schemas s ON s.schema_id = o.schema_id
 WHERE i.type_desc = N'NONCLUSTERED'
   AND o.type_desc = N'USER_TABLE'
   AND o.name = @TableName 
   AND s.name = @SchemaName

EXEC (@Sql);


CREATE TABLE [inputs].[source_temp](
  [Id] [int] NOT NULL,
  [CreatedOn] [datetime] NULL,
  [ImportedOn] [datetime] NULL,
  [Identifier_Value] [nvarchar](25) NULL,
  [Identifier_PrefixLength] [int] NULL,
  [Identifier_SuffixLength] [int] NULL,
  [Identifier_CRC] [bigint] NULL,
  [Code 1_Value] [nvarchar](max) NULL,
  [Code 1_PrefixLength] [int] NULL,
  [Code 1_SuffixLength] [int] NULL,
  [Code 1_CRC] [bigint] NULL,
  [Code 2_Value] [nvarchar](max) NULL,
  [Code 2_PrefixLength] [int] NULL,
  [Code 2_SuffixLength] [int] NULL,
  [Code 2_CRC] [bigint] NULL,
  [Code 3_Value] [nvarchar](max) NULL,
  [Code 3_PrefixLength] [int] NULL,
  [Code 3_SuffixLength] [int] NULL,
  [Code 3_CRC] [bigint] NULL,
  [Code 4_Value] [nvarchar](max) NULL,
  [Code 4_PrefixLength] [int] NULL,
  [Code 4_SuffixLength] [int] NULL,
  [Code 4_CRC] [bigint] NULL,
  [Number_Value] [decimal](19, 5) NULL,
  [Approval_Value] [nvarchar](max) NULL,
  [Approval_NumberValue] [decimal](19, 5) NULL,
  [Volume_Value] [decimal](19, 5) NULL,
  [Program_Value] [nvarchar](max) NULL,
  [Program_NumberValue] [decimal](19, 5) NULL,
  [Info_Value] [nvarchar](max) NULL,
  [Info_PrefixLength] [int] NULL,
  [Info_SuffixLength] [int] NULL,
  [Info_CRC] [bigint] NULL,
  [UpdatedOn] [datetime] NULL,
  [FacilityTimeZoneId] [nvarchar](80) NULL,
  [ParsingTimeZoneId] [nvarchar](80) NOT NULL,
  [CreatedOnUtc] [datetime] NULL,
  [ImportedOnUtc] [datetime] NULL,
CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED 
(
  [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

ALTER TABLE [inputs].[source] SWITCH TO [inputs].[source_temp];

ALTER TABLE [inputs].[source_temp] DROP CONSTRAINT [PK_Test];

;WITH CTE AS (
    SELECT
        [Id],
        ROW_NUMBER() OVER (ORDER BY [Id]) AS RN
    FROM [inputs].[source_temp]
)
UPDATE CTE
SET [Id] = RN
WHERE [Id] != RN;

ALTER TABLE [inputs].[source_temp] ADD CONSTRAINT [PK_Test] PRIMARY KEY (ID); 

ALTER TABLE [inputs].[source_temp] SWITCH TO [inputs].[source]


SET @EnableOrRebuild = 'REBUILD'

SELECT @Sql = @Sql + N'ALTER INDEX ' + quotename(i.name) + N' ON ' + quotename(s.name) + '.' + quotename(o.name) + ' ' + @EnableOrRebuild + N';' + CHAR(13) + CHAR(10)
  FROM sys.indexes i
 INNER JOIN sys.objects o ON i.object_id = o.object_id
 INNER JOIN sys.schemas s ON s.schema_id = o.schema_id
 WHERE i.type_desc = N'NONCLUSTERED'
   AND o.type_desc = N'USER_TABLE'
   AND o.name = @TableName 
   AND s.name = @SchemaName

EXEC (@Sql);

DROP TABLE [inputs].[source_temp];

The execution time is about 94 sec. Here is the [execution plan] (https://www.brentozar.com/pastetheplan/?id=rJJNpJFhn).

I found that if I don't remove PK on [inputs].[source_temp] the execution will take 236 sec. With this execution plan.

Is it ok to remove PK in that case or there is a better solution. And maybe there is another approach to improve performance?

I think the main goal is, "the fastest runtime regardless of how much hardware resources are consumed in doing so". This script will be executed when application is stopped for maintenance and "the downtime against this table specifically during this change" is not a problem.

In short, due to some mistakes in the implementation of business logic the id values ​​in the table are close to their maximum (2,147,483,647), although the number of records in the table is not more than 10 - 20 million.

The implementation is fixed, but the consequences in DB must be corrected. I want to reset IDs in the table and RESEED to the max ID.

I know I can't prevent new gaps appearing in future. I just need to apply this script one time.

Answer 1

The biggest bottleneck in your scheme is rebuilding the clustered index, followed by the single threaded and fully logged UPDATE. Avoiding both of those might offset the cost of an export and import. It will certainly use less memory and generate less log.

If you have enough fast local storage available, an alternative you should benchmark:

1. Create a view referencing every column of the table except the identity
2. Export data from this view in bulk to fast local storage in native format
3. Disable all the nonclustered indexes
4. Truncate the table and reset the identity seed value if desired
5. Ensure the database recovery model is SIMPLE or BULK_LOGGED
6. Bulk load the new view (not the table) from the export file with a TABLOCK hint for minimal logging
7. Rebuild the nonclustered indexes
8. Drop the temporary view

The bulk load will be single threaded but otherwise very efficient, with no sorting. The export can be performed with the bcp utility. You can use any bulk load method including BULK INSERT in T-SQL.

There is no need to drop and rebuild the clustered index with this approach. New identity values will be assigned during the import.

• Concurrent parallel bulk load is possible, but practically quite tricky to achieve with the clustered index in place on a single table.

  If you decide to explore this option, it would probably be easier to export to multiple files (one per core), concurrently bulk load multiple clustered tables with disjoint identity ranges, switch them in to individual partitions of a replacement table, then remove the partitioning (on the identity).

• Concurrent parallel bulk load to a heap is much easier, but then you lose many of the benefits like not rebuilding the cluster.

That said, maximum performance all at once isn't always the best option. Many tables of this kind are append-only, in which case the processing can be done piecemeal as time and resources allow.

If the table experiences updates, a trigger can be used to replicate changes to the in-progress reorganised table. You do need extra storage in this case, but so does building a clustered index on a heap table.

In short, there are many options. Which is most suitable to you depends on your detailed requirements.

Answer 2

You could avoid dropping and rebuilding the primary key by remapping the identity values one row at a time.

This removes the Split, Sort, and Collapse operators necessary to avoid transient key violations and also removes the need for the Eager Table Spool that provides Halloween Protection. SQL Server has to take these precautions, but you know enough about the intent of the changes to know key violations aren't possible if performed in ascending key order.

The existing execution plan is particularly inefficient because it tries to sort the whole table in memory, spills to disk, and makes a complete copy of the source table in the spool.

Updating a row at a time avoids the sorting and spooling. Add to that the cost of recreating the table with the clustered index rebuild and the row-at-a-time might pay off.

Row by row update:

SET NOCOUNT, XACT_ABORT ON;
SET STATISTICS XML OFF;

DECLARE c CURSOR LOCAL FORWARD_ONLY DYNAMIC READ_ONLY FOR
SELECT ST.Id
FROM inputs.source_temp AS ST
ORDER BY ST.Id ASC;

OPEN c;

DECLARE 
    @Id integer, 
    @Nid integer = 1; -- New seed

WHILE 1 = 1
BEGIN
    FETCH NEXT FROM c INTO @Id;
    IF @@FETCH_STATUS = -1 BREAK;

    UPDATE inputs.source_temp
    SET Id = @Nid
    WHERE Id = @Id;

    SET @Nid += 1;
END;

CLOSE c; DEALLOCATE c;

Original update plan (entire table):

Replacement update plan (per row):