SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

-T1118 の有無による競合ページの違いを確認してみる

leave a comment

SQL Server では、-T1118 というトレースフラグを設定することで、小量のデータのテーブルに関しても混合エクステントではなく単一エクステントを使用するように設定ができます。
[FIX] tempdb データベースの同時実行制御の強化

今回は、このトレースフラグの有無で競合 (リソース待ち) 対象のページが変化することを見ていきたいと思います。


■競合を発生させるために


手っ取り早く競合を発生させるために SQL Server 2008 R2 で一時テーブルを使用して、複数のスレッドでデータの挿入を実施します。
SQL Server 2008 で tempdb を使用する処理で、SGAM の競合を発生させているので、SQL Server 使いの方は 「あれっ」 と思っている方もいらっしゃるかもしれないですね。
この辺はまだ調べ中なのでまとまり次第投稿したいと思っています。

SET NOCOUNT ON
CREATE TABLE #tmp(
Col1 uniqueidentifier
)
DECLARE @i int
SET @i = 0
WHILE (@i <= 100000)
BEGIN
    INSERT INTO #tmp VALUES (NEWID())
    SET @i = @i + 1
END

このクエリを Enzo SQL Baseline を使って複数スレッドで実行します。
# RML Utility の OStress でも可能ですが最近は SQL Baseline がお気に入りです。

image

 

■リソースの競合状態の取得


先ほどのクエリを複数スレッドで実行することで、複数のユーザーが tempdb を使用するというワークロードを発生させることが可能です。

このワークロードが実行されているときのでリソースの競合を確認するために、Enzo SQL Baseline を実行する前に SSMS で以下のクエリを実行した状態にします。

SET NOCOUNT ON
GO

DECLARE @tmp TABLE(
    Col1 nvarchar(255),
    Col2 nvarchar(255)
)

DECLARE @i int
SET @i = 0

WHILE (@i = 0)
BEGIN

INSERT INTO
    @tmp
SELECT
    wait_type,
    resource_description
FROM
    sys.dm_os_waiting_tasks
WHERE
    resource_description = ‘2:1:2’
    OR
    resource_description = ‘2:1:3’

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 を実行して、競合するページを確認してみます。

 

■トレースラグを設定していない状態


トレースフラグを設定していない状況の競合状態についてはこのようになります。

image

SGAM (2:1:3 ) に対して、[PAGELATCH_UP] の競合が発生し、待ち事象が発生したことが確認できます。

■トレースフラグを設定した状態


つぎにトレースフラグを設定した状態で確認を行います。

今回はテストなので [DBCC TRACEON(1118, -1)] を実行して、トレースフラグを有効にしています。

image

先ほどは、SGAM (2:1:3 ) に対して、[PAGELATCH_UP] の競合が発生し、待ち事象が発生していましたが、トレースフラグを設定することで混合エクステントではなく単一エクステントとなるため、GAM (2:1:2) に対して競合が発生していることが確認できます。

トレースフラグの設定により、競合しているページに変化があったのが確認できますね。
SQL Server 2008 以降はトレースフラグを設定しなくても、一時テーブル系は GAM になるかと思っていたのですが違うようでした。
SGAM の競合回避の最適化が行われているイメージみたいですね。
# この辺は別途検証中です。

Written by masayuki.ozawa

7月 4th, 2011 at 8:11 am

Posted in SQL Server

Tagged with

Leave a Reply

*