Resolve index fragmentation by reorganizing or rebuilding indexes

• 03/19/2020
• 21 minutes to read
• • p
  • j
  • M
  • M
  • d
  • +11

Applies to: SQL Server(すべてのサポートされているバージョン)Azure SQLデータベースAzure SQLマネージインスタンスAzure Synapse Analyticsパラレルdata warehouse

この記事では、インデックスの最適化がどのように行われるかについて説明し、クエリのパフォーマ インデックスに存在する断片化の量を決定したら、選択したツールでTransact-SQLコマンドを実行するか、SQL Server Management Studioを使用して、インデックスを再編成するか、

インデックスの断片化の概要

インデックスの断片化とは何か、なぜそれを気にする必要があります。

• 断片化は、インデックスのキー値に基づ
• データベースエンジンは、基になるデータに対して挿入、更新、または削除操作が行われるたびに、インデックスを自動的に変更します。 たとえば、テーブルに行を追加すると、行ストアインデックス内の既存のページが分割されて、新しいキー値の挿入のためのスペースが確保される場合があ 時間の経過とともに、これらの変更により、インデックス内の情報がデータベース内に分散(断片化)される可能性があります。 断片化は、キー値に基づく論理的な順序がデータファイル内の物理的な順序と一致しないページがインデックスにある場合に発生します。
• インデックスが大きく断片化されていると、インデックスが指すデータを検索するために追加のI/Oが必要になるため、クエリのパフォーマ I/Oが増えると、特にスキャン操作が関与している場合、アプリケーションの応答が遅くなります。

断片化の量の検出

使用するインデックスの最適化方法を決定するための最初のステップは、断片化の程度を決定するためにインデ 行ストア索引と列ストア索引では、断片化の検出方法が異なります。

行ストアインデックスの断片化を検出する

sysを使用します。dm_db_index_physical_statsでは、特定のインデックス、テーブルまたはインデックス付きビューのすべてのインデックス、データベース内のすべてのインデックス、またはすべてのデータベース内のすべてのインデックスの断片化を検出できます。 パーティション索引の場合、sys.dm_db_index_physical_statsは、各パーティションの断片化情報も提供します。

sys.dm_db_index_physical_statsによって返される結果セットには、次の列が含まれます:Tr>

断片化の程度がわかった後、断片化を削除するための最良の方法を決定するために、次の表を使用します。INDEX REORGANIZEまたはINDEX。

1 These values provide a rough guideline for determining the point at which you should switch between ALTER INDEX REORGANIZEALTER INDEX REBUILD。 ただし、実際の値は場合によって異なる場合があります。 環境に最適なしきい値を決定するために実験することが重要です。

2インデックスの再構築は、オンラインまたはオフラインで実行できます。 インデックスの再編成は、常にオンラインで実行されます。 Reorganizeオプションと同様の可用性を実現するには、索引をオンラインで再構築する必要があります。 詳細については、”オンラインでのインデックス操作とインデックス操作の実行”を参照してください。

断片化が5パーセント未満のインデックスは、断片化のような少量を削除することの利点は、インデックスの再編成または再構築に発生するCPUコ また、小さな行ストア索引を再構築または再編成しても、通常は実際の断片化は減少しません。SQL Server2014(12.x)、SQL Serverデータベースエンジンは、混合エクステントを使用して領域を割り当てます。 したがって、小さなインデックスのページは、混在したエクステントに格納されることがあります。 混合エクステントは最大8つのオブジェクトで共有されるため、小さなインデックスの断片化は、再編成または再構築後に縮小されない可能性があ 行ストア索引の再構築に固有の考慮事項も参照してください。 エクステントの詳細については、”Pages and Extents Architecture Guide”を参照してください。

列ストアインデックスの断片化の検出

sys.dm_db_column_store_row_group_physical_statsを使用すると、インデックス内の削除された行の割合を判断できます。 この情報を使用して、特定の索引、表のすべての索引、データベース内のすべての索引、またはすべてのデータベース内のすべての索引の断片化を計算します。

sys.dm_db_column_store_row_group_physical_statsによって返される結果セットには、次の列が含まれます。

total_rows

返されたこの情報を使用して、次の式を使用してインデックスの断片化を計算します。

100*(ISNULL(deleted_rows,0))/NULLIF(total_rows,0)

インデックスの断片化の程度がわかった後、次の表を使用して、断片化を削除する最良の方法を決定します。INDEX REORGANIZEまたはINDEX。

Transact-SQLを使用して行ストアインデックスの断片化をチェックする

次の例では、HumanResources.EmployeeHumanResources.EmployeeHumanResources.EmployeeHumanResources.EmployeeHumanResources.EmployeeHumanResources.EmployeeAdventureWorks2016データベース。

SELECT a.object_id, object_name(a.object_id) AS TableName, a.index_id, name AS IndedxName, avg_fragmentation_in_percentFROM sys.dm_db_index_physical_stats (DB_ID (N'AdventureWorks2016_EXT') , OBJECT_ID(N'HumanResources.Employee') , NULL , NULL , NULL) AS aINNER JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;GO

前のステートメントは、次のような結果セットを返します。p>

object_id TableName index_id IndexName avg_fragmentation_in_percent----------- ------------ ----------- ----------------------------------------------------- ------------------------------1557580587 Employee 1 PK_Employee_BusinessEntityID 01557580587 Employee 2 IX_Employee_OrganizationalNode 01557580587 Employee 3 IX_Employee_OrganizationalLevel_OrganizationalNode 01557580587 Employee 5 AK_Employee_LoginID 66.66666666666671557580587 Employee 6 AK_Employee_NationalIDNumber 501557580587 Employee 7 AK_Employee_rowguid 0(6 row(s) affected)

詳細については、”sys.dm_db_index_physical_stats”を参照してください。

Transact-SQLを使用して列ストアインデックスの断片化をチェックするには

次の例では、AdventureWorksDW2016dbo.FactResellerSalesXL_CCIテーブル

SELECT i.object_id, object_name(i.object_id) AS TableName, i.index_id, i.name AS IndexName, 100*(ISNULL(SUM(CSRowGroups.deleted_rows),0))/NULLIF(SUM(CSRowGroups.total_rows),0) AS 'Fragmentation'FROM sys.indexes AS i INNER JOIN sys.dm_db_column_store_row_group_physical_stats AS CSRowGroups ON i.object_id = CSRowGroups.object_id AND i.index_id = CSRowGroups.index_idWHERE object_name(i.object_id) = 'FactResellerSalesXL_CCI'GROUP BY i.object_id, i.index_id, i.nameORDER BY object_name(i.object_id), i.name;

前のステートメントは、次のような結果セットを返します。

object_id TableName index_id IndexName Fragmentation----------- --------------------------- ----------- ------------------------------- ---------------114099447 FactResellerSalesXL_CCI 1 IndFactResellerSalesXL_CCI 0(1 row(s) affected)

SQL Server Management Studioを使用してインデックスの断片化を確認する

1. オブジェクトエクスプローラーで、インデックスの断片化をチェックするテーブルが含まれているデータベースを展開します。
2. Tablesフォルダを展開します。
3. インデックスの断片化をチェックするテーブルを展開します。
4. Indexesフォルダを展開します。
5. 断片化を確認するインデックスを右クリックし、プロパティを選択します。
6. “ページの選択”で、”断片化”を選択します。

断片化ページでは、次の情報を利用できます。

インデックスの再構築または再編成によるインデックスの最適化

次のいずれかの方法を使用して、フラグメ:

• インデックス再編成
• インデックス再構築

インデックスの再編成

インデックスの再編成は、最小限のシステムリソースを使用し、オンライン操作です。 つまり、長期ブロックテーブルロックは保持されず、ALTER INDEX REORGANIZEトランザクション中に基になるテーブルへのクエリまたは更新を続行できます。

• 行ストアインデックスの場合、データベースエンジンは、リーフノードの論理順序(左から右)に一致するようにリーフレベルのページを物理的に並べ替え また、再編成すると、インデックスの塗りつぶし係数の値に基づいてインデックスページが圧縮されます。 Fill factor設定を表示するには、sysを使用します。インデックス。 構文の例については、”例:Rowstore reorganize”を参照してください。

• 列ストアインデックスを使用する場合、デルタストアは、時間の経過とともにデータを挿入、更新、および削除した後、複数の小さな行グループにな 列ストアインデックスを再編成すると、すべての行グループが列ストアに強制され、行グループが少数の行グループに結合され、より多くの行が追加されます。 また、reorganize操作では、列ストアから削除された行も削除されます。 再編成では、最初にデータを圧縮するために追加のCPUリソースが必要になり、システム全体のパフォーマンスが低下する可能性があります。 ただし、データが圧縮されるとすぐに、クエリのパフォーマンスが向上します。 構文の例については、”例:ColumnStore reorganize”を参照してください。

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

インデックスを再構築すると、インデックスが削除されて再作成されます。 インデックスの種類とデータベースエンジンのバージョンに応じて、再構築操作はオンラインまたはオフラインで実行できます。 T-SQL構文については、”alter INDEX REBUILD

• 行ストアインデックスについては、再構築すると断片化が削除され、指定されたまたは既存のfill factor設定に基づいてペー ALLが指定されている場合、テーブル上のすべてのインデックスが削除され、単一のトランザクションで再構築されます。 外部キー制約を事前に削除する必要はありません。 エクステントが128以上のインデックスが再構築されると、データベースエンジンは、トランザクションがコミットされるまで、実際のページの割り当て解除 構文の例については、”例:Rowstore reorganize”を参照してください。

• 列ストアインデックスの再構築では、断片化が削除され、すべての行が列ストアに移動され、テーブルから論理的に削除された行を物理的に削除

  ヒント

  SQL Server2016以降(13.x)、列ストアインデックスの再構築は、通常、REORGANIZEがオンライン操作としてバックグラウンドで再構築の本質を実行するため、必要あり

  構文の例については、”例:列ストアの再構築”を参照してください。

パーミッション

テーブルまたはビューに対するALTERパーミッションが必要です。 ユーザーは、次のいずれかのロールのメンバーである必要があります。

• db_ddladminデータベースロール1
• db_ownerデータベースロール
• sysadminサーバーロール

1db_ddladminデータベースロール

SQL Server Management Studioを使用した断片化の削除

インデックスを再編成または再構築するには

1. オブジェクトエクスプローラーで、インデックスを再
2. Tablesフォルダを展開します。
3. インデックスを再編成するテーブルを展開します。
4. Indexesフォルダを展開します。
5. 再編成するインデックスを右クリックし、”再編成”を選択します。
6. インデックスの再編成ダイアログボックスで、再編成するインデックスグリッドに正しいインデックスがあることを確認し、OKをクリックします。
7. ラージ-オブジェクト(LOB)データを含むすべてのページも圧縮するように指定するには、”ラージ-オブジェクト列データの圧縮”チェック-ボックスをオンにします。
8. [OK]をクリックします。

オブジェクトエクスプローラーで表

1. 内のすべての索引を再編成するには、索引を再編成する表が含まれるデータベースを展開します。
2. Tablesフォルダを展開します。
3. インデックスを再編成するテーブルを展開します。
4. Indexesフォルダを右クリックし、”すべて再編成”を選択します。
5. インデックスの再編成ダイアログボックスで、再編成するインデックスに正しいインデックスがあることを確認します。 再編成するインデックスグリッドからインデックスを削除するには、インデックスを選択し、Deleteキーを押します。
6. ラージ-オブジェクト(LOB)データを含むすべてのページも圧縮するように指定するには、”ラージ-オブジェクト列データの圧縮”チェック-ボックスをオンにします。
7. [OK]をクリックします。

オブジェクトエクスプローラーでインデックスを再構築するには、インデックスを再編成するテーブルが含まれているデータベースを展開します。

Transact-SQLを使用した断片化の削除

ALTER INDEX IX_Employee_OrganizationalLevel_OrganizationalNode ON HumanResources.Employee REORGANIZE;

-- This command will force all CLOSED and OPEN rowgroups into the columnstore.ALTER INDEX IndFactResellerSalesXL_CCI ON FactResellerSalesXL_CCI REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);

ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.EmployeeREBUILD;

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