次の方法で共有

ALTER INDEX (Transact-SQL)

  • [アーティクル]

適用対象: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) Microsoft Fabric SQL Database

インデックスの無効化、再構築、再構成によって、またはインデックスに関するオプションの設定によって、既存のテーブルやビュー インデックス (行ストア、列ストア、または XML) を変更します。

Transact-SQL 構文表記規則

構文

SQL Server、Azure SQL Database、および Azure SQL Managed Instance の構文。

ALTER INDEX { index_name | ALL } ON <object>
{
      REBUILD {
            [ PARTITION = ALL [ WITH ( <rebuild_index_option> [ , ...n ] ) ] ]
          | [ PARTITION = partition_number [ WITH ( <single_partition_rebuild_index_option> [ , ...n ] ) ] ]
      }
    | DISABLE
    | REORGANIZE  [ PARTITION = partition_number ] [ WITH ( <reorganize_option>  ) ]
    | SET ( <set_index_option> [ , ...n ] )
    | RESUME [ WITH (<resumable_index_option> [ , ...n ] ) ]
    | PAUSE
    | ABORT
}
[ ; ]

<object> ::=
{
    { database_name.schema_name.table_or_view_name | schema_name.table_or_view_name | table_or_view_name }
}

<rebuild_index_option> ::=
{
      PAD_INDEX = { ON | OFF }
    | FILLFACTOR = fillfactor
    | SORT_IN_TEMPDB = { ON | OFF }
    | IGNORE_DUP_KEY = { ON | OFF }
    | STATISTICS_NORECOMPUTE = { ON | OFF }
    | STATISTICS_INCREMENTAL = { ON | OFF }
    | ONLINE = { ON [ ( <low_priority_lock_wait> ) ] | OFF }
    | RESUMABLE = { ON | OFF }
    | MAX_DURATION = <time> [ MINUTES ]
    | ALLOW_ROW_LOCKS = { ON | OFF }
    | ALLOW_PAGE_LOCKS = { ON | OFF }
    | MAXDOP = max_degree_of_parallelism
    | DATA_COMPRESSION = { NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE }
        [ ON PARTITIONS ( { <partition_number> [ TO <partition_number> ] } [ , ...n ] ) ]
    | XML_COMPRESSION = { ON | OFF }
        [ ON PARTITIONS ( { <partition_number> [ TO <partition_number> ] } [ , ...n ] ) ] }

<single_partition_rebuild_index_option> ::=
{
      SORT_IN_TEMPDB = { ON | OFF }
    | MAXDOP = max_degree_of_parallelism
    | RESUMABLE = { ON | OFF }
    | MAX_DURATION = <time> [ MINUTES ]
    | DATA_COMPRESSION = { NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE }
    | XML_COMPRESSION = { ON | OFF }
    | ONLINE = { ON [ ( <low_priority_lock_wait> ) ] | OFF }
}

<reorganize_option> ::=
{
       LOB_COMPACTION = { ON | OFF }
    |  COMPRESS_ALL_ROW_GROUPS =  { ON | OFF }
}

<set_index_option> ::=
{
      ALLOW_ROW_LOCKS = { ON | OFF }
    | ALLOW_PAGE_LOCKS = { ON | OFF }
    | OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }
    | IGNORE_DUP_KEY = { ON | OFF }
    | STATISTICS_NORECOMPUTE = { ON | OFF }
    | COMPRESSION_DELAY = { 0 | delay [ Minutes ] }
}

<resumable_index_option> ::=
 {
    MAXDOP = max_degree_of_parallelism
    | MAX_DURATION = <time> [ MINUTES ]
    | <low_priority_lock_wait>
 }

<low_priority_lock_wait> ::=
{
    WAIT_AT_LOW_PRIORITY ( MAX_DURATION = <time> [ MINUTES ] ,
                          ABORT_AFTER_WAIT = { NONE | SELF | BLOCKERS } )
}

Azure Synapse Analytics および Analytics Platform System (PDW) の構文。

ALTER INDEX { index_name | ALL }
    ON [ schema_name. ] table_name
{
      REBUILD {
            [ PARTITION = ALL [ WITH ( <rebuild_index_option> ) ] ]
          | [ PARTITION = partition_number [ WITH ( <single_partition_rebuild_index_option> ) ] ]
      }
    | DISABLE
    | REORGANIZE [ PARTITION = partition_number ]
}
[ ; ]

<rebuild_index_option> ::=
{
    DATA_COMPRESSION = { COLUMNSTORE | COLUMNSTORE_ARCHIVE }
        [ ON PARTITIONS ( { <partition_number> [ TO <partition_number> ] } [ , ...n ] ) ]
    | XML_COMPRESSION = { ON | OFF }
        [ ON PARTITIONS ( { <partition_number> [ TO <partition_number> ] } [ , ...n ] ) ]
}

<single_partition_rebuild_index_option> ::=
{
    DATA_COMPRESSION = { COLUMNSTORE | COLUMNSTORE_ARCHIVE }
    | XML_COMPRESSION = { ON | OFF }
}

引数

index_name

インデックスの名前です。 インデックス名は、テーブルまたはビュー内では一意である必要がありますが、データベース内で一意である必要はありません。 インデックス名は、識別子の規則に従っている必要があります。

ALL

インデックスの種類に関係なく、テーブルまたはビューに関連付けられているすべてのインデックスを指定します。 ALLを指定すると、1 つ以上のインデックスがオフラインまたは読み取り専用のファイル グループ内にある場合、または指定した操作が 1 つ以上のインデックスの種類で許可されていない場合、ステートメントは失敗します。 次の表は、インデックス操作と、許可されないインデックスの種類の一覧です。

キーワード ALL を使用する操作 テーブル内に存在すると操作が失敗するインデックスの種類
REBUILD WITH ONLINE = ON XML インデックス

空間インデックス

列ストア インデックス 1
REBUILD PARTITION = <partition_number> 非パーティション インデックス、XML インデックス、空間インデックス、または無効化されたインデックス
REORGANIZE ALLOW_PAGE_LOCKSが に設定されているインデックスOFF
REORGANIZE PARTITION = <partition_number> 非パーティション インデックス、XML インデックス、空間インデックス、または無効化されたインデックス
IGNORE_DUP_KEY = ON XML インデックス

空間インデックス

列ストア インデックス 1
ONLINE = ON XML インデックス

空間インデックス
列ストア インデックス 1
RESUMABLE = ON 2 再開可能なインデックスは、 ALL キーワードでサポートされていません

1 SQL Server 2012 (11.x) 以降のバージョンおよび Azure SQL Database に適用されます。

2 SQL Server 2017 (14.x) 以降のバージョンと Azure SQL Database に適用されます

PARTITION = <partition_number>でALLを指定する場合は、すべてのインデックスをアラインする必要があります。 つまり、すべてのインデックスは、等価パーティション関数に基づいてパーティション分割されます。 PARTITIONでALLを使用すると、同じ<partition_number>を持つすべてのインデックス パーティションが再構築または再構成されます。 パーティション インデックスの詳細については、「 パーティション テーブルとパーティション インデックスを参照してください。

オンラインで実行できるインデックス操作の詳細については、「オンライン インデックス操作の Guidelinesを参照してください。

database_name

データベースの名前。

schema_name

テーブルまたはビューが属するスキーマの名前です。

table_or_view_name

インデックスに関連付けられているテーブルまたはビューの名前。 オブジェクトに対するインデックスのレポートを表示するには、sys.indexes カタログ ビューを使用します。

SQL Database では、database_nameが現在のデータベースであるか、database_nameがtempdbされ、table_or_view_nameが # で始まる場合に<database_name>.[schema_name].<table_or_view_name> 3 部構成の名前形式がサポートされます。

REBUILD [ WITH ( <rebuild_index_option> [ ,... n ] ) ]

適用対象: SQL Server 2012 (11.x) 以降のバージョンと Azure SQL Database

同じ列、インデックスの種類、一意性属性、並べ替え順序を使用してインデックスを再構築することを指定します。 この句には DBCC DBREINDEX と同じ機能があります。 REBUILD を使用すると、無効化されたインデックスが有効になります。 キーワード ALL を指定しない限り、クラスター化インデックスを再構築しても、関連付けられている非クラスター化インデックスは再構築されません。 インデックス オプションを指定しない場合は、sys.indexes に格納されている既存のインデックス オプション値が適用されます。 値が sys.indexes に格納されていないインデックス オプションについては、オプションの引数に定義されている既定値が適用されます。

ALLが指定され、基になるテーブルがヒープである場合、REBUILD操作はテーブルに影響しません。 テーブルに関連付けられている非クラスター化インデックスは再構築されます。

データベース復旧モデルが一括ログ復旧モデルまたは単純復旧モデルのいずれかに設定されている場合、REBUILD 操作によるログへの記録を最小限にできます。

注意

プライマリ XML インデックスを再構築するとき、基になるユーザー テーブルはインデックス操作の間使用できなくなります。

列ストア インデックスの場合、REBUILD 操作。

  • 並べ替え順序を使用しません。
  • REBUILD が行われている間、テーブルまたはパーティションを排他的にロックします。 データは "オフライン" であり、NOLOCK、読み取りコミットされたスナップショット分離 (RCSI)、またはスナップショット分離 (SI) を使用している場合でも、REBUILD中は使用できません。
  • すべてのデータを列ストアに再圧縮します。 REBUILD が行われている間、列ストア インデックスのコピーが 2 つ存在します。 REBUILD が完了すると、SQL Server により、元の列ストア インデックスが削除されます。

詳細については、「 クエリのパフォーマンスを向上させ、リソースの消費量を減らすためにインデックスのメンテナンスを最適化するを参照してください。

パーティション

インデックスのパーティションを 1 つだけ再構築または再構成することを指定します。 PARTITIONindex_nameがパーティション インデックスでない場合は指定できません。

PARTITION = ALL は、すべてのパーティションを再構築します。

警告

パーティション数が 1,000 を超えるテーブルに対する非アライン インデックスの作成と再構築は可能ですが、サポートされていません。 これを行うと、パフォーマンスが低下したり、これらの操作中に過剰なメモリ消費が発生したりする可能性があります。 パーティションの数が 1,000 個を超えた場合は、固定されたインデックスのみを使用することをお勧めします。

  • partition_number

    再構築または再構成するパーティション インデックスのパーティション番号。 partition_number は変数を参照できる定数式です。 これにはユーザー定義型の変数または関数、およびユーザー定義関数が含まれますが、Transact-SQL ステートメントを参照することはできません。 partition_number は必須であり、指定しないとステートメントは失敗します。

  • WITH ( <single_partition_rebuild_index_option> )

    SORT_IN_TEMPDB、MAXDOP、DATA_COMPRESSION、XML_COMPRESSION は、単一のパーティション (PARTITION = partition_number) の REBUILD を実行するときに指定できるオプションです。 XML インデックスは、単一パーティションの REBUILD 操作では指定できません。

DISABLE

インデックスを無効とマークし、データベース エンジンで使用されないようにします。 どのインデックスも無効にできます。 無効になったインデックスのインデックス定義は、基になるインデックス データがなくてもシステム カタログに残ります。 クラスター化インデックスを無効にすると、基になるテーブル データをユーザーのアクセスができなくなります。 インデックスを有効にするには、ALTER INDEX REBUILD または CREATE INDEX WITH DROP_EXISTING を使用します。 詳細については、「 インデックスと制約の」および 「 インデックスと制約の」を参照してください。

REORGANIZE (行ストア インデックスの再構成)

行ストア インデックスの場合、 REORGANIZE はインデックス のリーフ レベルを再構成するように指定します。 REORGANIZE操作は次のとおりです。

  • 常にオンラインで実行されます。 つまり、ALTER INDEX REORGANIZE トランザクション中は、長期にわたって他をブロックするテーブル ロックは保持されず、基になるテーブルへのクエリまたは更新を続行できます。
  • 無効なインデックスに対しては指定できません。
  • ALLOW_PAGE_LOCKSが OFF に設定されている場合は許可されません。
  • トランザクション内で実行され、トランザクションがロールバックされた場合はロールバックされません。

Note

ALTER INDEX REORGANIZE で、既定の暗黙のトランザクション モードの代わりに、明示的なトランザクション (たとえば、BEGIN TRAN ... COMMIT/ROLLBACK 内の ALTER INDEX) を使用すると、REORGANIZE のロック動作がより制限の厳しいものになり、ブロッキングが発生する可能性があります。 暗黙的なトランザクションの詳細については、「 SET IMPLICIT_TRANSACTIONS」を参照してください。

詳細については、「 クエリのパフォーマンスを向上させ、リソースの消費量を減らすためにインデックスのメンテナンスを最適化するを参照してください。

REORGANIZE WITH ( LOB_COMPACTION = { ON | OFF } )

行ストア インデックスに適用されます。

LOB_COMPACTION = ON

  • これらのラージ オブジェクト (LOB) データ型のデータを含むすべてのページを圧縮するように指定します。型には image、text、ntext、varchar(max)、nvarchar(max)、varbinary(max)、および xml があります。 このデータを圧縮すると、ディスク上のデータ サイズを縮小できます。
  • クラスター化インデックスの場合、テーブルに含まれているすべての LOB 列が圧縮されます。
  • 非クラスター化インデックスの場合、そのインデックス内で非キー列 (付加列) となっているすべての LOB 列が圧縮されます。
  • REORGANIZE ALL を使用すると、すべてのインデックスに対して LOB_COMPACTION が実行されます。 インデックスごとに、クラスター化インデックス内のすべての LOB 列、基になるテーブル、または非クラスター化インデックス内の付加列が圧縮されます。

LOB_COMPACTION = OFF

  • ラージ オブジェクト データを含むページは圧縮されません。
  • OFF はヒープには影響しません。

REORGANIZE (列ストア インデックスの再構成)

列ストア インデックスの場合、 REORGANIZE は、各 CLOSED デルタ行グループを圧縮された行グループとして列ストアに圧縮します。 REORGANIZE 操作は常にオンラインで実行されます。 つまり、ALTER INDEX REORGANIZE トランザクション中は、長期にわたって他をブロックするテーブル ロックは保持されず、基になるテーブルへのクエリまたは更新を続行できます。 詳細については、「 クエリのパフォーマンスを向上させ、リソースの消費量を減らすためにインデックスのメンテナンスを最適化するを参照してください。

  • REORGANIZE は、デルタ行グループ CLOSED 圧縮された行グループに移動するために必要ありません。 バックグラウンドタプルムーバー (TM) プロセスは、デルタ行グループ CLOSED 圧縮するために定期的にウェイクアップします。 タプルムーバーが遅れている場合は、 REORGANIZE を使用することをお勧めします。 REORGANIZE では、行グループをより積極的に圧縮できます。
  • すべての OPEN と CLOSED 行グループを圧縮するには、このセクションの REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS) オプションを参照してください。

SQL Server (SQL Server 2016 (13.x 以降) および Azure SQL Database の列ストア インデックスの場合、 REORGANIZE はオンラインで次の最適化最適化を実行します。

  • 行の 10% 以上が論理的に削除されたとき、行グループから行を物理的に削除します。 削除されたバイトは、物理メディア上で解放されます。 たとえば、100 万行の圧縮された行グループで 100,000 行が削除された場合、SQL Server は削除された行を削除し、900,000 行の行グループを再圧縮します。 削除された行を解放することで、記憶域が節約されます。

  • 1 つまたは複数の圧縮された行グループを結合して、行グループあたりの行数を最大で 1,048,576 行まで増やすことができます。 たとえば、102,400 行の 5 つのバッチを一括インポートすると、5 つの圧縮された行グループが取得されます。 REORGANIZEを実行すると、これらの行グループは、サイズ 512,000 行の 1 つの圧縮された行グループにマージされます。 この処理は、ディクショナリ サイズまたはメモリに関する制限が存在していないことを前提としています。

  • 10% 以上の行が論理的に削除された行グループの場合、SQL Server はこの行グループを 1 つ以上の行グループと結合しようとします。 たとえば、行グループ 1 は 500,000 行の圧縮された行グループであり、行グループ 21 は最大 1,048, 576 行の圧縮された行グループであるとします。 行グループ 21 は行の 60% が削除され、残りが 409,830 行になっています。 SQL Server では、優先的にこの 2 つの行グループが結合されて、909,830 行の新しい行グループが圧縮されます。

REORGANIZE WITH ( COMPRESS_ALL_ROW_GROUPS = { ON | OFF } )

列ストア インデックスに適用されます。

適用対象: SQL Server (開始値 SQL Server 2016 (13.x)) および Azure SQL データベース

COMPRESS_ALL_ROW_GROUPS は、デルタ行グループを列ストアに強制的に OPEN または CLOSED する方法を提供します。 このオプションを使用すると、デルタ行グループを空にするために列ストア インデックスを再構築する必要がありません。 このオプションを他の削除およびマージ最適化機能と組み合わせて使用すれば、ほとんどの状況でインデックスを再構築する必要はなくなります。

  • ON は、サイズと状態 (CLOSED または OPEN) に関係なく、すべての行グループを列ストアに強制的に挿入します。
  • OFF は、すべての CLOSED 行グループを列ストアに強制的に挿入します。

詳細については、「 クエリのパフォーマンスを向上させ、リソースの消費量を減らすためにインデックスのメンテナンスを最適化するを参照してください。

SET ( <set_index オプション> [ ,... n ] )

インデックスを再構築または再構成しないでインデックス オプションを指定します。 SET 無効なインデックスには指定できません。

PAD_INDEX = { ON | OFF }

インデックスの埋め込みを指定します。 既定値は、OFF です。

  • ON

    FILLFACTOR で指定される空き領域のパーセンテージが、インデックスの中間レベルのページに適用されます。 PAD_INDEXがONに設定FILLFACTOR同時に指定されていない場合は、sys.indexes に格納されている fill factor 値が使用されます。

  • OFF または fillfactor は指定されません

    中間レベルのページは、ほぼ全容量が使用されます。 この場合、中間ページのキーのセットに基づき、インデックスに割り当てることのできる 1 行以上の最大サイズが収まる分の領域は残されます。

詳細については、「CREATE INDEX」を参照してください。

FILLFACTOR = fillfactor

インデックスの作成時または変更時に、データベース エンジン が各インデックス ページのリーフ レベルをどの程度まで埋めるかを、パーセント値で指定します。 fillfactor の値は、1 から 100 の整数値にする必要があります。 既定値は 0 です。 FILL FACTOR 値 0 と 100 の機能は、まったく同じです。

明示的な FILLFACTOR の設定は、インデックスの初回作成時または再構築時にのみ適用されます。 データベース エンジンでは、ページ内で指定されたパーセント分の空き領域は動的に保持されません。 詳細については、「CREATE INDEX」を参照してください。

FILL FACTOR の設定を表示するには、sys.indexes で fill_factor を使用します。

重要

データベース エンジンではクラスター化インデックスの作成時にデータが再分配されるため、FILLFACTOR 値を使用してクラスター化インデックスを作成または変更すると、データ用のストレージ領域のサイズが影響を受けます。

SORT_IN_TEMPDB = { ON | OFF }

並べ替え結果を tempdbに格納するかどうかを指定します。 既定値は、Azure SQL Database Hyperscale を除く OFF です。 Hyperscale のインデックス作成操作についてはすべて、再開可能なインデックス リビルドが使用されていない限り、SORT_IN_TEMPDB は常に ON になります。

  • ON

    インデックスの作成に使用される中間の並べ替え結果は、 tempdbに格納されます。 tempdbがユーザー データベースとは異なるディスク セット上にある場合、インデックスの作成に必要な時間が短縮される可能性があります。 インデックスの構築中に使用されるディスク領域のサイズは増加します。

  • OFF

    中間の並べ替え結果はインデックスと同じデータベースに格納されます。

並べ替え操作が必要ない場合、または並べ替えをメモリ内で実行できる場合、SORT_IN_TEMPDB オプションは無視されます。

詳細については、「インデックスの SORT_IN_TEMPDB オプション」を参照してください。

IGNORE_DUP_KEY = { ON | OFF }

挿入操作で、一意のインデックスに重複するキー値を挿入しようとした場合のエラー応答を指定します。 IGNORE_DUP_KEY オプションは、インデックスが作成または再構築された後の挿入操作のみに適用されます。 既定値は、OFF です。

  • ON

    重複したキー値が一意のインデックスに挿入されると、警告メッセージが表示されます。 一意性制約に違反している行のみが失敗します。

  • OFF

    重複したキー値が一意のインデックスに挿入されると、エラー メッセージが表示されます。 INSERT操作全体がロールバックされます。

IGNORE_DUP_KEY ビュー、一意でないインデックス、XML インデックス、空間インデックス、フィルター選択されたインデックスに対して、 ON に設定することはできません。

IGNORE_DUP_KEY を表示するには、sys.indexes を使用します。

下位互換性のある構文では、WITH IGNORE_DUP_KEY は WITH IGNORE_DUP_KEY = ON と等価です。

STATISTICS_NORECOMPUTE = { ON | OFF }

指定したインデックスに関連する統計の統計の自動更新オプション ( AUTO_STATISTICS_UPDATE) を無効または有効にします。 既定値は、OFF です。

  • ON

    統計の自動更新は、インデックスの再構築後に無効になります。

  • OFF

    統計の自動更新は、インデックスの再構築後に有効になります。

統計の自動更新を復元するには、STATISTICS_NORECOMPUTEをOFFに設定するか、NORECOMPUTE句を使用せずにUPDATE STATISTICSを実行します。

警告

統計の自動更新を無効にすると、クエリ オプティマイザーがテーブルを含むクエリの最適な実行プランを選択できなくなる可能性があります。 このオプションは、資格のあるデータベース管理者のみが慎重に使用する必要があります。

この設定では、再構築操作中にインデックス関連の統計のフルスキャンによる自動更新が禁止されることはありません。

STATISTICS_INCREMENTAL = { ON | OFF }

適用対象: SQL Server 2014 (12.x) 以降のバージョン、および Azure SQL Database

ONすると、作成される統計はパーティションごとの統計になります。 OFFすると、統計ツリーが削除され、SQL Server によって統計が再計算されます。 既定値は、OFF です。

パーティションごとの統計がサポートされていない場合、このオプションは無視され、警告が生成されます。 次の種類の統計では、増分統計がサポートされていません。

  • ベース テーブルにパーティションで固定されていないインデックスを使用して作成された統計
  • 可用性グループの読み取り可能なセカンダリ データベースに対して作成された統計
  • 読み取り専用のデータベースに対して作成された統計
  • フィルター選択されたインデックスに対して作成された統計
  • ビューに対して作成された統計
  • 内部テーブルに対して作成された統計
  • 空間インデックスまたは XML インデックスを使用して作成された統計

ONLINE = { ON | OFF } <rebuild_index_option に適用する場合>

インデックス操作時に、基になるテーブルや関連するインデックスをクエリやデータ変更で使用できるかどうかを指定します。 既定値は、OFF です。

XML インデックスまたは空間インデックスの場合は、 ONLINE = OFF のみがサポートされ、 ONLINE が ON に設定されている場合はエラーが発生します。

重要

オンラインでのインデックス操作は、Microsoft SQL Server のすべてのエディションで使用できるわけではありません。 SQL Server の各エディションでサポートされる機能の一覧については、「SQL Server 2022 の各エディションとサポートされている機能」を参照してください。

  • ON

    長期のテーブル ロックは、インデックス操作中は保持されません。 インデックス操作の主なフェーズの間は、基になるテーブル上に、インテント共有 (IS) ロックのみが保持されます。 これによって、基になるテーブルおよびインデックスに対してクエリや更新を続けることができます。 操作の開始時に、共有 (S) ロックがソース オブジェクトに短時間保持されます。 非クラスター化インデックスが作成されている場合、操作の終了時に、ソースに S ロックが短時間保持されます。 スキーマ変更 (Sch-M) ロックは、クラスター化インデックスがオンラインで作成または削除されたとき、およびクラスター化インデックスまたは非クラスター化インデックスが再構築されるときに取得されます。 ONLINE は、ローカル一時テーブルでインデックスを作成するときに ON に設定することはできません。

  • OFF

    テーブル ロックはインデックス操作中に適用されます。 オフラインのインデックス操作で、クラスター化インデックス、空間インデックス、XML インデックスの作成、再構築、削除を行う場合や、非クラスター化インデックスの再構築、削除を行う場合は、テーブルで Sch-M (スキーマ修正) ロックが取得されます。 このため、操作中はすべてのユーザーは基になるテーブルにアクセスできません。 非クラスター化インデックスを作成するオフライン インデックス操作では、テーブルの共有 (S) ロックが取得されます。 これにより、基になるテーブルの更新は防止されますが、 SELECT ステートメントなどの読み取り操作が許可されます。

詳細については、「オンラインでのパフォーマンス インデックス操作を参照してください。

インデックスは、グローバル一時テーブル上のインデックスを含めてオンラインでリビルドできます。ただし次の場合は例外です。

  • XML インデックス
  • ローカル一時テーブルのインデックス
  • ビューの最初の一意クラスター化インデックス
  • 列ストア インデックス
  • 基になるテーブルに LOB データ型 (image、ntext、text) および空間データ型が含まれる場合のクラスター化インデックス。
  • varchar(max) および varbinary(max) 列は、インデックスの一部にすることはできません。 SQL Server (SQL Server 2012 (11.x) 以降) および Azure SQL データベース では、テーブルに varchar(max) 列または varbinary(max) 列が含まれている場合、他の列を含むクラスター化インデックスは、ONLINE オプションを使用して作成または再作成できます。 ベース テーブルに ONLINEvarchar(max) または varbinary(max) 列が含まれている場合、Azure SQL Database では オプションが許可されません

詳細については、「オンライン インデックス操作の動作原理」を参照してください。

次の XEvent は、ALTER TABLE ... SWITCH PARTITION とオンライン インデックス リビルドに関連しています。

  • lock_request_priority_state
  • process_killed_by_abort_blockers
  • ddl_with_wait_at_low_priority

オンライン インデックス操作用の既存の XEvent progress_report_online_index_operation には、partition_number と partition_id が含まれます。

RESUMABLE = { ON | OFF}

適用対象: SQL Server 2017 (14.x) 以降のバージョンと Azure SQL Database

オンラインでのインデックス操作が再開可能かどうかを指定します。

  • ON

    インデックス操作は再開可能です。

  • OFF

    インデックス操作は再開可能ではありません。

MAX_DURATION = time [ MINUTES ] を RESUMABLE = ON と共に使用します ( ONLINE = ONが必要)

適用対象: SQL Server 2017 (14.x) 以降のバージョンと Azure SQL Database

再開可能なオンラインでのインデックス操作が、一時停止までに実行される時間 (分単位で指定する整数値) を示します。

重要

オンラインで実行できるインデックス操作の詳細については、「オンライン インデックス操作の Guidelinesを参照してください。

Note

再開可能なオンライン インデックス再構築は、列ストア インデックスではサポートされていません。

ALLOW_ROW_LOCKS = { ON | OFF }

行ロックを許可するかどうかを指定します。 既定値は、ON です。

  • ON

    インデックスにアクセスするとき、行ロックが許可されます。 いつ行ロックを使用するかは、データベース エンジンによって決定されます。

  • OFF

    行ロックは使用されません。

ALLOW_PAGE_LOCKS = { ON | OFF }

ページ ロックを許可するかどうかを指定します。 既定値は、ON です。

  • ON

    インデックスにアクセスするとき、ページ ロックが許可されます。 いつページ ロックを使用するかは、データベース エンジン によって決定されます。

  • OFF

    ページ ロックは使用されません。

Note

ALLOW_PAGE_LOCKSが OFF に設定されている場合、インデックスを再構成することはできません。

OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }

適用対象: SQL Server 2019 (15.x) 以降のバージョンと Azure SQL Database

最終ページ挿入競合に対して最適化するかどうかを指定します。 既定値は、OFF です。 詳細については、「シーケンシャル キー」を参照してください。

MAXDOP = max_degree_of_parallelism

インデックス操作中に max degree of parallelism 構成オプションをオーバーライドします。 詳細については、「 並列処理の最大限度の構成 (サーバー構成オプション)」を参照してください。 並列プランの実行で使用されるプロセッサの数を制限するには、 MAXDOP を使用します。 最大数は 64 プロセッサです。

重要

MAXDOP オプションはすべての XML インデックスで構文的にサポートされていますが、空間インデックスまたはプライマリ XML インデックスの場合、ALTER INDEXは現在、1 つのプロセッサのみを使用しています。

max_degree_of_parallelism は次のように指定できます。

  • 1: 並列プランの生成を抑制します。
  • >1: 並列インデックス操作で使用されるプロセッサの最大数を、指定した数に制限します。
  • 0 (既定値): 現在のシステム ワークロードに基づいて、プロセッサの実際の数以下を使用します。

詳細については、「 並列インデックス操作の構成」を参照してください。

Note

並列インデックス操作は、SQL Server のすべてのエディションで使用できるわけではありません。 SQL Server の各エディションでサポートされる機能の一覧については、「SQL Server 2022 の各エディションとサポートされている機能」を参照してください。

COMPRESSION_DELAY = { 0 | duration [ minutes ] }

適用対象: SQL Server (SQL Server 2016 (13.x) 以降)

ディスク ベーステーブルの場合、遅延は、SQL Server が圧縮された行グループに圧縮する前に、 CLOSED 状態のデルタ行グループがデルタ行グループに残る必要がある最小分数を指定します。 ディスク ベースのテーブルでは、個々の行の挿入と更新の時間が追跡されないため、SQL Server は、 CLOSED 状態のデルタ行グループに遅延を適用します。

既定値は、0 分です。

COMPRESSION_DELAYを使用するタイミングに関する推奨事項については、「Get started with Columnstore for real-time operational analytics」を参照してください。

DATA_COMPRESSION

指定したインデックス、パーティション番号、またはパーティション範囲に、データ圧縮オプションを指定します。 次のようなオプションがあります。

  • NONE

    インデックスまたは指定したパーティションが圧縮されません。 これは、列ストア インデックスには適用されません。

  • ROW

    行の圧縮を使用して、インデックスまたは指定したパーティションが圧縮されます。 これは、列ストア インデックスには適用されません。

  • PAGE

    ページの圧縮を使用して、インデックスまたは指定したパーティションが圧縮されます。 これは、列ストア インデックスには適用されません。

  • COLUMNSTORE

    適用対象: SQL Server 2014 (12.x) 以降のバージョン、および Azure SQL Database

    非クラスター化列ストアとクラスター化列ストア インデックスの両方を含む列ストア インデックスにのみ適用されます。 COLUMNSTORE は、 COLUMNSTORE_ARCHIVE オプションで圧縮されたインデックスまたは指定されたパーティションを展開することを指定します。 データが復元されると、すべての列ストア インデックスに使用される列ストア圧縮で引き続き圧縮されます。

  • COLUMNSTORE_ARCHIVE

    適用対象: SQL Server 2014 (12.x) 以降のバージョン、および Azure SQL Database

    非クラスター化列ストアとクラスター化列ストア インデックスの両方を含む列ストア インデックスにのみ適用されます。 COLUMNSTORE_ARCHIVE 指定したパーティションをさらに小さいサイズに圧縮します。 これは、アーカイブ用や、ストレージのサイズを減らす必要があり、かつ保存と取得に時間をかける余裕があるその他の状況で使用できます。

圧縮の詳細については、「データ圧縮」を参照してください。

XML_COMPRESSION

適用対象: SQL Server 2022 (16.x) 以降のバージョン、Azure SQL データベース、および Azure SQL Managed Instance。

1 つ以上の xml データ型列を含む、指定したインデックスの XML 圧縮オプションを指定します。 次のようなオプションがあります。

  • ON

    XML 圧縮を使用して、インデックスまたは指定したパーティションが圧縮されます。

  • OFF

    インデックスまたは指定したパーティションが圧縮されません。

ON PARTITIONS ( { <partition_number_expression> | <range> } [ ,...n ] )

DATA_COMPRESSION または XML_COMPRESSION の設定が適用されるパーティションを指定します。 インデックスがパーティション分割されていない場合、 ON PARTITIONS 引数はエラーを生成します。 ON PARTITIONS 句を指定しないと、パーティション インデックスのすべてのパーティションに、DATA_COMPRESSION または XML_COMPRESSION オプションが適用されます。

<partition_number_expression> は以下の方法で指定できます。

  • パーティション番号を指定します (例: ON PARTITIONS (2))。

  • コンマで区切った複数の個別のパーティションのパーティション番号を指定します (例: ON PARTITIONS (1, 5))。

  • 次のように範囲と個別のパーティションの両方を指定します: ON PARTITIONS (2, 4, 6 TO 8)。

<range> はパーティション番号として、TO で区切って指定できます。たとえば、ON PARTITIONS (6 TO 8) のようになります。

さまざまなパーティションにさまざまな種類のデータ圧縮を設定するには、DATA_COMPRESSION オプションを複数回指定します。例:

REBUILD WITH
(
DATA_COMPRESSION = NONE ON PARTITIONS (1),
DATA_COMPRESSION = ROW ON PARTITIONS (2, 4, 6 TO 8),
DATA_COMPRESSION = PAGE ON PARTITIONS (3, 5)
);

また、XML_COMPRESSION オプションを 1 回以上指定できます。次に例を示します。

REBUILD WITH
(
  XML_COMPRESSION = OFF ON PARTITIONS (1),
  XML_COMPRESSION = ON ON PARTITIONS (2, 4, 6 TO 8),
  XML_COMPRESSION = OFF ON PARTITIONS (3, 5)
);

ONLINE = { ON | OFF } <single_partition_rebuild_index_option に適用する場合>

インデックスまたは基になるテーブルのインデックス パーティションをオンラインまたはオフラインで再構築できるかどうかを指定します。 REBUILD ... ONLINE = ON が実行されている場合、このテーブルのデータは、インデックス操作中にクエリやデータ変更で使用できます。 既定値は、OFF です。

  • ON

    長期のテーブル ロックは、インデックス操作中は保持されません。 インデックス操作の主なフェーズの間は、基になるテーブル上に、インテント共有 (IS) ロックのみが保持されます。 インデックスの再構築を開始するときにテーブルに対するスキーマ安定度 (Sch-S) ロックが必要であり、オンライン インデックス再構築を終了するときにテーブルに対するスキーマ修正 (Sch-M) ロックが必要です。 どちらのメタデータ ロックも期間は短くなりますが、特に Sch-M ロックでは、ブロックしているすべてのトランザクションの完了を待機する必要があります。 待機中、Sch-M ロックによって、同じテーブルにアクセスするためにこのロックの後に待機している他のすべてのトランザクションがブロックされます。

    Note

    オンライン インデックスの再構築では、low_priority_lock_wait オプションを設定できます。「オンライン インデックス操作での WAIT_AT_LOW_PRIORITY」を参照してください。

  • OFF

    テーブル ロックはインデックス操作中に適用されます。 このため、操作中はすべてのユーザーは基になるテーブルにアクセスできません。

RESUME

適用対象: SQL Server 2017 (14.x) 以降のバージョンと Azure SQL Database

手動で一時停止、またはエラーのために一時停止されたインデックス操作を再開します。

  • MAX_DURATION は RESUMABLE = ON と共に使用されます

    再開可能なオンラインでのインデックス操作が、再開後に実行される時間 (分単位で指定する整数値) を示します。 その時間が経過すると、再開可能な操作はまだ実行中であっても一時停止されます。

  • WAIT_AT_LOW_PRIORITYRESUMABLE = ONとONLINE = ONで使用されます。

    一時停止の後でオンラインでのインデックス再構築を再開するには、このテーブルに対する操作がブロックされるまで待機する必要があります。 WAIT_AT_LOW_PRIORITY は、オンライン インデックス再構築操作が優先順位の低いロックを待機し、オンライン インデックスのビルド操作が待機している間に他の操作を続行することを示します。 WAIT_AT_LOW_PRIORITY オプションを省略すると、WAIT_AT_LOW_PRIORITY (MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE) と同等になります。 詳細については、「WAIT_AT_LOW_PRIORITY」を参照してください。

PAUSE

適用対象: SQL Server 2017 (14.x) 以降のバージョンと Azure SQL Database

再開可能なオンラインでのインデックス再構築操作を一時停止します。

ABORT

適用対象: SQL Server 2017 (14.x) 以降のバージョンと Azure SQL Database

再開可能として宣言されている実行中または一時停止中のインデックス操作を中止します。 再開可能なインデックス再構築操作を終了するには、ABORT コマンドを明示的に実行する必要があります。 障害が発生しても再開可能なインデックス操作を一時停止しても、操作の実行は終了しません。操作は無期限の一時停止状態のままとなります。

解説

インデックスをパーティションに再分割するか別のファイル グループに移動する場合、ALTER INDEX は使用できません。 このステートメントは、列の追加や削除、または列の順序変更など、インデックス定義の変更には使用できません。 これらの操作は、CREATE INDEX と DROP_EXISTING 句を使用して実行します。

オプションを明示的に指定しない場合は、現在の設定が適用されます。 たとえば、 FILLFACTOR 設定が REBUILD 句で指定されていない場合、システム カタログに格納されている fill factor 値は再構築プロセス中に使用されます。 現在のインデックス オプション設定を表示するには、sys.indexes を使用します。

ONLINE、MAXDOP、SORT_IN_TEMPDB の値はシステム カタログに格納されません。 インデックス ステートメントでオプション値を指定しない限り、各オプションの既定値が使用されます。

マルチプロセッサ コンピューター上では、ALTER INDEX REBUILD は他のクエリと同様、自動的に使用プロセッサの数を増やしてインデックスの変更に関連するスキャンや並べ替え操作を実行します。 LOB_COMPACTION の有無に関係なく、ALTER INDEX REORGANIZE を実行すると、max degree of parallelism の値は単一スレッドの操作になります。 詳細については、「 並列インデックス操作の構成」を参照してください。

Microsoft Fabric の SQL データベースでは、 ALTER INDEX ALL はサポートされていませんが、 ALTER INDEX <index name> 。

重要

インデックスが配置されているファイル グループがオフラインであるか、読み取り専用に設定されている場合、インデックスを再構成または再構築することはできません。 キーワード ALL を指定した場合で、1 つ以上のインデックスがオフラインまたは読み取り専用のファイル グループにある場合、ステートメントは失敗します。

インデックスの再構築

インデックスの再構築では、インデックスを削除し再作成します。 この操作では、断片化をなくし、指定されているか既に存在する FILL FACTOR 設定に基づいてページを圧縮することによりディスク領域を取り戻した後、連続するページにインデックス行を再び並べ替えます。 ALL を指定した場合、テーブル上のすべてのインデックスが、1 回のトランザクションで削除され再構築されます。 外部キー制約は、事前に削除しておく必要はありません。 128 以上のエクステントがあるインデックスを再構築すると、データベース エンジンでは、トランザクションがコミットされるまで実際のページの割り当て解除とそれに関連するロックが延期されます。

詳細については、「 クエリのパフォーマンスを向上させ、リソースの消費量を減らすためにインデックスのメンテナンスを最適化するを参照してください。

インデックスの再構成

インデックスの再構成では、最小のシステム リソースが使用されます。 この操作では、リーフ レベル ページをリーフ ノードの論理順序 (左から右) に合わせて物理的に並べ替えることにより、テーブルやビュー上にあるクラスター化および非クラスター化インデックスのリーフ レベルをデフラグします。 再構成でも、インデックス ページは圧縮されます。 圧縮は既存の FILL FACTOR 値に基づいて行われます。

ALL を指定した場合、テーブル上のクラスター化と非クラスター化の両方のリレーショナル インデックスと XML インデックスが再構成されます。 ALL を指定した場合は、いくつかの制限が適用されます。本記事の「引数」セクションの ALL の定義を参照してください。

詳細については、「 クエリのパフォーマンスを向上させ、リソースの消費量を減らすためにインデックスのメンテナンスを最適化するを参照してください。

重要

順序付けされたクラスター化列ストア インデックスを持つ Azure Synapse Analytics テーブルの場合、 ALTER INDEX REORGANIZE はデータを再ソートしません。 データを再度並べ替えるには ALTER INDEX REBUILD を使用します。

インデックスの無効化

インデックスを無効化すると、ユーザーはインデックスにアクセスできなくなり、クラスター化インデックスの場合は基になるテーブル データにもアクセスできなくなります。 インデックス定義はシステム カタログに残ります。 ビュー上で非クラスター化インデックスまたはクラスター化インデックスを物理的に無効にすると、インデックス データが削除されます。 クラスター化インデックスを無効にすると、データにアクセスできなくなりますが、データはインデックスが削除または再構築されるまで B ツリーに残ります。このデータは管理されません。 有効化または無効化されたインデックスの状態を表示するには、is_disabled カタログ ビューの sys.indexes 列にクエリを実行します。

Note

ドキュメントでは、一般的にインデックスに関して B ツリーという用語が使用されます。 行ストア インデックスで、データベース エンジンによって B+ ツリーが実装されます。 これは、列ストア インデックスやメモリ最適化テーブルのインデックスには適用されません。 詳細については、「SQL Server と Azure SQL のインデックスのアーキテクチャとデザイン ガイド」を参照してください。

テーブルがトランザクション レプリケーション パブリケーション内にある場合、主キー列に関連付けられているインデックスを無効にすることはできません。 これらのインデックスはレプリケーションで必要です。 インデックスを無効にするには、まずパブリケーションからテーブルを削除する必要があります。 詳細については、「Publish Data and Database Objects」(データとデータベース オブジェクトのパブリッシュ) をご覧ください。

インデックスを有効にするには、ALTER INDEX REBUILD ステートメントまたは CREATE INDEX WITH DROP_EXISTING ステートメントを使用します。 無効なクラスター化インデックスの再構築は、 ONLINE オプションを ON に設定して実行することはできません。 詳細については、「 可能なインデックスと制約を参照してください。

オプションを設定する

指定したインデックスの ALLOW_ROW_LOCKS、 ALLOW_PAGE_LOCKS、 OPTIMIZE_FOR_SEQUENTIAL_KEY、 IGNORE_DUP_KEY、および STATISTICS_NORECOMPUTE のオプションを、そのインデックスを再構築または再構成せずに設定できます。 変更された値はすぐにインデックスに適用されます。 これらの設定を表示するには、sys.indexes を使用します。 詳細については、「 インデックス オプションの設定」を参照してください。

行およびページ ロック オプション

ALLOW_ROW_LOCKS = ON と ALLOW_PAGE_LOCK = ON の場合、インデックスにアクセスするとき、行レベル、ページ レベル、テーブル レベルのロックが許可されます。 データベース エンジンは適切なロックを選択し、行ロックまたはページ ロックをテーブル ロックにエスカレートすることができます。

ALLOW_ROW_LOCKS = OFF と ALLOW_PAGE_LOCK = OFF の場合、インデックスにアクセスするとき、テーブル レベルのロックのみが許可されます。

行またはページ ロック オプションが設定されている場合に ALL を指定すると、この設定はすべてのインデックスに適用されます。 基になるテーブルがヒープの場合、この設定は次のように適用されます。

オプション 詳細
ALLOW_ROW_LOCKS = ON or OFF ヒープおよび関連する非クラスター化インデックスに適用。
ALLOW_PAGE_LOCKS = ON ヒープおよび関連する非クラスター化インデックスに適用。
ALLOW_PAGE_LOCKS = OFF 非クラスター化インデックスに完全に適用。 これは、非クラスター化インデックスではすべてのページ ロックが許可されないことを意味します。 ヒープでは、ページに対して共有 (S)、更新 (U) および排他 (X) ロックだけが許可されなくなります。 データベース エンジンでは内部目的用にインテント ページ ロック (IS、IU、または IX) を引き続き取得できます。

オンライン インデックス操作

インデックスを再構築し、 ONLINE オプションを ON に設定すると、基になるオブジェクト、テーブル、および関連するインデックスをクエリやデータの変更に使用できます。 1 つのパーティションに存在するインデックスの一部をオンラインで再構築することもできます。 排他テーブル ロックは、変更プロセス中に短時間だけ保持されます。

インデックスの再構成は、常にオンラインで実行されます。 このプロセスではロックが長期間保持されないので、実行中のクエリや更新はブロックされません。

同じテーブルまたはテーブル パーティションに対して同時オンライン インデックス操作を実行できるのは、次の操作を実行する場合のみです。

  • 複数の非クラスター化インデックスを作成する。
  • 同じテーブルで異なるインデックスを再構成する。
  • 同じテーブルで重複しないインデックスを再構築する間、別のインデックスを再構成する。

その他すべてのオンライン インデックス操作は、同時に実行しようとしても失敗します。 たとえば、同じテーブル上で同時に複数のインデックスを再構築したり、同じテーブルで既存のインデックスを再構築する間に新しいインデックスを作成することはできません。

詳細については、「オンラインでのパフォーマンス インデックス操作を参照してください。

再開可能なインデックス操作

適用対象: SQL Server 2017 (14.x) 以降のバージョンと Azure SQL Database

オンラインでのインデックス再構築は、RESUMABLE = ON オプションを使用し、再開可能として指定できます。

  • RESUMABLE オプションは特定のインデックス用のメタデータ内に保持されるのではなく、現在の DDL ステートメントの実行中にのみ適用されます。 したがって、再開機能を有効にするには、RESUMABLE = ON 句を明示的に指定する必要があります。

  • MAX_DURATION オプションは、RESUMABLE = ON オプションまたは low_priority_lock_wait オプションに対してサポートされています。

    • MAX_DURATION for RESUMABLE オプションは、再構築するインデックスの時間間隔を指定します。 この時間を使用すると、インデックスの再構築が一時停止されるか、実行が完了します。 ユーザーは、一時停止されたインデックスの再構築を再開可能にするタイミングを決定します。 MAX_DURATION の time (分単位) は、0 分より長く、かつ 1 週間 (7 * 24 * 60 = 10080 分) 以下とする必要があります。 インデックス操作を長時間一時停止すると、インデックス (元のテーブルと新しく作成されたもの) の両方にディスク領域が必要であり、DML 操作中に更新する必要があるため、特定のテーブルの DML パフォーマンスとデータベース ディスク容量に影響する可能性があります。 MAX_DURATIONオプションを省略した場合、インデックス操作は完了するまで、またはエラーが発生するまで続行されます。
    • low_priority_lock_wait 引数オプションを使用すると、Sch-M ロックでブロックされたときにインデックス操作を続行する方法を決定することができます。

  • 元の ALTER INDEX REBUILD ステートメントを同じパラメーターで再実行すると、一時停止中のインデックス再構築操作が再開されます。 ALTER INDEX RESUME ステートメントを実行して、一時停止中のインデックス再構築操作を再開することもできます。

  • SORT_IN_TEMPDB = ON オプションは、再開可能なインデックスに対してはサポートされていません

  • RESUMABLE = ON を指定した DDL コマンドを、明示的なトランザクション内で実行することはできません (BEGIN TRAN ... COMMIT ブロックの一部にすることはできません)。

  • 一時停止されているインデックス操作のみが再開可能です。

  • 一時停止しているインデックス操作を再開するときに、 MAXDOP の値を新しい値に変更できます。 一時停止中のインデックス操作を再開するときに MAXDOP が指定されていない場合は、最後の MAXDOP 値が取得されます。 インデックスの再構築操作に MAXDOP オプションがまったく指定されていない場合は、既定値が使用されます。

  • インデックス操作を直ちに一時停止するには、進行中のコマンドを停止するか (Ctrl + C キー)、ALTER INDEX PAUSE コマンドまたは KILL <session_id> コマンドを実行します。 コマンドを一時停止すると、 RESUME オプションを使用して再開できます。

  • ABORT コマンドを実行すると、元のインデックス再構築をホストしたセッションが強制終了されて、インデックス操作が中止されます

  • 再開可能なインデックス再構築には追加のリソースは必要ありません。ただし、次の場合は例外です。

    • インデックスの作成を維持するために必要な余分な領域 (インデックスが一時停止されている時間を含む)
    • いかなる DDL 変更も阻止する DDL 状態

  • ゴースト クリーンアップはインデックスの一時停止フェーズ中に実行されますが、インデックスの実行中は一時停止されます。 再開可能なインデックス再構築操作に対して次の機能は無効になります。

    • RESUMABLE = ON では、無効になっているインデックスの再構築はサポートされていません
    • ALTER INDEX REBUILD ALL コマンド
    • インデックス再構築を使用する ALTER TABLE
    • RESUMABLE = ON を指定した DDL コマンドを、明示的なトランザクション内で実行することはできません (BEGIN TRAN ... COMMIT ブロックの一部にすることはできません)
    • 計算列または TIMESTAMP 列をキー列として含むインデックスを再構築します。

  • ベース テーブルに LOB 列が含まれている場合、再開可能なクラスター化インデックスの再構築には、この操作の開始時に Sch-M ロックが必要です

Note

DDL コマンドは、完了するか、一時停止するか、または失敗するまで実行されます。 コマンドが一時停止した場合、操作が一時停止され、インデックスの作成が完了しなかったことを示すエラーが発行されます。 現在のインデックスの状態の詳細については、sys.index_resumable_operations を参照してください。 前と同様に、障害が発生した場合はエラーも発行されます。

オンライン インデックス操作での WAIT_AT_LOW_PRIORITY

適用対象: SQL Server 2014 (12.x) 以降のバージョン、および Azure SQL Database

low_priority_lock_wait 構文を使用すると、WAIT_AT_LOW_PRIORITY 動作を指定できます。 WAIT_AT_LOW_PRIORITY は、ONLINE = ON でのみ使用できます。

オンライン インデックス再構築の DDL ステートメントを実行するには、特定のテーブルで実行されているすべてのアクティブなブロック トランザクションが完了する必要があります。 オンライン インデックス再構築を実行すると、このテーブルに対する実行の開始が準備できているすべての新しいトランザクションがブロックされます。 オンライン インデックス再構築のロックの期間は短いですが、特定のテーブルで開いているすべてのトランザクションが完了するのを待機し、新しいトランザクションの開始をブロックすると、スループットに大きな影響を与え、ワークロードの速度低下やタイムアウトが発生し、基になるテーブルへのアクセスが大幅に制限される可能性があります。

WAIT_AT_LOW_PRIORITY オプションを使用すると、DBA は、オンライン インデックス再構築に必要なスキーマ安定度 (Sch-S) ロックとスキーマ修正 (Sch-M) ロックを管理でき、2 つのオプションのいずれかを選択できます。 いずれのケースでも、待機時間 MAX_DURATION = n [minutes] 中にブロックするアクティビティがない場合は、オンライン インデックス再構築が待機なしで直ちに実行され、DDL ステートメントが完了します。

WAIT_AT_LOW_PRIORITY は、オンライン インデックス再構築操作が優先順位の低いロックを待機し、オンライン インデックスのビルド操作が待機している間に他の操作を続行することを示します。 WAIT AT LOW PRIORITY オプションを省略すると、WAIT_AT_LOW_PRIORITY (MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE) と同等になります。

MAX_DURATION = time [ MINUTES ]

DDL コマンドの実行時に、オンライン インデックス再構築によってロックが低い優先順位で待機する待機時間 (分単位で指定された整数値)。 操作が MAX_DURATION 時間ブロックされている場合は、指定した ABORT_AFTER_WAIT アクションが実行されます。 MAX_DURATION time は常に分単位で、 MINUTES という単語は省略できます。

ABORT_AFTER_WAIT = [ NONE |SELF |BLOCKERS ]

  • NONE

    通常の (標準) 優先度のロックを待機し続けます。

  • SELF

    いずれのアクションも行わずに、現在実行中のオンライン インデックス再構築の DDL 操作を終了します。 SELFオプションは、MAX_DURATION 0 では使用できません。

  • BLOCKERS

    オンライン インデックス再構築の DDL 操作をブロックしているすべてのユーザー トランザクションを強制終了して、操作を続行できるようにします。 BLOCKERS オプションを使用するには、ログインに ALTER ANY CONNECTION 権限が必要です。

空間インデックスに関する制限

空間インデックスを再構築するとき、基になるユーザー テーブルがインデックス操作中に使用できなくなります。これは、空間インデックスでスキーマ ロックが保持されるためです。

ユーザー テーブルの PRIMARY KEY 制約は、そのテーブルの列に空間インデックスが定義されている間は変更できません。 PRIMARY KEY制約を変更するには、まずテーブルのすべての空間インデックスを削除します。 PRIMARY KEY制約を変更した後、各空間インデックスを再作成できます。

単一パーティションの再構築操作では、空間インデックスを指定できません。 ただし、パーティションの完全な再構築では、空間インデックスを指定できます。

BOUNDING_BOX や GRID など、空間インデックス固有のオプションを変更するには、DROP_EXISTING = ON を指定する CREATE SPATIAL INDEX ステートメントを使用するか、空間インデックスを削除して新しく作成します。 例については、「CREATE SPATIAL INDEX」を参照してください。

データ圧縮

データ圧縮の詳細については、「 Data 圧縮」を参照してください。

PAGEとROW圧縮の変更がテーブル、インデックス、またはパーティションに与える影響を評価するには、sp_estimate_data_compression_savings ストアド プロシージャを使用します。

パーティション インデックスには次の制限が適用されます。

  • ALTER INDEX ALL ... を使用しても、固定されていないインデックスがテーブルにあると、単一パーティションの圧縮の設定を変更できません。
  • ALTER INDEX <index> ... REBUILD PARTITION ... 構文は、そのインデックスの指定のパーティションを再構築します。
  • ALTER INDEX <index> ... REBUILD WITH ... 構文は、そのインデックスのすべてのパーティションを再構築します。

統計

テーブルで ALTER INDEX ALL ... を実行すると、インデックスに関連付けられた統計のみが更新されます。 (インデックスではなく) テーブルで作成されている自動または手動の統計は更新されません。

アクセス許可

ALTER INDEX を実行するには、少なくとも、テーブルまたはビューの ALTER 権限が必要です。

バージョンに関するメモ

  • SQL Database では、filegroup および filestream オプションは使用されません。
  • 列ストア インデックスは、SQL Server 2012 (11.x) より前には使用できません。
  • 再開可能なインデックス操作は、SQL Server 2017 (14.x) および Azure SQL データベース 以降のバージョンで使用できます。

基本構文例

ALTER INDEX index1 ON table1 REBUILD;
ALTER INDEX ALL ON table1 REBUILD;
ALTER INDEX ALL ON dbo.table1 REBUILD;

例: 列ストア インデックス

これらの例は、列ストア インデックスに適用されます。

A. REORGANIZE のデモ

この例では、ALTER INDEX REORGANIZE コマンドの動作方法を示します。 複数の行グループを含むテーブルを作成し、REORGANIZE で行グループを結合する方法を示します。

-- Create a database
CREATE DATABASE [columnstore];
GO

-- Create a rowstore staging table
CREATE TABLE [staging] (
    AccountKey INT NOT NULL,
    AccountDescription NVARCHAR(50),
    AccountType NVARCHAR(50),
    AccountCodeAlternateKey INT
);

-- Insert 10 million rows into the staging table.
DECLARE @loop INT;
DECLARE @AccountDescription VARCHAR(50);
DECLARE @AccountKey INT;
DECLARE @AccountType VARCHAR(50);
DECLARE @AccountCode INT;

SELECT @loop = 0

BEGIN TRANSACTION

WHILE (@loop < 300000)
BEGIN
    SELECT @AccountKey = CAST(RAND() * 10000000 AS INT);
    SELECT @AccountDescription = 'accountdesc ' + CONVERT(VARCHAR(20), @AccountKey);
    SELECT @AccountType = 'AccountType ' + CONVERT(VARCHAR(20), @AccountKey);
    SELECT @AccountCode = CAST(RAND() * 10000000 AS INT);

    INSERT INTO staging
    VALUES (
        @AccountKey,
        @AccountDescription,
        @AccountType,
        @AccountCode
     );

    SELECT @loop = @loop + 1;
END

COMMIT

-- Create a table for the clustered columnstore index
CREATE TABLE cci_target (
    AccountKey INT NOT NULL,
    AccountDescription NVARCHAR(50),
    AccountType NVARCHAR(50),
    AccountCodeAlternateKey INT
);

-- Convert the table to a clustered columnstore index named inxcci_cci_target;
CREATE CLUSTERED COLUMNSTORE INDEX idxcci_cci_target ON cci_target;

TABLOCK オプションを使用して、行を並列に挿入します。 SQL Server 2016 (13.x) 以降では、TABLOCKを使用すると、INSERT INTO操作を並列で実行できます。

INSERT INTO cci_target WITH (TABLOCK)
SELECT TOP 300000 * FROM staging;

このコマンドを実行して、 OPEN デルタ行グループを確認します。 行グループの数は、並列処理の次数によって決まります。

SELECT *
FROM sys.dm_db_column_store_row_group_physical_stats
WHERE object_id  = object_id('cci_target');

このコマンドを実行して、すべての CLOSED 行グループと OPEN 行グループを列ストアに強制的に挿入します。

ALTER INDEX idxcci_cci_target ON cci_target REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);

このコマンドをもう一度実行すると、小さい行グループが 1 つの圧縮された行グループにマージされます。

ALTER INDEX idxcci_cci_target ON cci_target REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);

B. CLOSED デルタ行グループを列ストアに圧縮する

この例では、 REORGANIZE オプションを使用して、各 CLOSED デルタ行グループを圧縮された行グループとして列ストアに圧縮します。 これは必要ありませんが、タプルムーバーが行グループを十分に高速 CLOSED 圧縮していない場合に便利です。

AdventureWorksDW2022 サンプル データベースで両方の例を実行できます。

このサンプルでは、すべてのパーティションで REORGANIZE を実行します。

ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REORGANIZE;

このサンプルでは、特定のパーティションで REORGANIZE を実行します。

-- REORGANIZE a specific partition
ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REORGANIZE PARTITION = 0;

C: すべての OPEN デルタ行グループおよび CLOSED デルタ行グループを列ストアに圧縮する

適用対象: SQL Server (開始値 SQL Server 2016 (13.x)) および Azure SQL データベース

このコマンド REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON) 、各 OPEN を圧縮し、デルタ行グループを圧縮された行グループとして列ストアに CLOSED します。 これにより、デルタストアは空になり、すべての行が強制的に列ストアに圧縮されます。 これは多くの挿入操作を実行した後で特に便利です。挿入操作を多く実行すると行が 1 つまたは複数のデルタ行グループに格納されるからです。

REORGANIZE では、行グループを組み合わせて、最大行数 <= 1,024,576 行を埋めます。 したがって、すべての OPEN と CLOSED 行グループを圧縮しても、その中に少数の行しか含まれない圧縮された行グループが多数作成されることはありません。 行グループにできるだけ多くの行を詰め込むことで、圧縮サイズを縮小し、クエリのパフォーマンスを向上させることができます。

次の例では、AdventureWorksDW2022 データベースを使用します。

次の使用例は、すべての OPEN および CLOSED デルタ行グループを列ストア インデックスに移動します。

ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);

次の使用例は、すべての OPEN および CLOSED デルタ行グループを特定のパーティションの列ストア インデックスに移動します。

ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REORGANIZE PARTITION = 0 WITH (COMPRESS_ALL_ROW_GROUPS = ON);

D. オンラインで列ストア インデックスを最適化する

適用されません: SQL Server 2012 (11.x) と SQL Server 2014 (12.x)。

SQL Server 2016 (13.x) 以降では、 REORGANIZE はデルタ行グループを列ストアに圧縮する以上の処理を行います。 また、オンラインでの最適化も行われます。 まず、行グループ内にある行の 10% 以上が削除されると、削除された行を物理的に削除することで、列ストアのサイズを縮小します。 次に、複数の行グループを結合して、より大きな行グループを形成します。行グループあたり最大で 1,024,576 行を含めることができます。 変更された行グループはすべて再圧縮されます。

Note

SQL Server 2016 (13.x) 以降では、削除された行を物理的に削除して行グループをマージ REORGANIZE ので、ほとんどの状況で列ストア インデックスを再構築する必要がなくなりました。 COMPRESS_ALL_ROW_GROUPSオプションを使用すると、すべてのOPENまたはCLOSEDデルタ行グループが列ストアに強制的に挿入されます。これは、以前は再構築でのみ実行できました。 REORGANIZE はオンラインであり、バックグラウンドで発生するため、操作の発生時にクエリを続行できます。

次の例では、テーブルから論理的に削除された行を物理的に削除し、行グループをマージすることで、インデックスを最適化する REORGANIZE を実行します。

ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REORGANIZE;

E. クラスター化列ストア インデックスをオフラインで再構築する

適用対象:SQL Server (開始値 SQL Server 2012 (11.x))

ヒント

SQL Server 2016 (13.x) 以降および Azure SQL データベース では、列ストアインデックスに対して ALTER INDEX REBUILD ではなく ALTER INDEX REORGANIZE を使用することをお勧めします。

Note

SQL Server 2012 (11.x) と SQL Server 2014 (12.x) では、 REORGANIZE は行グループ CLOSED 列ストアへの圧縮にのみ使用されます。 最適化操作を実行し、すべてのデルタ行グループを列ストアに強制的に移動する唯一の方法は、インデックスを再構築することです。

この例では、クラスター化列ストア インデックスを再構築し、すべてのデルタ行グループを列ストアに強制的に移動する方法を示します。 この最初の手順では、クラスター化列ストア インデックスを含む FactInternetSales2 テーブルを AdventureWorksDW2022 データベースで準備し、最初の 4 つの列からのデータを挿入します。

CREATE TABLE dbo.FactInternetSales2 (
    ProductKey [int] NOT NULL,
    OrderDateKey [int] NOT NULL,
    DueDateKey [int] NOT NULL,
    ShipDateKey [int] NOT NULL);

CREATE CLUSTERED COLUMNSTORE INDEX cci_FactInternetSales2
ON dbo.FactInternetSales2;

INSERT INTO dbo.FactInternetSales2
SELECT ProductKey, OrderDateKey, DueDateKey, ShipDateKey
FROM dbo.FactInternetSales;

SELECT * FROM sys.column_store_row_groups;

結果には 1 つの OPEN 行グループが表示されます。つまり、SQL Server は、行グループを閉じてデータを列ストアに移動する前に、追加される行が増えるのを待機します。 次のステートメントではクラスター化列ストア インデックスを再構築します。これにより、すべての行が列ストアに強制的に移動されます。

ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REBUILD;
SELECT * FROM sys.column_store_row_groups;

SELECTステートメントの結果は、行グループがCOMPRESSEDされていることを示しています。つまり、行グループの列セグメントが圧縮され、列ストアに格納されるようになりました。

F. クラスター化列ストア インデックスのパーティションをオフラインで再構築する

適用対象: SQL Server 2012 (11.x) 以降のバージョン

大きなクラスター化列ストア インデックスのパーティションを再構築するには、ALTER INDEX REBUILD とパーティション オプションを一緒に使用します。 この例では、パーティション 12 を再構築します。 SQL Server 2016 (13.x) 以降では、REBUILD を REORGANIZE に置き換えることをお勧めします。

ALTER INDEX cci_fact3
ON fact3
REBUILD PARTITION = 12;

G. アーカイブ用圧縮を使用するようにクラスター化列ストア インデックスを変更する

適用されません: SQL Server 2012 (11.x)

COLUMNSTORE_ARCHIVEデータ圧縮オプションを使用して、クラスター化列ストア インデックスのサイズをさらに小さくすることもできます。 これは、以前のデータをより安価なストレージに保持したい場合に実用的な方法です。 これは、圧縮解除が通常の COLUMNSTORE 圧縮よりも遅いため、頻繁にアクセスされないデータでのみ使用することをお勧めします。

次の例では、保存用圧縮を使用するクラスター化列ストア インデックスを再構築し、次に保管用圧縮を削除する方法を示します。 最終的な結果では、列ストア圧縮のみが使用されます。

まず、クラスター化列ストア インデックスを含むテーブルを作成して、例を準備します。 その後、アーカイブ圧縮を使用してテーブルをさらに圧縮します。

--Prepare the example by creating a table with a clustered columnstore index.
CREATE TABLE SimpleTable (
    ProductKey [int] NOT NULL,
    OrderDateKey [int] NOT NULL,
    DueDateKey [int] NOT NULL,
    ShipDateKey [int] NOT NULL
);

CREATE CLUSTERED INDEX cci_SimpleTable ON SimpleTable (ProductKey);

CREATE CLUSTERED COLUMNSTORE INDEX cci_SimpleTable
ON SimpleTable
WITH (DROP_EXISTING = ON);

--Compress the table further by using archival compression.
ALTER INDEX cci_SimpleTable ON SimpleTable
REBUILD
WITH (DATA_COMPRESSION = COLUMNSTORE_ARCHIVE);
GO

このサンプルでは、アーカイブ圧縮を削除し、列ストア圧縮のみを使用します。

ALTER INDEX cci_SimpleTable ON SimpleTable
REBUILD
WITH (DATA_COMPRESSION = COLUMNSTORE);
GO

例 :行ストア インデックス

A. インデックスを再構築する

次の例では、AdventureWorks2022 データベースにある Employee テーブルで単一のインデックスを再構築します。

ALTER INDEX PK_Employee_EmployeeID ON HumanResources.Employee REBUILD;

B. テーブルですべてのインデックスを再構築し、オプションを指定する

次の例では、キーワード ALL を指定します。 これにより、AdventureWorks2022 データベースのテーブル Production.Product に関連付けられているすべてのインデックスが再構築されます。 3 つのオプションが指定されます。

ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON);

次の例は、優先度の低いロック オプションを含めて ONLINE オプションを追加し、行の圧縮オプションを追加します。

適用対象: SQL Server 2014 (12.x) 以降のバージョン、および Azure SQL Database

ALTER INDEX ALL ON Production.Product
REBUILD WITH
(
    FILLFACTOR = 80,
    SORT_IN_TEMPDB = ON,
    STATISTICS_NORECOMPUTE = ON,
    ONLINE = ON ( WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 4 MINUTES, ABORT_AFTER_WAIT = BLOCKERS ) ),
    DATA_COMPRESSION = ROW
);

C: LOB 圧縮を行いインデックスを再構成する

次の例では、AdventureWorks2022 データベースの単一のクラスター化インデックスを再構成します。 インデックスではリーフ レベルに LOB データ型が含まれるので、このステートメントではラージ オブジェクト データを含むすべてのページが圧縮されます。 既定値が ON であるため、WITH (LOB_COMPACTION = ON) オプションの指定は必須ではありません。

ALTER INDEX PK_ProductPhoto_ProductPhotoID ON Production.ProductPhoto REORGANIZE WITH (LOB_COMPACTION = ON);

D. インデックスにオプションを設定する

次の例では、AdventureWorks2022 データベースのインデックス AK_SalesOrderHeader_SalesOrderNumber にいくつかのオプションを設定します。

ALTER INDEX AK_SalesOrderHeader_SalesOrderNumber ON
    Sales.SalesOrderHeader
SET (
    STATISTICS_NORECOMPUTE = ON,
    IGNORE_DUP_KEY = ON,
    ALLOW_PAGE_LOCKS = ON
    ) ;
GO

E. インデックスを無効にする

次の例では、AdventureWorks2022 データベースにある Employee テーブルで非クラスター化インデックスを無効にします。

ALTER INDEX IX_Employee_ManagerID ON HumanResources.Employee DISABLE;

F. 制約を無効にする

次の例では、AdventureWorks2022 データベースのPRIMARY KEY インデックスを無効にすることで、PRIMARY KEY制約を無効にします。 基になるテーブルの FOREIGN KEY 制約は自動的に無効になり、警告メッセージが表示されます。

ALTER INDEX PK_Department_DepartmentID ON HumanResources.Department DISABLE;

結果セットでは、次の警告メッセージが返されます。

Warning: Foreign key 'FK_EmployeeDepartmentHistory_Department_DepartmentID'
on table 'EmployeeDepartmentHistory' referencing table 'Department'
was disabled as a result of disabling the index 'PK_Department_DepartmentID'.

G. [Enable constraints](制約の有効化)

次の例では、例 F で無効にされた PRIMARY KEY 制約と FOREIGN KEY 制約を有効にします。

PRIMARY KEY制約は、PRIMARY KEY インデックスを再構築することによって有効になります。

ALTER INDEX PK_Department_DepartmentID ON HumanResources.Department REBUILD;

その後、 FOREIGN KEY 制約が有効になります。

ALTER TABLE HumanResources.EmployeeDepartmentHistory
CHECK CONSTRAINT FK_EmployeeDepartmentHistory_Department_DepartmentID;
GO

H. パーティション インデックスを再構築する

次の例では、AdventureWorks2022 データベースのパーティション インデックス 5 のパーティション番号 IX_TransactionHistory_TransactionDate の単一パーティションを再構築します。 パーティション 5 は ONLINE=ON で再構築され、優先度の低いロックの 10 分間の待機時間が、インデックスの再構築操作によって取得された各ロックに個別に適用されます。 この期間中に、インデックスの再構築を完了するためのロックを取得できない場合、ABORT_AFTER_WAIT = SELF であるため、再構築操作のステートメント自体が中止されます。

適用対象: SQL Server 2014 (12.x) 以降のバージョン、および Azure SQL Database

-- Verify the partitioned indexes.
SELECT *
FROM sys.dm_db_index_physical_stats (DB_ID(),OBJECT_ID(N'Production.TransactionHistory'), NULL , NULL, NULL);
GO
--Rebuild only partition 5.
ALTER INDEX IX_TransactionHistory_TransactionDate
ON Production.TransactionHistory
REBUILD Partition = 5
   WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 10 minutes, ABORT_AFTER_WAIT = SELF)));
GO

I. インデックスの圧縮設定を変更する

次の例では、非パーティション行ストア テーブルのインデックスを再構築します。

ALTER INDEX IX_INDEX1
ON T1
REBUILD
WITH (DATA_COMPRESSION = PAGE);
GO

J. インデックスの設定を XML 圧縮に変更する

適用対象: SQL Server 2022 (16.x) 以降のバージョン、Azure SQL データベース、および Azure SQL Managed Instance。

次の例では、非パーティション行ストア テーブルのインデックスを再構築します。

ALTER INDEX IX_INDEX1
ON T1
REBUILD
WITH (XML_COMPRESSION = ON);
GO

その他のデータ圧縮の例については、「 Data 圧縮」を参照してください。

K. オンラインでの再開可能なインデックス再構築

適用対象: SQL Server 2017 (14.x) 以降のバージョンと Azure SQL Database

次の例では、オンラインでの再開可能なインデックス再構築を使用する方法を示します。

MAXDOP = 1を使用して、再開可能な操作としてオンライン インデックス再構築を実行します。 インデックス操作が一時停止された後に同じコマンドをもう一度実行すると、インデックスの再構築操作が自動的に再開されます。

ALTER INDEX test_idx on test_table REBUILD WITH (ONLINE = ON, MAXDOP = 1, RESUMABLE = ON);

MAX_DURATIONを 240 分に設定して、再開可能な操作としてオンライン インデックス再構築を実行します。

ALTER INDEX test_idx on test_table REBUILD WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 240);

オンラインでの再開可能なインデックス再構築の実行を一時停止します。

ALTER INDEX test_idx on test_table PAUSE;

4 に設定された新しい値を指定して再開可能な操作として実行されたインデックス再構築のオンライン インデックス再構築 MAXDOP 再開します。

ALTER INDEX test_idx on test_table RESUME WITH (MAXDOP = 4);

再開可能として実行されたオンラインでのインデックス再構築について、オンラインでのインデックス再構築操作を再開します。 MAXDOPを 2 に設定し、再開可能なインデックスの実行時間を 240 分に設定します。ロックでインデックスがブロックされている場合は、10 分待ってからすべてのブロックを強制終了します。

ALTER INDEX test_idx on test_table
    RESUME WITH (MAXDOP = 2, MAX_DURATION = 240 MINUTES,
    WAIT_AT_LOW_PRIORITY (MAX_DURATION = 10, ABORT_AFTER_WAIT = BLOCKERS));

実行中または一時停止中の再開可能なインデックス再構築操作を中止します。

ALTER INDEX test_idx on test_table ABORT;

関連するコンテンツ