SE の雑記

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

SQL Azure のデータベースのファイル構成を想像してみる -tempdb 編-

leave a comment

SQL Azure のデータベースのファイル構成を想像してみる の派生で、今回はユーザーデータベースではなく tempdb について想像してみたいと思います。
前回に引き続き今回も想像です。

■前回と同じ方法で取得できる?


まずは、前回使用したクエリを一時テーブルに書き換えて実行してみたいと思います。
# ループ回数の変更とロック取得時のページ数取得を追加しています。

SET NOCOUNT ON

CREATE TABLE [dbo].[#Table_1](
    [Col1] [uniqueidentifier] NOT NULL,
    [Col2] [int] NULL,
    [Col3] [char](4500) NULL,
CONSTRAINT [PK_#Table_1] PRIMARY KEY CLUSTERED
([Col1] ASC)
)

BEGIN TRAN
DECLARE @i int = 0
WHILE (@i < 100)
BEGIN
    INSERT INTO #Table_1 VALUES(NEWID(), @i , NEWID())
    SET @i += 1
END

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRAN
DELETE FROM #Table_1

SELECT DISTINCT LEFT(resource_description,PATINDEX(‘%:%’,resource_description) -1) , COUNT(*)
FROM sys.dm_tran_locks
WHERE resource_type = ‘PAGE’
GROUP BY
LEFT(resource_description,PATINDEX(‘%:%’,resource_description) -1)
ORDER BY 1

ROLLBACK TRAN

DROP TABLE [dbo].[#Table_1]
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

image

前回と同じクエリでは情報が取得できませんでした…。

SQL Server 2008 R2 で実行した結果がこちら。
image

SQL Azure も SQL Server 2008 R2 も同様の方法では取得できないみたいですね。

 

■ラッチ WAIT から見てみる


ロックがだめならラッチから見てみたいと思います。

まずは、[sys.dm_exec_requests] の情報を一時テーブルに取得するクエリを実行したままの状態にします。
この時に気を付ける必要があるのが、[データベースはユーザーデータベースを選択した状態にする] ということです。
[master] を選択した状態だと思ったような結果が取得できません。

SET NOCOUNT ON
CREATE TABLE #tmp(Col1 nvarchar(100), Col2 nvarchar(100))

WHILE (0=0)
BEGIN
INSERT INTO #tmp
SELECT wait_type, wait_resource
FROM sys.dm_exec_requests
WHERE wait_type IS NOT NULL AND wait_resource <>”
END

 

このクエリを実行しておくことで、[sys.dm_exec_requests] から待ち事象を取得して、一時テーブルに格納することができます。

このクエリを実行した状態でもう一つクエリウィンドウを開いて以下のクエリを実行します。
この時選択するデータベースは [上記のクエリを実行した際に選択したユーザーデータベースと同じ] にします。

SET NOCOUNT ON

CREATE TABLE [dbo].[#Table_1](
    [Col1] [uniqueidentifier] NOT NULL,
    [Col2] [int] NULL,
    [Col3] [char](3000) NULL,
CONSTRAINT [PK_#Table_1] PRIMARY KEY CLUSTERED
([Col1] ASC)
)

DECLARE @i int = 0
WHILE (@i < 400000)
BEGIN
    INSERT INTO #Table_1 VALUES(NEWID(), @i , NEWID())
    SET @i += 1
END

ALTER INDEX PK_#Table_1 ON  #Table_1 REBUILD
UPDATE #Table_1 SET Col1 = NEWID()

DELETE FROM #Table_1

DROP TABLE [dbo].[#Table_1]

 

データ整列後に主キーを更新したり、データを全件削除したりして、PAGELATCH と PAGEIOLATCH を発生させています。
このクエリの実行が完了したら先ほど実行したままにしていた 、[sys.dm_exec_requests] の情報を取得するクエリを終了 (SSMS のクエリ実行の取り消しをクリック) させます。

クエリを終了させたら、、[sys.dm_exec_requests] を実行していたクエリウィンドウで以下のクエリを実行します。
先ほど取得した一時テーブルの情報から、tempdb (DBID = 2) に対して発生した待ち事象を取得しています。

SELECT DISTINCT LEFT(COl2,PATINDEX(‘2:%’,Col2)+3)
FROM #tmp
WHERE LEFT(COl2,PATINDEX(‘2:%’,Col2)) = 2
ORDER BY 1

そうすると以下のような情報が取得できます。
image

このデータですが [DBID : ファイル ID] となっています。
ここから tempdb はファイルID 3 ~ 12 までの 10 ファイルで構成されているようであるということが取得できます。

2 はログファイルだと思うのですが。1 が無いのがちょっと気になりますね。
PRIMARY ファイルグループに 1 のデータファイルだけ配置して、3~12 を他のファイルグループとして設定し、デフォルトのファイルグループとしているのかもしれませんね~。

前回調べたデータベースのファイルと同様 tempdb も 10 個 (もしかしたら 11 個) のデータファイルで構成されているようですね。
SQL Azure は tempdb もファイルを分割して I/O の効率を高めていそうです。

Written by masayuki.ozawa

6月 9th, 2011 at 10:26 pm

Leave a Reply

*