MS SQLでインデックスを再編成および再構築する方法

SQL

インデックスはMS SQLデータベースファイルのオブジェクトです。これらのスキーマオブジェクトはデータベースの目次のように機能します。これらはMS SQLデータベーステーブルのデータ検索操作を改善するために使用されます。

ただし、時間の経過とともに、テーブルは連続したINSERTおよびUPDATE操作によって断片化する可能性があります。また、他のオブジェクト同様、インデックスも破損しやすいです。インデックスが破損すると、次のようなエラーが発生する可能性があります：

Plain Text

Table error: table 'Items' (ID 1954106002). Data row does not have a matching index row in the index 'IX_Advertise_BrandCopy_Price_Stock' (ID 69). Possible missing or invalid keys for the index row matching:

​

Msg 8955, Level 16, State 1, Line 1

​

Data row (1:11226494:9) identified by (Id = 11078215) with index values 'AdvertiseFlag = 1 and BrandCopyParentId = 0 and NSFPPrice = 137.50 and NSFPQtyInStock = 0 and Id = 11078215'.

​

Msg 8951, Level 16, State 1, Line 1

インデックスの破損に関連するエラーを解決するか、インデックスの断片化レベルが増加した場合、インデックスを再構築または再編成できます。この記事では、MS SQL Serverでインデックスを再編成および再構築する方法について説明します。また、破損したSQLデータベースファイルからインデックスおよび他のオブジェクトを完全に正確に迅速に復元できる高度なMS SQLデータベース修復ツールについても言及します。

インデックスの断片化率を確認する

インデックスを再編成する前に、断片化率を知る必要があります。以下のコマンドを使用して、インデックスの断片化率を確認できます：

SQL

SELECT 
    OBJECT_NAME(object_id) tableName12,
    index_id,
    index_type_desc,
    avg_fragmentation_in_percent,
    page_count
FROM 
    sys.dm_db_index_physical_stats (
        DB_ID('Adventureworks2019'), 
        OBJECT_ID('[Person].[Person]'), 
        NULL, NULL, 'DETAILED'
    );
​

SQL Serverでのインデックスの再編成

インデックスの断片化率が10から30の間の場合、インデックスを再編成することをお勧めします。次のT-SQLコードを使用して、インデックスを再編成できます：

SQL

ALTER INDEX [PK_Person_BusinessEntityID] ON [Person].[Person]
REORGANIZE;
​

代わりに、SQL Server Management Studio（SSMS）を使用してインデックスを再編成することもできます。以下の手順に従ってください：

SSMS で SQL Server インスタンスに接続します。
オブジェクトエクスプローラーでデータベースを展開します。
テーブルフォルダーを展開し、次にインデックスフォルダーを展開します。
再編成するインデックスを右クリックし、再編成をクリックします。

MS SQL でのインデックスの再構築

断片化が高い場合は、インデックスを再構築することができます。インデックスを再構築するには、DBCC DBREINDEX コマンドを使用します。このコマンドを実行する方法は次のとおりです：

SQL

DBCC DBREINDEX
(
    table_name
    [ , index_name [ , fillfactor ] ]
)
    [ WITH NO_INFOMSGS ]
​

このコマンドは、空間インデックスとメモリ最適化列ストアインデックスをサポートしていません。

代替手段として、ALTER INDEX コマンドを使用することもできます：

SQL

ALTER INDEX [PK_Person_BusinessEntityID] ON [Person].[Person]
REBUILD;
​

また、SSMS のグラフィカルユーザーインターフェイスを使用してインデックスを再構築することもできます。手順は次のとおりです：

SSMS で、オブジェクトエクスプローラーで、インデックスを再構築する必要のあるテーブルを含むデータベースを展開します。
Tables フォルダーを展開し、再構築するインデックスが必要なテーブルを展開します。
Indexes フォルダーを展開し、再構築するインデックスを右クリックし、再構築 を選択します。

SQL Server データベースの修復

インデックスの再編成や再構築がうまくいかない場合は、DBCC CHECKDBコマンドをREPAIR_ALLOW_DATA_LOSSオプションとともに使用してデータベースを修復することができます。このコマンドの使用方法は以下の通りです。

まず、以下のコマンドを使用してデータベースのモードをSINGLE_USERに変更します。

SQL

ALTER DATABASE BusinessEntity SET SINGLE_USER

次に、以下のようにDBCC CHECKDBコマンドを実行してデータベースを修復します。

SQL

DBCC CHECKDB (N ’BusinessEntity’, REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS, NO_INFOMSGS; 
GO
​

修復後、以下のコマンドを実行してデータベースのモードをMULTI_USERに設定します。

SQL

ALTER DATABASE BusinessEntity SET MULTI_USER

上記のDBCC CHECKDBコマンドを使用すると、インデックスの破損に関連するすべてのエラーを解決できます。これは、SQLデータベースのクラスター化および非クラスター化された両方のインデックスを修復できます。ただし、完全なデータの回復を保証するものではなく、データ損失が発生する可能性があります。データ損失を防ぐためには、専門のMS SQL修復ツールを使用できます。これらのツールは、クラスター化および非クラスター化されたインデックス、ストアドプロシージャ、トリガーなど、すべてのオブジェクトを回復するために高度なアルゴリズムで設計されています。

結論

インデックスの断片化を減少させるには、インデックスの再編成や再構築ができます。この記事では、インデックスの断片化率を確認する方法やインデックスの再構築方法について説明しました。

Source:
https://dzone.com/articles/reorganize-and-rebuild-indexes-in-ms-sql