非クラスター化インデックスのキー項目を変更した場合、どのようなオーバーヘッドが発生するかについてまとめておきたいと思います。
テスト用データの作成
最初に、次のようなクエリを実行して、テスト用のデータを作成します。
DROP TABLE IF EXISTS IndexTest
GO
CREATE TABLE IndexTest(
C1 int identity not null,
C2 int,
C3 char(36),
CONSTRAINT PK_IndexTest PRIMARY KEY CLUSTERED(C1),
INDEX NCIX_IndexTest_01 NONCLUSTERED(C2) INCLUDE(C3)
)
GO
SET NOCOUNT ON
GO
BEGIN TRAN
DECLARE @cnt int = 1
WHILE (@cnt <= 100)
BEGIN
INSERT INTO IndexTest VALUES(@cnt % 2, NEWID())
SET @cnt += 1
END
COMMIT TRAN
GO
テーブルの構成はシンプルで、C1 に Identity のプライマリキーを持ち、C2 を非クラスター化インデックスとして、C3 が INCLUDE 項目として指定されています。
このデータに対して C2 (非クラスター化インデックスのキー項目) を変更した場合のオーバーヘッドについて考えてみます。
非クラスター化インデックスのキー項目変更時のオーバーヘッドを確認する
テスト用のデータが生成できたので、実際にオーバーヘッドを確認していきたいと思います。
最初に、非クラスター化インデックスのデータの状態を確認してみます。
次のようなクエリを実行することで、インデックスのページを確認することができます。
SELECT
OBJECT_NAME(p.object_id) AS object_name,
p.index_id,
iau.first_page,
CAST(CONVERT(varbinary(2), '0x' + SUBSTRING(CONVERT(varchar(14), iau.first_page, 1), 13,2) + SUBSTRING(CONVERT(varchar(14), iau.first_page, 1), 11,2), 1) AS int) AS file_id,
CAST(CONVERT(varbinary(4), '0x' + SUBSTRING(CONVERT(varchar(14), iau.first_page, 1), 9,2) + SUBSTRING(CONVERT(varchar(14), iau.first_page, 1), 7,2) +
SUBSTRING(CONVERT(varchar(14), iau.first_page, 1), 5,2) + SUBSTRING(CONVERT(varchar(14), iau.first_page, 1), 3,2), 1) AS int) AS page_id,
iau.root_page,
iau.first_iam_page
FROM sys.system_internals_allocation_units AS iau
INNER JOIN sys.partitions AS p ON iau.container_id = p.hobt_id
WHERE
p.object_id = OBJECT_ID('IndexTest')
GO
今回は、非クラスター化インデックスを変更しますので、ページ ID「1182456」の情報を確認してみます。
DBCC TRACEON(3604)
DBCC PAGE('TESTDB', 1, 1182456,3)
GO
実際のインデックスのデータページを確認すると次のようなデータ構造となっていることが確認できます。
今回は赤枠のデータ (C1 = 2) の C2 のデータを、次のクエリで変更してみます。
UPDATE IndexTest SET C2 = 1 WHERE C1 = 2 GO
先ほどまでは、C1 = 2 のデータは Row=0 として格納されていましたが、C2 を 1 に変更することでデータの場所が移動され、Row=51 に移動していることが確認できました。
この時の、非クラスター化インデックスの内部的な動作ですが、DELETE -> INSERT により、非クラスター化インデックスのレコードが生成されます。
トランザクションログとして生成されたレコードを確認してみましょう。
SELECT [Current LSN], Operation, Context, AllocUnitName, [Transaction Name] FROM sys.fn_dblog(NULL, NULL) GO
次の画像の赤枠の箇所が DELETE -> INSERT を行っているログレコードとなります。
非クラスター化インデックスに対して DELETE -> INSERT を行っていることが確認できますね。
非クラスター化インデックスの INCLUDE 項目のみを更新した場合はどうでしょうか？
UPDATE IndexTest SET C3 = NEWID() WHERE C1 = 2 GO
INCLUDE の項目を変更した場合には、インデックスに対しての修正のみが発生していることが確認できますね。
INCLUDE の項目の変更については、キー項目の変更よりはオーバーヘッドが少なく処理が実行できています。
まとめ
クラスター化インデックスのキー項目とは異なり、非クラスター化インデックスのキー項目は通常のデータに対してインデックスを設定することになるため、キー項目の変更は実際のワークロードでも発生する可能性は高いかと思います。
しかし、非クラスター化インデックスのキー項目を変更した場合には、新しいインデックスレコードの生成が行われるため、キー項目の変更については、オーバーヘッドが大きい処理となります。
非クラスター化インデックスのインデックス長 (インデックスのキー項目ならびに INCLUDE 項目) が大きい場合、キー項目が変更された際の、インデックスの INSERT コストが大きくなる (キー項目変更後のインデックスの INSERT で移動されるデータが多い) はずですので、インデックスに含まれる項目と、キー項目の更新頻度はインデックスを設定する際に、少しは意識しておいた方がよいのではないでしょうか。