SE の雑記

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

tempdb の分割とその効果についての概要

leave a comment

ブログを書いている今日は 12/25 のクリスマスです。

ということでクリスマスらしく tempdb の分割について少し書いてみたいと思います。

tempdb の分割に関しては以下の記事がとても参考になります。
[FIX] tempdb データベースの同時実行制御の強化
Tempdb データベースは Microsoft SQL Server I/O サブシステムの要件
tempdb のパフォーマンスの最適化
I/Oボトルネックの病巣はこれで究明できる(1/3) − @IT
DB管理者がいますぐ確認すべき3つの設定 − @IT
A SQL Server DBA myth a day: (12/30) tempdb should always have one data file per processor core
TempDB – SQLServerPedia

■tempdb のデータファイル分割の効果


tempdb の分割の効果ですが、tempdb を使用している処理でページの競合回避で効果が出てきます。

この競合ですが SQL Server ではページラッチの競合として現れてきます。
以下のクエリを 100 ユーザーで実行してみます。

SET NOCOUNT ON
DECLARE @table TABLE(
Col1 int IDENTITY,
Col2 uniqueidentifier
)

DECLARE @i int = 0

WHILE (@i < 1000)
BEGIN
    INSERT INTO @table(Col2) VALUES (NEWID())
    SET @i += 1
END

現在は、tempdb のファイル数は 1 ファイルで設定しています。
image

テーブル変数にデータを格納しているクエリになります。
これを Enzo SQL Baseline を使用して複数のスレッドで実行します。
image

この時のページラッチの競合状態を確認すると以下のようになっています。
image

この時の待ち事象の瞬間の状態を [sys.dm_exec_requests] から取得してみると以下のようなデータが取得できました。

select
    wait_type,
    wait_resource,
    COUNT(*)
from
    sys.dm_exec_requests
where
    session_id > 50
    and
    wait_type IS NOT NULL
group by
    wait_type,
    wait_resource

image

それでは、次はファイルを分割してみたいと思います。
今回は HT 有効にして 8 コア環境のため、8 ファイルに分割しています。
# コア数が多くなってきた昨今では、1/2 ~ 1/4 の分割という情報も海外では出てきていますね。
image

この状態で、Enzo SQL Baseline で再度クエリを実行してみます。
image

ページラッチの競合が減っているのが確認できますね。

瞬間的に排他ページラッチがかかっているのはさほど変わらないですが、共有ラッチと更新ラッチについては待ちが減っていることが確認できますね。
image

PAGELATCH_UP は一般的には、システムアロケーションテーブル (PFS / GAM / SGAM 等) を更新する際の競合で発生すると言われており、tempdb のデータファイルを増やすことにより、使用されるデータファールのラウンドロビンによる分散がされ競合が少なくなるということが行われています。
使用されるデータファイルの判断にはデータファイルの空き領域が考慮されるため、均等なファイルサイズになっていないと使用されるデータファイルに偏りが発生して、負荷分散が思ったように動作しないことがあります。

すべての状況でこのような減少が確認できるとは限らないですが、tempdb の数を増やしたことによる競合の現象はこのような方法で確認することができます。

Written by masayuki.ozawa

12月 25th, 2011 at 4:09 pm

Posted in SQL Server

Tagged with

Leave a Reply

*