SQL Server 2012 のメンテナンスウィザードでオンラインインデックスの再構築を使用したところ、オンラインインデックスの再構築がサポートされる場合でも ONLINE = OFF で インデックスの再構築を行うT-SQLが実行されることがあります。今回はオフラインのリビルドがされるケースのメモになります。

検証環境は次のとおり

  • SQL Server 2012 Developer Edition
  • SQL Server 2012 Management Studio
  • Windows Server 2012 Datcentert Edition に SQL Server, Managemet Studio ともにインストール

1.オンラインでインデックスを再構築できないケース

オンラインインデックスリビルドに関して SQL Server 2008 では次のような制限がありました。

SQL Server 2008 R2 では、クラスタードインデックス の再構築をオンラインで行う場合、カラムに nvarchar(max) や ntext 型が含まれているとオフラインでの再構築を行う必要がありました。実際に SQL Server 2008 R2 のオンラインでインデックス操作を実行する場合のガイドラインのページ(http://technet.microsoft.com/ja-jp/library/ms190981(v=sql.105).aspx)には次のような記載があります。

  • 基になるテーブルに image、ntext、text、varchar(max)、nvarchar(max)、varbinary(max)、xml などの LOB (ラージ オブジェクト) データ型が含まれている場合、クラスタ化インデックスの作成、再構築、または削除は、オフラインで行う必要があります。
  • テーブルに LOB データ型が含まれていても、そのデータ型の列がキー列または非キー (付加) 列としてインデックス定義で使用されていない場合は、一意ではない非クラスタ化インデックスをオンラインで作成できます。LOB データ型の列を使用して定義されている非クラスタ化インデックスの作成や再構築は、オフラインで行う必要があります。

というようにLOB型のカラムがテーブルに含まれている場合、クラスタードインデックスはそもそもオフラインインデックスできないという制限がありました。また、非クラスタードインデックスでもインデックスにLOB型のカラムが含まれているとオフラインでインデックスを行う必要がありました。

SQL Server 2012 の場合はオンラインインデックスの機能が強化され、次のような記述に変更されています(引用:http://technet.microsoft.com/ja-jp/library/ms190981%28v=sql.110%29.aspx)。

  • 基になるテーブルに image、 ntext、text などの LOB (ラージ オブジェクト) データ型が含まれている場合、クラスター化インデックスの作成、再構築、または削除は、オフラインで行う必要があります。
  • テーブルに LOB データ型が含まれていても、そのデータ型の列がキー列または非キー (付加) 列としてインデックス定義で使用されていない場合は、一意ではない非クラスター化インデックスをオンラインで作成できます。
  • ローカル一時テーブルのインデックスの作成、再構築、または削除は、オンラインでは実行できません。 この制限は、グローバル一時テーブルのインデックスには当てはまりません。

いろいろ記載されていますが、簡単に言うと 2012 では、nvarchar(max), varchar(max), nvarchar(max), varchar(max), xml 型がテーブルのカラムに含まれている場合もクラスタードインデックスをオンラインで再構築できるようになったということです。テーブルにnvarchar(max)等が含まれている場合もインデックスの列として使用していなければ、非クラスタードおよびクラスタードインデックスをオンラインインデックスリビルドを行えるようになったので、オンラインインデックス再構築の制約がかなり緩和されたとこになります。

2.動作確認

SQL Server 2012 での オンラインインデックスの機能強化のおかげで、すばらしいことにこれで、ほとんどのケースで、オンラインインデックスのリビルドが可能になったと思います。

動作を確認しようと思い、メンテナンスプランのインデックスの再構築タスクで、下のように インデックスの再作成中にオンラインのインデックスを保持するにチェックして、 オンラインでのインデックスの再構築をサポートしていない種類のインデックス用のオプション で オフラインでインデックスを再構築をする を選択してみました。

実際に発行される T-SQL はどんなもんだろうと思って確認してみたら、nvarchar(max) がテーブルの列に含まれる クラスタードインデックスの場合に ONLINE = OFF というオプションが設定されたT-SQL を使用してリビルドを行っていました。実際に該当するT-SQLを ONLINE=ON に変更して Management Studio でインデックスを再構築する場合はエラーにならないので、オンラインインデックスの再構築はできるはずです(もちろん2008 R2 の場合はエラーになります。)。もしかしたら 2008 R2 互換のクエリーを生成しているのかもしれません。

このような場合は、インデックスの再構築タスクのオプションで、インデックスを再構築しないを選択し、オフラインインデックスのT-SQLが生成されるインデックスに関しては別途自前でインデックスをオンライン再構築するT-SQLを使用するようにする必要があるかもしれません。

3.まとめ

説明は以上です。誤り、指摘点などがございましたらご連絡ください。