SQL Server では、-T1118 というトレースフラグを設定することで、小量のデータのテーブルに関しても混合エクステントではなく単一エクステントを使用するように設定ができます。
[FIX] tempdb データベースの同時実行制御の強化
今回は、このトレースフラグの有無で競合 (リソース待ち) 対象のページが変化することを見ていきたいと思います。
■競合を発生させるために
手っ取り早く競合を発生させるために SQL Server 2008 R2 で一時テーブルを使用して、複数のスレッドでデータの挿入を実施します。
SQL Server 2008 で tempdb を使用する処理で、SGAM の競合を発生させているので、SQL Server 使いの方は 「あれっ」 と思っている方もいらっしゃるかもしれないですね。
この辺はまだ調べ中なのでまとまり次第投稿したいと思っています。
SET NOCOUNT ON |
このクエリを Enzo SQL Baseline を使って複数スレッドで実行します。
# RML Utility の OStress でも可能ですが最近は SQL Baseline がお気に入りです。
■リソースの競合状態の取得
先ほどのクエリを複数スレッドで実行することで、複数のユーザーが tempdb を使用するというワークロードを発生させることが可能です。
このワークロードが実行されているときのでリソースの競合を確認するために、Enzo SQL Baseline を実行する前に SSMS で以下のクエリを実行した状態にします。
SET NOCOUNT ON DECLARE @tmp TABLE( DECLARE @i int WHILE (@i = 0) INSERT INTO SELECT @i = COUNT(*) FROM @tmp END SELECT * FROM @tmp |
[sys.dm_os_waiting_tasks] から tempdb の GAM (2:1:2) と SGAM (2:1:3) のリソース競合を取得し、競合が発生したらループを抜け結果を返すクエリとなっています。
[sys.dm_os_exec_request] の待ち事象を使用しても同様のことが実行できます。
# SQL Azure で試したい場合はこちらを使用するとよいかと。
このクエリを実行した状態で Enzo SQL Baseline を実行して、競合するページを確認してみます。
■トレースラグを設定していない状態
トレースフラグを設定していない状況の競合状態についてはこのようになります。
SGAM (2:1:3 ) に対して、[PAGELATCH_UP] の競合が発生し、待ち事象が発生したことが確認できます。
■トレースフラグを設定した状態
つぎにトレースフラグを設定した状態で確認を行います。
今回はテストなので [DBCC TRACEON(1118, -1)] を実行して、トレースフラグを有効にしています。
先ほどは、SGAM (2:1:3 ) に対して、[PAGELATCH_UP] の競合が発生し、待ち事象が発生していましたが、トレースフラグを設定することで混合エクステントではなく単一エクステントとなるため、GAM (2:1:2) に対して競合が発生していることが確認できます。
トレースフラグの設定により、競合しているページに変化があったのが確認できますね。
SQL Server 2008 以降はトレースフラグを設定しなくても、一時テーブル系は GAM になるかと思っていたのですが違うようでした。
SGAM の競合回避の最適化が行われているイメージみたいですね。
# この辺は別途検証中です。