SQLServer: int型のID値を持つテーブルをできる限り短時間でbigint型に変更する

背景

プロダクション環境において、とあるint型のID値を持つテーブルのレコード数が18憶レコード程になっていました。
int型の最大値は2^31-1 (2147483647)であり、この値を上回ろうとするとエラーでINSERTできなくなります。
ID値の型をbigintに変更できれば最大値が2^63-1 (9223372036854775807)と、実質無限になるため、bigintにしたいです。
ただし、intからbigint変更に伴う関連サービスの停止期間はできる限り短くする必要があります。
この要件を満たすために実施した調査と、実際にプロダクション環境にて変更を行った内容を紹介します。

対象のテーブルは、簡易的に下記内容であるとします。

CREATE TABLE [dbo].[SampleTable](
	[column0_ID] [int] IDENTITY(1,1) NOT NULL,
	[column1] [int] NULL,
	[column2] [nvarchar](50) NOT NULL,
	[column3] [datetime] NOT NULL,
 CONSTRAINT [PK_SampleTable] PRIMARY KEY CLUSTERED 
(
	[column0_ID] ASC
))

また、クラスタ化インデックスとは別に、追加で非クラスタ化インデックスが3つ作成されているとします。

方法1:シンプルにテーブル定義を変更

やり方として最もシンプルな方法は、以下のようにカラムの型を変更させる方法です。

ALTER TABLE SampleTable ALTER COLUMN column0_ID bigint

ただし、PrimaryKey制約がついていると型を変更できないため、一度制約を削除する必要があり、実際は以下のようなクエリで実現できます。方法としてはとてもシンプルです。

--bigintに変えるために一度削除
ALTER TABLE SampleTable
DROP CONSTRAINT [PK_SampleTable]

--bigintに変更
ALTER TABLE SampleTable ALTER COLUMN column0_ID bigint

--もう一度制約追加
ALTER TABLE SampleTable
ADD  CONSTRAINT [PK_SampleTable] PRIMARY KEY CLUSTERED 
(
 [column0_ID] ASC
)

このクエリを使った変更手順は以下のイメージです。

1. SampleTableへの変更(INSERT/UPDATE/DELETE)ができるサービスを停止
2. 上記クエリを実行（この処理の実行時間＝サービス停止時間）

事前に所要時間を検証するために、プロダクション環境と同一スペックを持つDBサーバ上で、同じデータ量を持つ別テーブルを作成し、上記クエリを実行してみました。

その結果、最初のPrimaryKey制約を削除するクエリの実行時に、SQLServerの内部オブジェクトであるsysschobjsに対して長時間ロックをかけてしまい、それが原因で大量のブロッキングが起きてしまいました。
内部オブジェクトへのロックのため、今回対象としていたテーブル(SampleTable)以外に対して発行しているクエリもブロックされてしまうようです。

PrimaryKey制約の削除は、18憶レコードのクラスタ化インデックスを削除することも兼ねているため、おそらく内部でデータをヒープに変更するために大量のスキャン等が走っていたと考えられます。30分たっても終わらないことと、いかなるクエリでもブロックする可能性がある危険性を考慮して、この案は無しにしました。

#方法2:bigint版の別テーブルをあらかじめ作成しておき、地道に同期

以下のような、SampleTableのID値がbigintになったSampleTable2という別テーブルをあらかじめ用意しておき、地道に同期していく方法です。

CREATE TABLE [dbo].[SampleTable2](
	[column0_ID] [bigint] IDENTITY(1,1) NOT NULL,
	[column1] [int] NULL,
	[column2] [nvarchar](50) NOT NULL,
	[column3] [datetime] NOT NULL,
 CONSTRAINT [PK_SampleTable2] PRIMARY KEY CLUSTERED 
(
	[column0_ID] ASC
))

このテーブルを使った変更手順は以下のイメージです。

1. SampleTable2へ、SampleTableを事前にbcpで全件同期（※同期以降もSampleTableへの変更は随時行われるため、正確な同期はとれない）
2. 同期完了後、SampleTable2に対して、SampleTableと同様の非クラスタ化インデックス3つを作成（※先にインデックスを全て作ってしまうとbcpによるINSERTが劇的に重くなるため、同期完了後に作成するのが望ましい）
3. SampleTableへの変更(INSERT/UPDATE/DELETE)ができるサービスを停止
4. SampleTableとSampleTable2の差分をSampleTable2へ取り込み、完全に同期をとる
5. 完全に同期がとれていることをクエリでチェック
6. SampleTableおよびSampleTable2をリネームして、SampleTable2の名前をSampleTableに変更することでintからbigintへの変更を完了させる

手順4-6の合計時間が、サービス停止時間となります。差分取り込みや同期されているかの確認は、18憶レコードのテーブル2つを使ったクエリとなるため、高速に終わらせるためには工夫が必要です。

##2テーブルの同期の取り方

SampleTableへのINSERT/DELETE/UPDATEをSampleTable2へ反映させる必要があります。
今回、DELETEは実施されない機能だったため、INSERTおよびUPDATEの反映のみ行いました。

■ INSERTされたレコードを反映させるクエリ

--1000万レコードずつINSERTを繰り返す
SELECT top (10000000) *
INTO #SampleTable_Tmp
FROM dbo.SampleTable a
where column0_ID > 1800000000 --ポイント:ここの値を適宜変更することで高速にINSERT対象を抽出可能
and not exists (select * from SampleTable2 b where a.column0_ID = b.column0_ID)
order by column0_ID

set identity_insert SampleTable2 on

insert into SampleTable2 (
	 column0_ID
	,column1
	,column2
	,column3
)
select
	 column0_ID
	,column1
	,column2
	,column3
from #SampleTable_Tmp
order by column0_ID --ポイント：SampleTable2の断片化を防ぐため、クラスタ化インデックスの並び順と同じ順番にソートしてINSERTする

set identity_insert SampleTable2 off
drop table #SampleTable_Tmp

■ UPDATEされたレコードを反映させるクエリ

select b.*
into
	#SampleTable_Diff
from SampleTable2 a
join SampleTable b on a.column0_ID = b.column0_ID
where 
	isnull(a.column1, 0) <> isnull(b.column1, 0)
or a.column2 <> b.column2
--ポイント：column3は絶対にupdateされないカラムであると事前に分かっていたため、比較対象から除外。これにより比較速度が向上する
option (maxdop 32) --ポイント：maxdopを多めに設定して高速化

update SampleTable2
set
	column1 = #SampleTable_Diff.column1
	,column2 = #SampleTable_Diff.column2
	,column3 = #SampleTable_Diff.column3
from
	#SampleTable_Diff
where
	SampleTable2.column0_ID = #SampleTable_Diff.column0_ID

##2テーブルが完全に同期されたことを確認する

素直に考えると、以下のような2テーブル間の差分をとればOKだと考えます。

select * from SampleTable2
except
select * from SampleTable

select * from SampleTable
except
select * from SampleTable2

ただし、この方法だとかなり低速なので、地道に以下のクエリでチェックします。
ID値の範囲を一定の範囲に限定し、それらを並列で実行させることで、exceptによる差分チェックよりもかなり高速に処理できます。

select * from SampleTable2 a
join SampleTable b on a.column0_ID = b.column0_ID
where 
(	isnull(a.column1, 0) <> isnull(b.column1, 0)
or a.column2 <> b.column2
or a.column3 <> b.column3
)
and a.column0_ID between 1 and 300000000
option (maxdop 16)

select * from SampleTable2 a
join SampleTable b on a.column0_ID = b.column0_ID
where 
(	isnull(a.column1, 0) <> isnull(b.column1, 0)
or a.column2 <> b.column2
or a.column3 <> b.column3
)
and a.column0_ID between 300000000 and 600000000
option (maxdop 16)

以下、ID値の範囲を全てカバーできるまで同じようなクエリを用意

##テーブルのリネーム
テーブルのリネームは一瞬で完了します。まず、int型番のテーブルをバックアップも兼ねて別名にリネームし、次にbigint型版のテーブルをもとのテーブル名にリネームして完了です。

--backup
sp_rename 'SampleTable', 'SampleTable_BK', 'object'
go

--backupした制約をrename
sp_rename 'PK_SampleTable', 'PK_SampleTable_BK', 'object'
go

--rename
sp_rename 'SampleTable2', 'SampleTable', 'object'
go

sp_rename 'PK_SampleTable2', 'PK_SampleTable', 'object'
go

#まとめ
かなり地道な方法ですが、方法2を採用したことで、サービスの停止期間を1時間に抑えることができました。
あまりこのようなケースは発生しないかと思いますが、同様の対応が必要になった方への参考になれば幸いです。