この記事では、SQL ServerにおけるIndex Fragmentationの特定と解決方法について説明します。 インデックスフラグメントの特定とインデックスのメンテナンスは、データベースのメンテナンスタスクの重要な部分です。 Microsoft SQL Server は、テーブルに対する Insert、Update、Delete 操作で、インデックス統計値を更新しつづけます。 インデックスフラグメンテーションは、SQL Server DMVで取得できるインデックスの性能値（パーセンテージ）です。
Why the Index Fragmentation percent vary?
Index Fragmentation percentage vary when the logical page orders do not coordinate with the physical page order in the page allocation of an index.Why the Index Fragmentation percent vary? テーブルのデータ修正に伴い、データページ上で情報のリサイズが可能です。 テーブルの更新操作の前に、ページはトップフルでした。 しかし、テーブルの更新操作により、データページ上に空き領域が発見される可能性があります。 ユーザーは、テーブルの大規模な削除操作によって、乱れたページ順序を観察することができます。 更新と削除操作に伴い、データページはトップフルや空のページにはならない。 したがって、未使用の空き領域は、断片化の増加に伴い、論理ページと物理ページの間の順序の不一致を高め、それは最悪のクエリ性能を引き起こし、同様に多くのサーバーリソースを消費することができます。 テーブル上の高断片化インデックスを持つクエリの実行に多くの時間がかかり、キャッシュ、CPU、および IO などのリソースをより多く消費するため、このような場合、他の SQL 要求のパフォーマンスが低下する可能性が高くなります。 そのため、他の SQL リクエストは、サーバーのリソースが不安定な状態で処理を完了することが困難となります。 オプティマイザはクエリの実行計画を生成する際にインデックスの断片化の情報を収集しないため、更新および削除操作によってブロッキングさえ発生する可能性があります。 そして、それぞれのインデックスは異なる断片化率を持つことができます。さて、適切なインデックスを作成する、またはメンテナンス中のインデックスを取る前に、ユーザーはデータベースからその閾値を見つけなければなりません。 以下のT-SQLステートメントは、オブジェクトの詳細とそれを見つけるための効率的な方法です。
T-SQLステートメントを使用してインデックスフラグメンテーションステータスを検索する。SQL文
|
1
2
3
4
5
6
57
8
9
10
11
12
13
14
|
select s.name as ‘Schema’,
T.name as ‘Table’,
I.name as ‘Index’,
DDIPS.avg_fragmentation_in_percent,
DDIPS.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS DDIPS
INNER JOIN sys.DB_index_physical_stats (DB_ID(), DNULL, DNULL, NULL, NULL) AS DDIPS
INNER JOIN sys.tables T on T.object_id = DDIPS.object_id
INNER JOIN sys.schemas S on T.schema_id = S.schema_id
INNER JOIN sys.indexes I ON I.object_id = DDIPS.Object_d = DDIPS.Object_id
INNER JOIN sys.schema_id = DDIPS.Object_id
AND DDIPS.index_id = I.index_id
WHERE DDIPS.database_id = DB_ID()
and I.name is not null
AND DDIPS.index_id = I.index_id
WITH I.name is not null
AND DDIPS.Object_id = DDIPS.index_id
ORDER BY DDIPS.avg_fragmentation_in_percent desc
|
ここで、最大平均断片化率は99％と顕著で、REBUILDまたはREORGANIZEのいずれかの選択肢で断片化を減らすアクションを取る必要があることを確認できます。 REBUILDまたはREORGANIZEはALTER INDEXステートメントで実行できるインデックス・メンテナンス・コマンドです。
Rebuild and Reorganize Index using SQL Server Management Studio (SSMS)
Object Explorerでテーブルを見つけて展開 >> Open Indexes >> Target Indexで右クリック >> Rebuild or Reorganize.
上の画像にあるように、REBUILD と REORGANIZE は、ページ上でトリム操作を実行するための 2 つの利用可能な選択肢となります。 理想的には、この操作は、他のトランザクションやユーザーへの影響を避けるために、オフピーク時に実行する必要があります。 Microsoft SQL Server Enterprise Editionは、インデックスREBUILDでインデックスオンラインおよびオフライン機能をサポートします。
REBUILD INDEX
INDEX REBUILDは常にインデックスをドロップし、新しいインデックスページでそれを再生成します。 この活動は、ALTER INDEXコマンドでオンライン・オプション（Enterprise Edition）を使用して並行して実行することができ、類似テーブルの実行要求やタスクに影響を与えません。
REBUILD インデックスは、以下のSQLコマンドを使用してオンラインまたはオフラインで設定することができます。
|
1
2
3
4
5
|
–基本再構築コマンド
ALTER INDEX Index_Name ON Table_Name REBUILD
-> REBUILDコマンドは、以下のSQLコマンドを使用してオンラインまたはオフラインでインデックスを設定できます。-REBUILD Index with ONLINE OPTION
ALTER INDEX Index_Name ON Table_Name REBUILD WITH(ONLINE=ON) | WITH(ONLINE=ON)
|
もしユーザーがオフラインで REBUILD INDEX を実行した場合は、次のようにします。 REBUILD処理が完了するまで、インデックスのオブジェクト・リソース（Table）にアクセスできなくなります。 これは、このオブジェクトに関連する他の多くのトランザクションにも影響します。 インデックスの再構築操作は、インデックスを再作成します。 したがって、新しい統計情報を生成し、データベース・トランザクション・ログ・ファイルにインデックスのログ・レコードも追加します。
たとえば、インデックスを再構築する前に、AdventureWorksデータベース、Sales.OrderTrackingテーブル、IX_OrderTracking_CarrierTrackingNumberというインデックスの現在のページ割り当てはどうなっているのかを見てみましょう。
|
1
2
3
4
5
|
select object_name(ix.ORG)
|
| select object_name(ix.ORG)object_id) as db_name, si.name, extent_page_id, allocated_page_page_id, previous_page_page_id, next_page_page_id
FROM sys.Object_id (ix.Object_id) as db_name, si.name, extent_page_id, allocate_page_id, next_page_page_id
FROM sys.dm_db_database_page_allocations(DB_ID(‘AdventureWorks’), OBJECT_ID(‘Sales.OrderTracking’),NULL, NULL, ‘DETAILED’) IX
INNER JOIN sys.indexes si on IX.object_id = si.object_id AND IX.index_id = si.index_id
WHERE si.index=si.object_id
IX.index_id = si.index_id
WHERE si.index=si.index_id
IX.index=si.object_id = si.index_idname = ‘IX_OrderTracking_CarrierTrackingNumber’
ORDER BY allocated_page_page_id
|
ここで。 このインデックスのデータベースファイルには1961ページが存在し、最初の5ページはページ番号順に861、862、1627、1628、1904となる。 ここで、SSMSを使用してインデックスを再構築してみましょう。
Index REBUILD操作は正常に完了し、再度同じT-SQLクエリの助けを借りて同じインデックスのページ割り当て参照を取得します。
|
1
2
3
4
5
6
|
select object_name(ix.) {7645>
| 1
{7645>1 {7645>2 {7645>3 previous_page_id, next_page_id
FROM sys.Object_id (ix.Object_id) as db_name, si.name, extent_page_id, allocate_page_id,
from sys.dm_db_database_page_allocations(DB_ID(‘AdventureWorks’), OBJECT_ID(‘Sales.OrderTracking’),NULL, NULL, ‘DETAILED’) IX
INNER JOIN sys.indexes si on IX.object_id = si.object_id AND IX.index_id = si.index_id
WHERE si.name = ‘IX_OrderTracking_CarrierTrackingNumber’
ORDER BY allocated_page_page_id
|
インデックス再構築後、更新されたページ数は1457、それまでは1961だったのが、1961となっています。 同じインデックスの最初の5つの割り当てページを確認すると、新しいページ参照で変更されています。 これは、インデックスを削除してもう一度作成したものと推測されます。 同じインデックスのリフレッシュされた断片化率を確認する必要がありますが、以下に見られるように、現在は 0.1% です。
REBUILD クラスタ化インデックスはテーブルの非クラスタ化インデックスも再構築するので、テーブルの他のインデックスも影響を受けます。 テーブルまたはデータベースのすべてのインデックスに対して一緒に再構築操作を実行します。ユーザは DBCC DBREINDEX() コマンドを使用することができます。
|
1
|
DBCC DBREINDEX (‘DatabaseName’, ‘TableName’) を実行しました。
|
REORGANIZED INDEX
REORGANIZE INDEXコマンドは、ページ上の空きスペースまたは未使用スペースを削除してインデックスページを再順序付けするコマンドです。 理想的には、インデックスページはデータファイル内で物理的に並べ替えられます。 REORGANIZEはインデックスをドロップして作成するのではなく、単にページ上の情報を再構築するだけである。 REORGANIZEにはオフラインの選択肢がなく、REBUILDオプションと比較して、REORGANIZEは統計に影響を与えません。 REORGANIZE は常にオンラインで実行されます。
たとえば、インデックスに対して REORGANIZE を実行する前に、データベース ‘AdventureWorks’、テーブル ‘Sales.OrderTracking’ およびインデックス名 ‘IX_OrderTracking_SalesOrderID’ の断片化の読み取りを行ってみましょう。 画像の下のリストは、インデックスへの割り当てページです。
|
1
2
3
4
5
6
|
SELECT OBJECT_NAME(IX.object_id) as db_name, si.name, extent_page_id, allocated_page_page_id,
previous_page_id, next_page_id
FROM sys.dm_db_database_page_allocations(DB_ID(‘AdventureWorks’), OBJECT_ID(‘Sales.Pirates’), OBJECT_ID(IX.Pirates’) as db_name, si.name, extended_page_id, allocate_page_id,
INNER JOIN sys.indexes si on IX.object_id = si.object_id AND IX.index_id = si.index_id
WHERE si.name = ‘IX_OrderTracking_CarrierTrackingNumber’
ORDER BY allocated_page_page_id
|
ここで、上記の画像では合計459ページ記載されていて、最初の5ページは1065、1068、1069、1944、1945になっています。 では、以下のT-SQL文を使ってインデックスにREORGANIZEコマンドを実行し、ページ割り当てをもう一度見てみましょう。
|
1
|
ALTER INDEX IX_OrderTracking_SalesOrderID ON Sales.OrderTracking REORGANIZE
|
ここで、総ページ数は以前459でしたが、331に減少しています。 さらに、最初の5ページのリストに新しいページがないことから、データが再構築されただけで、再充填されたわけではないことがわかります。 テーブルまたはデータベースのすべてのインデックスに対してREORGANIZEインデックス操作を実行するには、ユーザーはDBCC INDEXDEFRAG()コマンドを使用できます。
|
1
|
DBCC INDEXDEFRAG(‘DatabaseName’, ‘TableName’);
|
ご覧のようにインデックス REBUILD と REORGANIZE にはかなりの違いがあることがおわかりいただけると思います。 ここでは、ユーザーはインデックスの断片化の割合に応じて、選択肢の中から1つを選ぶことができます。 しかし、データベース管理者は、インデックスのサイズと情報の種類の要件に従って、標準的な式に従います。 REORGANIZE
REBUILDオプションは、ONLINEオプションと併用すると、オフピーク時にデータベースがインデックスメンテナンスを受けられない場合に便利です。
結論
Index Fragmentationとはデータファイル内部のフラグメントのことです。 データベースの高速性能の核となるパラメータは、データベースアーキテクチャ、データベース設計、クエリ作成です。 メンテナンスの行き届いたインデックス設計は、データベースエンジンのクエリパフォーマンスを常に向上させます。
- Author
- Recent Posts
Jignesh Raiyani さんの投稿をすべて表示
- Page Life Expectancy (PLE) in SQL Server – July 17, 2020年
- SQL Serverでテーブルパーティショニングを自動化する方法 – 2020年7月7日
- AWS EC2上でSQL Server Always On Availability Groupを構成する – 2020年7月6日