セージ の メモ書き

メモこそ命の恩人だ

SQL Server - 断片化/再構築/再編成

断片化

  • インデックスを使用する場合に発生する考え方。
  • インデックスページの行はキーバリューの構成。
    • キー:インデックスで指定したカラムの値
    • バリュー:ページID

データページの断片化

  • 以下の並びが一致すれば、断片化が小さい。
    • インデックスページの行の並び
    • 対応先のページIDの物理的な並び
  • ページIDが以下の場合、断片化が大きくなる。
    • 飛び飛びなページID
    • インデックの値の並びと逆順 etc.
  • IOアクセスの効率が悪くなり、パフォーマンスが劣化。

インデックスページの断片化

稼働後数カ月たち,データベースの応答性能が徐々に悪くなってきた | 日経クロステック(xTECH)

  • Bツリーのバランスに保てている場合、断片化が小さい。
  • Bツリーのバランスに崩れている場合、断片化が大きい。
  • バランスが崩れている場合...
    • 一部の階層が無駄に深くなる状態がある。
    • IOアクセスが必要になり、パフォーマンスが劣化。
  • 再構成により、ツリーのバランスを戻す必要がある。


確認用クエリ

断片化率・ページ密度

  • avg_fragmentation_in_percent:断片化率(%)
  • avg_page_space_used_in_percent:平均のページ密度
SELECT 
    DB_NAME(database_id) AS DB名, 
    OBJECT_NAME(object_id) AS オブジェクト名,
    index_id,
    index_type_desc,
    index_level,
    page_count,
    avg_fragmentation_in_percent,
    avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('SampleTable1'), NULL, NULL, 'DETAILED')
ORDER BY index_id, index_level DESC;


断片化の対処

再編成 (REORGANIZE)

  • リーフページの断片化のみを解消する。
  • ロック中のページはスキップされる。
  • 断片化がひどいと再構築より時間がかかる。
  • 処理中もテーブルにアクセスできる。
-- テーブル内の全インデックスを再編成
ALTER INDEX ALL ON テーブル名 REORGANIZE;
-- テーブル内の指定インデックスを再編成
ALTER INDEX インデックス名 ON テーブル名 REORGANIZE;

再構築 (REBUILD)

  • 新しくインデックス用の領域を生成し、そこで再構築する。
  • 古いインデックスは削除される。
  • なので、ページIDがまったく別物になる。
  • 断片化を完全に解消できる。
  • 処理中はテーブルにアクセスできない。
-- テーブル内の全インデックスを再構築
ALTER INDEX ALL ON テーブル名 REBUILD;
-- テーブル内の指定インデックスを再構築
ALTER INDEX インデックス名 ON テーブル名 REBUILD;

再編成の再構築の違い


50-50ページ分割

  • 追加・変更時、ページの変更が多発しないよう分割される。
  • インデックスの値を考慮し、50%・50%程度の配分で分割される。
  • ページ分割を繰り返すことにより、断片化率が大きくなる。
  • どこかのタイミングで再構成・再構築が必要。

データの追加

USE SampleDb;
CREATE TABLE SampleTable1 (Id INT PRIMARY KEY, Data1 VARCHAR (8000));  
GO

-- データ登録(ID2は後で登録)
DECLARE @Data VARCHAR (8000) = REPLICATE('x', 1000);
INSERT INTO SampleTable1 (id, Data1) VALUES (1, @Data);
--INSERT INTO SampleTable1 (id, Data1) VALUES (2, @Data);
INSERT INTO SampleTable1 (id, Data1) VALUES (3, @Data);
INSERT INTO SampleTable1 (id, Data1) VALUES (4, @Data);
INSERT INTO SampleTable1 (id, Data1) VALUES (5, @Data);
INSERT INTO SampleTable1 (id, Data1) VALUES (6, @Data);
INSERT INTO SampleTable1 (id, Data1) VALUES (7, @Data);

-- ページIDの確認
SELECT * FROM SampleTable1 CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%)

この後、ID2を登録。

INSERT INTO SampleTable1 (id, Data1) VALUES (2, REPLICATE('x', 1000));
SELECT * FROM SampleTable1 CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%)

ページ分割したことを確認。50-50ではなさそう。

データ変更

USE SampleDb;
CREATE TABLE SampleTable1 (Id INT PRIMARY KEY, Data1 VARCHAR (8000));  
GO

-- データ登録(ID2のデータサイズを後で変更)
DECLARE @Data VARCHAR (8000) = REPLICATE('x', 1000);
INSERT INTO SampleTable1 (id, Data1) VALUES (1, @Data);
INSERT INTO SampleTable1 (id, Data1) VALUES (2, @Data);
INSERT INTO SampleTable1 (id, Data1) VALUES (3, @Data);
INSERT INTO SampleTable1 (id, Data1) VALUES (4, @Data);
INSERT INTO SampleTable1 (id, Data1) VALUES (5, @Data);
INSERT INTO SampleTable1 (id, Data1) VALUES (6, @Data);

-- ページIDの確認
SELECT * FROM SampleTable1 CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%)

この後、ID2のデータサイズを変更。

-- 1000桁から2000桁にサイズ変更。
-- これで行サイズの上限を超え、ページ分割が発生する。
UPDATE SampleTable1 SET Data1 = REPLICATE('x', 2000) WHERE Id = 2;
SELECT * FROM SampleTable1 CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%)


Q&A

ページ密度とは違う?

多くのワークロードでは、ページ密度を上げる方が、断片化を減らすより、パフォーマンスが大幅に向上します。

  • 以下のスクリプトで、空き領域の断片化を上げてみた。
  • 断片化率が上がらないことを確認。
USE SampleDb;
CREATE TABLE SampleTable1 (Id INT PRIMARY KEY, Data1 VARCHAR (8000));  
GO

-- データ登録
DECLARE @Data VARCHAR (8000) = REPLICATE('x', 1000);
DECLARE @Count INT = 1;
WHILE @Count <= 1000 BEGIN
    INSERT INTO SampleTable1 (id, Data1) VALUES (@Count, @Data);
    SET @Count += 1;
END

-- 適当にレコードを間引く
DELETE FROM SampleTable1 WHERE Id LIKE '%1%'
DELETE FROM SampleTable1 WHERE Id LIKE '%3%'
DELETE FROM SampleTable1 WHERE Id LIKE '%5%'



以上