スキーマ変更時のベスト・プラクティス　その２

マイクロソフトの植田です。

今回は前回ご紹介した「スキーマ変更時のベスト・プラクティス」の第二弾をご紹介したいと思います。元になっているU.S.のサイトは以下です。

https://blogs.msdn.com/sqlcat/archive/2006/03/31/566046.aspx

注：下記内容に関する詳細の確認をご希望される場合は上記のブログを参照いただけますようお願いします。

本ドキュメントは以下の方を対象としております。

l 開発者、テストエンジニア、データベース・アドミニストレータ

l データベース、および、Microsoft SQL Serverについて基本的な知識をお持ちの方

スキーマ変更の制御（その２）

質問：SQL Server 2005においてスキーマ変更を行う際のベスト・プラクティスはなんでしょうか。最も早く、簡単な方法は何でしょうか？

答え：その１では、SQL Server 2005 におけるスキーマ変更の動作の要点をまとめました（「スキーマ変更時のベスト・プラクティス」参照）。その２では、データ型、および、ほとんどのデータ長の変更時に発生するバッチ更新要求を分離するベスト・プラクティス・テクニックについて見て行きたいと思います。この方法は10億行規模の大きなテーブルにおいてパフォーマンス、および、同時並行性に与える影響を最小限に保ちつつ、スキーマ変更を短時間で行うことを可能にします。

シナリオ：5文字の郵便番号が格納されている列があり、それを10文字のフォーマット、例えば12345-0001、に拡張するケースを考えましょう。以下のステートメントで郵便番号（Zip code）の長さをchar(5)からchar(10)に変更できます。

alter table alter column ZipCode Char(10)

しかし、10億行のテーブルにおいて列のデータ長を変更すると、同時並行性を著しく損なわせるバッチ更新処理（プロファイラーにて確認可能）が発生します。もしこの様子をプロファイラーで確認したいのであれば、「スキーマ変更時のベスト・プラクティス」を参照してください。

ベスト・プラクティス

NULLプロパティを使用すること（「DEFAULT WITH VALUES」句を使用するケースは除きます）は列を短時間で追加する場合のベスト・プラクティスです。その列に値を設定しなければならない場合においても、NULLプロパティを使用することで、Alter tableステートメントの場合とは異なり、更新処理を分離できるので自由度の高い方法を利用できます。

（更新処理の分離を）実現するためのアプローチ：

1. NewZipという名前の列を追加する

2. 分離する個々のバッチサイズを設定する

3. 詳細な住所コードに変更してNewZip列にデータを投入する

4. 古いZipCode列を削除する

5. NewZip列をZipCode列に変更する（任意）

ステップ1：新しい列の追加

alter table MyTable

add NewZip char(10) NULL

上記のAlter tableが一瞬で終わると（こちらのテストでは約1秒）、テーブルの全ての行のNewZip列はNULLになります。実際のケースでは他の列の値、または、その他の値をNewZip列に代入する必要があるかも知れません。ステップ2では、書き込みロックの取得によるデータ投入処理が同時並行性に与える影響を最小限にするために、一度に変更する行の数を制限する「SET ROWCOUNT」句が使用します。「SET ROWCOUNT」を設定する代わりの方法は「WHERE」句を用いて一度に変更する行の数をあるキーの範囲で限定することです。

ステップ2：更新処理のバッチサイズ設定

--- (2) set piecemeal batch size

set nocount on

SET ROWCOUNT 1000

次に、NewZip列にNULLの値がなくなるまで繰り返し、段階的な更新処理を流します。今回の例では全ての行にchar(5) ZipCode列の値を用いて、値を代入していきます。代替案として、「lookup」や「join」を使用して、選択的に追加した列に値を代入していくこともできます。

ステップ3：分離された個々の（段階的）更新

declare @rowcount int, @batchcount int, @rowsupdated int

select @rowcount = 1,@batchcount = 0, @rowsupdated = 0

while @rowcount > 0

begin

       begin tran

              update MyTable

              set NewZip = ZipCode

              where NewZip is NULL

              set @rowcount = @@rowcount

              select @rowsupdated = @rowsupdated + @rowcount

              if @rowcount > 0 select @batchcount = @batchcount + 1

       commit

end

ステップ4：char(5) ZipCode列の削除（任意）

alter table MyTable

drop column ZipCode

go

ステップ5：NewZip列の名前をZipCodeに変更（任意）

exec sp_rename 'MyTable.NewZip','ZipCode','COLUMN'

go

--- Observe the data type for ZipCode is now char(10)

sp_help MyTable

ZipCodeに対するトリガー

仮にユーザーがNewZipへの更新の後に顧客のZipCodeを変更すると、どのようなことが起こるでしょう。もしユーザーのアプリケーションが段階的な更新作業中、NewZipがすでに古い住所コードで置き換えられている時、にchar(5)のZipCodeを更新したら、このような場合ZipCodeとの整合性が崩れてしまいます。段階的な更新作業中は、ZipCodeとNewZipの整合性を保つために更新トリガーが利用できます。もし古いchar(5)のZipCodeを削除して（ステップ4）NewZip列の名前をZipCodeに変更する（ステップ5）予定であれば、段階的な更新処理の後ではその更新トリガーは必要なくなるでしょう。

明示的なデータ型の変更

いくつかのデータ型の変更は明示的な変換を必要とします。これは「alter table alter column」を使用した暗黙的なデータ型の変換が行えないケースです。例えば、DATETIMEデータ型をINTまたはYYYYMMDDフォーマットのnumeric(8)型に変更する時は、明示的なデータ変換が必要とされます。このシナリオは「convert (またはdatepart)」関数を使って、上記の段階的な更新シナリオを全く同じように実行することにより対処できます。

列の削除

列の削除はスキーマのみの操作になります。この場合は、削除される列からオブジェクトを再利用スペースに移動させない限り、同時並行性の問題は発生しません。

ユニーク、および、プライマリーキー制約

ユニーク、および、プライマリーキー制約の追加は読み込み処理、ソート処理、書き込み処理を含みます。既定では、プライマリーキー制約がクラスタ化インデックスを付加し、デフォルトのユニーク制約は非クラスタ化インデックスを付加します。すでにユニーク制約（非クラスタ化インデックス）を持っている大きなテーブルに対してプライマリーキー制約（そしてデフォルトのクラスタ化インデックス）を追加する場合、パフォーマンス、および、同時並行性の問題が発生することは明白です。これはプライマリーキー（クラスタ化インデックス）を追加することによって非クラスタ化インデックスの再構築が行われ、RID（列ID）がプライマリーキーに置き換えられるためです。

Alter table, create/alter indexはユニーク、および、プライマリーキー制約/インデックスを制御するためのONLINEオプションを持っています。可能であれば、最高のパフォーマンス、および、最高の同時並行性を実現するために、インデックス操作の際はONLINEオプションを使うべきです。もしくは、メンテナンス時間にこれらの操作を延期するのが賢明でしょう。

結論、および、ベスト・プラクティス

SQL Server 2005はスキーマの変更をサポートしています。しかし、いくつかのスキーマ変更は他のスキーマ変更と比べて高速に処理することができます。以下の場合、最も速くスキーマ変更を行うことができます：

1. NULLプロパティを設定して列を追加

2. NULL、および、デフォルトプロパティを設定して列を追加

3. NOT NULLプロパティをNULLに変更

4. デフォルト制約を追加

5. チェック、または、デフォルト制約を削除

パフォーマンス、および、同時並行性を最も高くするための、大きなテーブルにおけるスキーマ変更のベストプラクティスはNULLプロパティで列を追加することを含みます。新しく追加した列に値をセットしなければならない場合、列にまずNULLプロパティを設定することで「alter table」ステートメントで一括更新される代わりに、更新処理を小さな単位に分離する自由度が与えられます。大きなテーブルにおいて同時並行性に多大な影響を及ぼすバッチ更新処理を行うより、一度に更新する行数を制限して段階的に更新処理を行う方がパフォーマンスの面においてずっと望ましい方法です。更新する列の数を制限する一つの方法として「SET ROWCOUNT」を使用することが上げられます。この方法は段階的に更新処理を行うことにより、発生するブロッキングの影響を劇的に削減します。もし、この段階的な更新処理が完了した後、作成した列に対してNULL値を許可しないことが重要ならば、その列を使用するアプリケーションはこの方法を実施するべきでしょう。

チェック制約が含まれている場合、チェックを行うためにテーブル全体を読み込む必要があります。大きなテーブルでは、変更処理に付随して行われるテーブル走査は書き込み処理に影響を与える恐れがあります。

その他、大きなテーブルにおいて、変更に付随して生じるバッチ更新処理が、パフォーマンス、および、同時並行性に影響を与えるスキーマ変更として以下があげられます：

1. NULLプロパティの列を「DEFAULT」句、「WITH VALUES」句を使用して追加する

2. NOT NULLプロパティの列を追加する

3. NULLプロパティの列をNOT NULLに変更する

NULL列を利用して段階的な更新処理のベスト・プラクティスを採用することで、意図しない読み込み処理や書き込み処理などを避けることができ、高い同時並行性と最も速く、シンプルな方法を利用することができます。

コミュニティにおけるマイクロソフト社員による発言やコメントは、マイクロソフトの正式な見解またはコメントではありません。