SE の雑記

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

インデックス再構築時の SORT_IN_TEMPDB の挙動を確認

leave a comment

インデックスの作成や再構築の際にソート領域がメモリでは足りなくなった場合にインデックスを操作するデータベースのデータ領域ではなく、tempdb をソート領域として使用するためのオプションに SORT_IN_TEMPDB があります。
CREATE INDEX (Transact-SQL)
ALTER INDEX (Transact-SQL)

この SORT_IN_TEMPDB の挙動を少し確認してみたいと思います。

インデックスの作成ですが ALTER TABLE で [ADD CONSTRAINT <プライマリキー> PRIMARY KEY CLUSTERED] を使用してクラスター化インデックスとして設定されたプライマリキーを設定する際に作成することもできますが、この場合には SORT_IN_TEMPDB は使えなかったはずです。
ALTER TABLE (Transact-SQL)

SORT_IN_TEMPDB はメモリ内でソートが行えなかった場合に tempdb でソートを行うためのオプションとなります。

以下のようなサイズのテーブルがあります。
image

この中でサイズが大きい [LINEITEM] にインデックスを作成してみたいと思います。

インデックスを作成する際には以下のようなクエリを実行しています。

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
CHECKPOINT
GO

SELECT
    DbId
    , ‘Data’ As Type
    , SUM(NumberReads) AS NumberReads
    , SUM(BytesRead) AS BytesRead
    , SUM(IoStallReadMS) AS IoStallReadMS
    , SUM(NumberWrites) AS NumberWrites
    , SUM(BytesWritten) AS BytesWritten
    , SUM(IoStallWriteMS) AS IoStallWriteMS
    , SUM(IoStallMS) AS IoStallMS
FROM sys.fn_virtualfilestats(NULL, NULL) where DbId IN (2, 5) and FileId <> 2
GROUP BY DbId
UNION
SELECT
    DbId
    , ‘Log’ As Type
    , SUM(NumberReads) AS NumberReads
    , SUM(BytesRead) AS BytesRead
    , SUM(IoStallReadMS) AS IoStallReadMS
    , SUM(NumberWrites) AS NumberWrites
    , SUM(BytesWritten) AS BytesWritten
    , SUM(IoStallWriteMS) AS IoStallWriteMS
    , SUM(IoStallMS) AS IoStallMS
FROM sys.fn_virtualfilestats(NULL, NULL) where DbId IN (2, 5) and FileId = 2
GROUP BY DbId
GO

CREATE UNIQUE CLUSTERED INDEX [CIX_LINEITEM] ON [dbo].[LINEITEM]
(
    [L_ORDERKEY] ASC,
    [L_PARTKEY] ASC,
    [L_SUPPKEY] ASC,
    [L_LINENUMBER] ASC
)WITH (DATA_COMPRESSION = PAGE, SORT_IN_TEMPDB = ON, MAXDOP = 0) ON [PRIMARY]
GO

CHECKPOINT
GO

SELECT
    DbId
    , ‘Data’ As Type
    , SUM(NumberReads) AS NumberReads
    , SUM(BytesRead) AS BytesRead
    , SUM(IoStallReadMS) AS IoStallReadMS
    , SUM(NumberWrites) AS NumberWrites
    , SUM(BytesWritten) AS BytesWritten
    , SUM(IoStallWriteMS) AS IoStallWriteMS
    , SUM(IoStallMS) AS IoStallMSf
FROM sys.fn_virtualfilestats(NULL, NULL) where DbId IN (2, 5) and FileId <> 2
GROUP BY DbId
UNION
SELECT
    DbId
    , ‘Log’ As Type
    , SUM(NumberReads) AS NumberReads
    , SUM(BytesRead) AS BytesRead
    , SUM(IoStallReadMS) AS IoStallReadMS
    , SUM(NumberWrites) AS NumberWrites
    , SUM(BytesWritten) AS BytesWritten
    , SUM(IoStallWriteMS) AS IoStallWriteMS
    , SUM(IoStallMS) AS IoStallMS
FROM sys.fn_virtualfilestats(NULL, NULL) where DbId IN (2, 5) and FileId = 2
GROUP BY DbId
GO

インデックスの作成前後に tempd のアクセス状況を確認するためにデータベースのファイル I/O を出力するようにしていますのでインデックスの作成で tempdb が使われたかを確認することができます。

最初に [SORT_IN_TEMPDB=ON] の状態でインデックスを作成してみます。
image

SORT_IN_TEMPDB はディスクでソートが必要になった際に tempdb を使用してソート行います。
実行前は [17,555,841,024] だった Bytes Written が実行後は [35,103,670,272] になっていますのでインデックスの差作成時には、[17,547,829,248] (17.5GB) の書き込みが tempdb に対して行われています。

それでは、[SORT_IN_TEMPDB=OFF] でインデックスを作成した場合を見てみます。
image

実行前は [35,103,678,464]、実行後は [35,114,049,536] ですので [10,371,072] (10MB) tempdb が使用されたことになります。
SORT_IN_TEMPDB=ON の場合と比較して tempdb の利用率が大幅に減っていますね。

データを格納している DB の書き込み状況に関しては ON の場合は [13,808,132,096] (14GB)、OFF の場合は [31,371,403,264] (31GB) ですので、OFF にすることでテーブルを格納しているデータ領域が多く使用されていることが確認できます。

SORT_IN_TEMPDB を使用するメリットとしては、

  • tempdb を高速なストレージに配置している場合、ソートの操作を高速化できる
  • データ領域とは別の領域でソートすることにより、作成されたインデックスを連続した領域で配置することができる

というようなことが挙げられます。
シーケンシャルに読み込む場合には連続した領域でデータを配置することが重要になってきますので SORT_IN_TEMPDB を使用してデータを連続した配置にできるようにすることがポイントになることがあります。

それでは、インデックスの再構築の際に SORT_IN_TEMPDB を使用するとどうなるかを見ていきたいと思います。
先ほど使用したクエリの CREATE INDEX の部分を以下のように変更して実行してみます。

ALTER INDEX [CIX_LINEITEM] ON [dbo].[LINEITEM] REBUILD PARTITION = ALL
WITH (SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE, ONLINE=OFF)
GO

 

まずは SORT_IN_TEMPDB を OFF にして実行してみます。
image

データを格納しているデータベースの書き込みバイト数が、実行前が [74,119,766,016]、実行後が [60,311,945,216] ですので、[13,807,820,800] の書き込みが行われていることが確認できます。
なお、読み取りに関しては [13,813,088,256] ですので読み取りと書き込みのバイト数はほぼ同じぐらい行われていることが確認できますね。
インデックスの再構築は既存のインデックスを新規の領域に再作成する動作になりますので、

  • 既存データを読む
  • 新規データとして書き込む

の操作が行われていることがここから確認できますね。

それでは、SORT_IN_TEMPDB を ON にしてインデックスの再構築を実行してみます。
image

データを格納しているデータベースの書き込みに関しては [13,807,558,656]、読み取りに関しては [13,814,693,888] 行われていることが確認できます。
それでは tempdb のアクセス数はどうでしょう。

書き込み回数 (Nuber Writes) は上昇していないため、tempdb にデータが書き込まれていないことが確認できます。
インデックスが作成されているデータは既にソートが行われているデータになります。
そのため、ソートは行われないため SORT_IN_TEMPDB の有無による tempdb の利用状況の差は発生していません。
# オプションが使えるので何かの場合には tempdb でソートがされるのかもしれませんが、基本的にはソート済みのデータを再構築するため、tempdb の利用はないかと思いますが。

今回はインデックスの作成や再構築の際に tempdb が使われるかを調べてみましたが、他の操作でも上記のようなクエリを使用することで tempdb がどのくらい使用されたかを確認することができますので、気になるときには汎用的に使えるかと思います。

Written by masayuki.ozawa

3月 11th, 2013 at 11:26 pm

Posted in SQL Server

Tagged with

Leave a Reply

*