twitter で、DROP INDEX (Transact-SQL) の次の記載についてのつぶやきがあり、興味を持ったので、軽く動作確認をしてみた際の内容を。
128 以上のエクステントを持つインデックスを削除すると、データベース エンジンは、トランザクションがコミットされるまで実際のページの割り当て解除と関連するロックを遅らせます。
128 エクステントがマジックナンバーとなっているので、最初に 127 エクステントのインデックスを作成してみます。
DROP TABLE IF EXISTS T1;
CREATE TABLE T1(
C1 int identity primary key,
C2 char(100),
C3 varchar(36),
C4 datetime2(0) DEFAULT GETDATE(),
INDEX NCIX_T1_C2 (C2)
)
GO
SET NOCOUNT ON
GO
DECLARE @cnt int = 1
BEGIN TRAN
WHILE(@cnt <= 49565)
BEGIN
INSERT INTO T1(C2,C3) VALUES(NEWID(), NEWID())
SET @cnt += 1
END
COMMIT TRAN
GO
SELECT
COUNT(*)
FROM
(
SELECT
extent_page_id
FROM
sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID('T1'), 2, NULL, 'DETAILED')
GROUP BY
extent_page_id
) AS T
GO
このクエリを実行すると非クラスター化インデックス NCIX_T1_C2 については、127 エクステント使用しているインデックスとして作成が行われます。(実行タイミングによっては 127 を超える可能性もありますので、127 を超えていた場合は数回実行してみてください)
127 エクステント使用している状態で DROP INDEX を実行すると、DROP INDEX により実行されたトランザクションとしては次のようなトランザクションが発生します。
CHECKPOINT
BACKUP LOG DEMODB TO DISK=N'NUL'
SELECT COUNT(*) FROM sys.fn_dblog(NULL, NULL)
GO
DROP INDEX NCIX_T1_C2 ON T1
GO
DECLARE @transaction_id varchar(100) = (SELECT [Transaction ID] FROM sys.fn_dblog(NULL, NULL) WHERE [Transaction Name] = 'DROP INDEX')
SELECT
Operation, Context, COUNT(*) AS cnt
FROM
sys.fn_dblog(NULL, NULL)
WHERE
[Transaction ID] = @transaction_id
GROUP BY
Operation, Context
-- SELECT * FROM sys.fn_dblog(NULL, NULL) WHERE [Transaction ID] = @transaction_id
128 未満のエクステントのインデックスを DROP した場合、GAM / IAM / PFS というような、管理用のページの変更についても DROP INDEX のステートメント内で処理が行われています。
それでは 128 エクステントのインデックスになるようにループのカウンタを「50000」に変更して、129 エクステントのインデックスを作成してみます。(こちらも実行タイミングによっては 129 以上にならないことがありますので、超えていた場合は、数回実行してみてください)
今回のクエリでは、128 ちょうどになるようにすると、一部の管理用ページが 128 の変更にならないようなので、129 エクステント辺りを設定しておくとよいかと思います。
このエクステント数の場合、削除後の情報を確認すると次のようになります。
127 エクステントで削除をした場合は、GAM / IAM / PFS に対しての変更が、DROP INDEX のステートメント内の変更として実行されていましたが、エクステント数が多い場合には、これらのページに対しての変更については、DROP INDEX ステートメント内では実行されていないことが確認できます。
この動作が、冒頭に記載した
128 以上のエクステントを持つインデックスを削除すると、データベース エンジンは、トランザクションがコミットされるまで実際のページの割り当て解除と関連するロックを遅らせます。
の動作となります。
エクステント数にかかわらず、SQL Server のページの実領域の解放については、ゴースト クリーンアップ プロセス (ゴースト クリンナップ タスク) により実行が行われることは変わりませんが、管理用ページの変更タイミングについては、エクステント数によってタイミングが異なります。
128 エクステント以上の場合、GAM / IAM / PFS がどのようなタイミングで削除が行われるかというと、DROP INDEX のステートメントのコミット後にバックグラウンドで実行される「DeferredAllocUnitDrop::Process」というトランザクションにより、管理用ページの削除が行われます。
これは、次のクエリで確認ができます。
DECLARE @transaction_id varchar(100) = (SELECT [Transaction ID] FROM sys.fn_dblog(NULL, NULL) WHERE [Transaction Name] = 'DeferredAllocUnitDrop::Process')
SELECT
Operation, Context, COUNT(*) AS cnt
FROM
sys.fn_dblog(NULL, NULL)
WHERE
[Transaction ID] = @transaction_id
GROUP BY
Operation, Context
127 エクステントの場合、DROP INEX ステートメントのトランザクション内で、管理用ページの削除 (ビット変更) が行われていましたが、128 エクステントの以上の場合は、DeferredAllocUnitDrop::Process のトランザクション内で処理が行われています。
GAM / IAM に変更を行う際には、エクステントに対して、LOCK_X をかけているようなのですが、このロックをかけるタイミングを、ステートメント内のトランザクションではなく、ステートメントコミット後に実行されるトランザクションにより変更をかけるというのが、エクステント数の違いによる動作となるようです。