INT to BIGINT, a SQL Story

“Hey, the primary key in one of our hottest tables is almost out of range in production…”

My initial thoughts when presented with the above problem was, “Oh, no big deal, we’ll just update it, right? WRONG!

Introduction

In SQL Server, the INT data type ranges from -2,147,483,648 to 2,147,483,647. When a primary key (PK) column with the IDENTITY(1,1) property approaches this upper limit, it poses a significant risk to database operations. This article details the challenges and solutions encountered during the migration of a PK column from INT to BIGINT in a high-traffic production environment.

Understanding the problem

The primary key uniquely identifies each row in a table. An IDENTITY(1,1) property ensures that each new row receives a sequential integer value starting from 1. However, SQL Server does not reuse identity values from deleted rows, leading to potential exhaustion of the INT range. In our case, the PK value had reached approximately 1.9 billion, with the table containing around 650 million rows.

What happens when you run out of range on a PK w/ identity turned on?

Well, nothing good! Every single new insert will start to fail as the data type in the PK column can’t physically hold a number higher than the top of the range!

Here’s what our table looked like to begin with:

CREATE TABLE [dbo].[LargeTable](
	[LargeTableId] [int] IDENTITY(1,1) NOT NULL,
	[ErrorMessage] [nvarchar](max) NULL,
	[DateCreated] [datetime]
	CONSTRAINT [PK_dbo.LargeTable] PRIMARY KEY CLUSTERED
	(
		[LargeTableId] ASC
	) WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Research and Testing Begins…

Attempt 1: Direct Column Alteration

“Can we just ALTER TABLE LargeTable ALTER COLUMN LargeTableId BIGINT?”

Ok, let’s test this out in a local database and see what happens:

Well, that didn’t work, but ok, let’s work through the error. This approach failed due to the existing PK constraint, which prevented the alteration of the column’s data type.

Attempt 2: Utilizing Row Compression

What about row compression?

I came across a blog post that mentioned the ability to update a column’s data type from INT to BIGINT by utilizing row compression. Ultimately, the same issue with the PK constraint stopped this approach from working but remains a great option for non-constrained columns.

Attempt 3: Dropping and Recreating the Primary Key

We can drop the constraint and then run the ALTER command.

That worked! Let’s confirm this will work though in a real production situation. If possible, the best way to do something like this is request a copy of the database (specifically, just the actual table and any related tables) be made temporarily somewhere you can test this out.

Now that we have a copy of what the table looks like in production, let’s attempt the above command.

When this was attempted, not only did the command never finish, but it was drawing down a ton of server resources while it was trying to apply these changes!

The reason this was occurring has to do with how data is structured in SQL server. If you look at the PK definition, you’ll notice that it’s CLUSTERED . This means that the way that data is stored in this table is dictated by the PK value. Unfortunately for us, this also means that if we were to drop the PK, the index would go away and the table would have to reorganize itself as a HEAP instead of the B-Tree structure that it was w/ the Clustered PK.

We’re now in a situation where we can’t realistically get rid of the PK or alter it in any way! Sounded like the only option was to migrate to a new table and backfill all the data over (remember this table is highly used and contains well over 650 million rows!)

Attempt 4: Creating a New Table and Data Migration

Create a copy of the table but with the PK being a BIGINT and come up with a syncing mechanism

This approach is generally what other DBA experts recommend when dealing with a production table that needs this treatment but what wasn’t clear from further research was a consistent implementation of approach.

A few approaches that were considered:

• Option 1: Backfill new table with rows from current table and create a TRIGGER to keep new rows added or updated in the current table in sync with the new one.
• Option 2: Modify the application code to insert/update new records in both tables while backfilling. Then, once everything has been backfilled, cut over to using just the new table.
• Option 3: Create a script to backfill and keep records in sync between the current and new tables. Once finished, take a brief downtime to swap the names of the tables, keys, and indexes (more details on this later!).

Ultimately, Option 3 was chosen as the most straightforward, least risky, and lowest amount of required downtime with no impact to application code necessary.

Some additional considerations were made once this approach was chosen:

• Our current table was enormous, not just because of the total number of rows of data but also because one of the columns was an nvarchar(max) , a frequent source of performance pain because any time it exceeded 4000 characters in length, SQL will store that information “off page”, increasing query time to retrieve it from disc

Since a new table was being created, we decided to truncate this column down to the maximum size before being stored off page, nvarchar(4000)

Again, this was tested against a copy of the table in production on a separate server and once a working script was written, the test went smoothly. It took a few hours to insert everything from the old table to the new, but things were looking good!

The backfill sync script looked like this:

DECLARE
	@CurrentId INT,
	@MaxId BIGINT,
	@message VARCHAR(1000),
	@batchSize INT,
	@EndTime AS DATETIME,
	@StartTime AS DATETIME,
	@totalInserted INT,
	@totalIterations INT

-- Step 3: Turn on identity insert for new table
SET IDENTITY_INSERT dbo.LargeTable_NEW ON

-- Step 4: Set current transaction
SET @CurrentId = 0; -- Update this as necessary
SET @batchSize = 10000;

SELECT @StartTime = GETUTCDATE()
SELECT @EndTime = @StartTime

-- Step 5: Get max and min ID from current table
SELECT TOP 1 @MaxId = a.LargeTableId FROM LargeTable AS iac WITH (NOLOCK) ORDER BY a.LargeTableId DESC
SELECT TOP 1 @CurrentId = COALESCE(LargeTableId, 0) FROM LargeTable_NEW AS new WITH (NOLOCK) ORDER BY new.LargeTableId DESC
SET @totalInserted = 0
SET @totalIterations = 0

-- Step 6: Start loop while the current ID is less than the max id
WHILE @CurrentId < @MaxId

BEGIN
	SET @message =
		'Processing from [' + CAST(@CurrentId AS VARCHAR(100)) + ']'
		+ ' to [' + CAST(@CurrentId + @batchSize AS VARCHAR(100)) + ']  '
		+ CAST(ROUND(((CAST(@CurrentId AS FLOAT)/CAST(@MaxId AS FLOAT))), 4)*100 AS VARCHAR(100)) + '%%'
	RAISERROR (@message, 0, 1) WITH NOWAIT;

	SET NOCOUNT ON;

	-- Start timestamp
	SET @StartTime = GETUTCDATE()

	INSERT INTO LargeTable_NEW
	(
		LargeTableId,
		ErrorMessage,
		DateCreated
	)
	SELECT
		f.LargeTableId,
		f.ErrorMessage,
		f.DateCreated
	FROM
	(
		-- NOTE: This batch size is hardcoded here because SQL server was creating horrible execution plans with this value
		-- being represented as a variable, even though it was constant.  This change sped up the execution time by 80% towards
		-- the back half of the migration
		SELECT TOP 200000
			old.LargeTableId,
			CONVERT(NVARCHAR(4000), old.ErrorMessage) AS ErrorMessage,
			old.DateCreated
		FROM
			LargeTable AS old WITH (NOLOCK)
			LEFT JOIN LargeTable_NEW AS new ON new.LargeTableId = old.LargeTableId
		WHERE
			old.LargeTableId > @CurrentId
			AND new.LargeTableId IS NULL
		ORDER BY
			old.LargeTableId
	) AS f
	OPTION (MAXDOP 2);

	SET @totalInserted = @totalInserted + @@ROWCOUNT

	SELECT TOP 1
		@CurrentId = LargeTableId
	FROM
		LargeTable_NEW
	ORDER BY
		LargeTableId DESC

	SET @totalIterations = @totalIterations + 1

	-- End timestamp
	SET @EndTime = GETUTCDATE()

	-- Log elapsed
	SET @message = 'Elapsed: [' + CONVERT(VARCHAR, CAST(@EndTime - @StartTime AS TIME), 114) + ']'
		+ ' Inserted So Far: [' + CAST(@totalInserted AS VARCHAR) + ']'
	RAISERROR (@message, 0, 1) WITH NOWAIT;
END

SET @message = 'Total Iterations = [' + CAST(@totalIterations AS VARCHAR) + ']'
			   + ' Total Records = [' + CAST(@totalInserted AS VARCHAR) + ']'
RAISERROR (@message, 0, 1) WITH NOWAIT;

-- Step 7: Set IDENTITY_INSERT to OFF for the new table
SET IDENTITY_INSERT [dbo].[LargeTable_NEW] OFF;

We proceeded to create the new table in production and start running the script to backfill the data. With things looking good, a cutover plan was put in place where we planned our downtime window.

At this point a few things happened in quick succession over the next week or so:

• About 100 million rows in, I began to notice a considerable drop in insert speed
• Decided to drop Indexes and restarted the script to speed up inserts
• Halfway through this, attempted to reapply the indexes and this ultimately failed to complete without SQL requiring a large number of resources impacting production
• Revisited the timeline and pushed out the cutover to give the script more time to complete w/ indexes in place (knowing the inserts would take longer)
• Captured a query plan of the script as it was inserting data and noticed an issue where the variable holding the size of the batch of rows to retrieve from the current table was causing the plan to underestimate the number of rows coming back by 80%
• Fixed this by hard coding the value in the script and instantly noticed a significant increase in throughput
• A few days later, all the data was inserted, and a new cutover was scheduled
• In order to swap the names of the current and new tables/constraints/indexes, the table needs to stop being written to because of the schema lock that’s necessary to apply the change
• This is the source of the downtime necessary and was accomplished by turning off our application temporarily, then running a final round of the backfill script to insert any new data, and finally executing sp_rename on all the appropriate objects
• A total of ~7 minutes of downtime occurred with no hiccups and the application was none the wiser about the swap

Lessons Learned!

Migrating a primary key column from INT to BIGINT is far from a straightforward task, especially in production environments with large, high-traffic tables. Here are the critical lessons we learned through this process:

1. Thorough Testing is Essential

• Simulate the Production Environment: Ensure your test environment closely mirrors your production setup in terms of data volume, schema, and concurrent workloads. This helps identify challenges that may not surface in a simplified test.
• Test Every Step: Validate each component of the migration strategy independently, such as backfill scripts, synchronization mechanisms, and cutover plans.
• Iterate on Test Results: Incorporate findings from test runs to refine scripts and approaches. For example, adjusting batch sizes or indexing strategies can significantly improve performance.

2. Understand the Impact of Constraints

• Primary Key Constraints: The clustered index associated with a primary key introduces complexity when altering a column. Dropping and recreating constraints can lead to resource-intensive operations and downtime.
• Data Organization: A clustered index dictates the physical order of data in the table. Altering it often requires the table to reorganize, which is costly for large datasets.

3. Optimize for Performance

• Monitor Resource Usage: Large-scale data migrations can consume significant server resources. Use tools like SQL Server Profiler or Extended Events to monitor performance and adjust as needed.
• Batch Processing: Migrating data in manageable batches helps avoid excessive locking and reduces the strain on the database.
• Optimize Query Plans: Variables in SQL scripts can sometimes lead to suboptimal execution plans. Hardcoding constants, where feasible, can improve performance significantly, as seen in our experience.

4. Plan for Synchronization and Downtime

• Data Synchronization: Implementing a robust synchronization mechanism between old and new tables ensures no data is lost during the migration. Triggers, scripts, or dual-write strategies can help achieve this.
• Downtime Management: While minimizing downtime is ideal, a brief period may be necessary for schema changes. Schedule this during low-traffic windows and communicate with stakeholders well in advance.

5. Iterate and Adapt

• Iterative Improvement: Even well-planned strategies may encounter unforeseen challenges. For instance, the slowdown in our backfill process due to an underestimated query plan required quick adjustments.
• Flexibility is Key: Be prepared to revise your approach mid-process. For example, dropping and recreating indexes improved performance but introduced its own set of trade-offs.

6. Communicate with Stakeholders

• Cross-Functional Collaboration: Keep teams such as DevOps, product, and engineering informed about the migration timeline and potential risks.
• Regular Updates: Share progress, highlight completed steps, and address any concerns to build confidence in the migration process.

7. Document the Process

• Detailed Records: Document every step, from initial research and testing to the final implementation. Include scripts, configurations, and decision rationale.
• Reusable Templates: A well-documented migration process can serve as a template for future projects, saving time and effort.

8. Understand Your Data

• Column Optimization: Migrating gave us the opportunity to optimize certain columns, such as truncating nvarchar(max) to nvarchar(4000), reducing storage requirements and improving query performance.
• Assess Data Growth: Use the migration process as a chance to analyze data growth trends and plan for future scalability.

9. Expect the Unexpected

• Anticipate Delays: Complex migrations often take longer than anticipated due to unforeseen complications, such as resource contention or unexpected query behaviors.
• Backup and Recovery Plans: Always have a rollback plan in case the migration fails, and test backups to ensure data integrity.

10. Celebrate Success

• Acknowledgment: Migrations like these require effort from multiple teams. Celebrate milestones and share lessons with the broader organization to foster a culture of learning and collaboration.

---

About Matthew Preciado

With over 15 years of experience in software engineering, I have worked on various web development, cloud services, and e-commerce projects for leading companies in the travel, home services, and automotive industries.

As a Senior Software Engineer at Empower, I work on cross-cutting concerns on the Platform Team, enabling other developers throughout the organization.

I am proficient in C#, .NET, and other web technologies, and I enjoy working with agile teams and applying best practices to deliver high-quality and user-friendly solutions. I am passionate about creating innovative and impactful products that enhance the lives of people and solve real-world problems.

Connect with Matthew