SE の雑記

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

一時テーブルとテーブル変数使用時の tempdb の利用について

leave a comment

一時テーブルと (CREATE TABLE #table) と、テーブル変数 (DECLARE @table TABLE) で tempdb の利用有無に差があるかについて書いていなかったな~と思ったので軽くではありますが、まとめてみたいと思います。

■tempdb の利用有無について


どちらの場合も tempdb が利用されるというのが結論になります。

技術情報としては以下に記載されています。
tempdb に使用するディスク領域の計画
tempdb のディスク領域の不足に関するトラブルシューティング

テーブル値関数を使用して、Table-Valued Function プラン が生成された際にも tempdb が使用されています。

それでは実際にデータを取りながら確認してみたいと思います。

まずは一時テーブルから。
以下のクエリを実行して、一時テーブルにデータを挿入し、そのテーブルで使用されているファイルを取得しています。
今回、tempdb は 8 ファイルに分割しています。
# tempdb 以外のデータベースは 1 データファイルで構成しているので複数のデータファイルの情報については tempdb になります。

SET NOCOUNT ON
GO

CREATE TABLE #tmp(Col1 uniqueidentifier)
GO

INSERT INTO #tmp VALUES (NEWID())
GO 30000

SELECT
    SUBSTRING(sys.fn_PhysLocFormatter(%%physloc%%), 2, 1)
    , COUNT(*)
FROM
    #tmp
GROUP BY
    SUBSTRING(sys.fn_PhysLocFormatter(%%physloc%%), 2, 1)
ORDER BY
    1

DROP TABLE #tmp

実行結果がこちらになります。
image

1, 3~9 の 8 ファイルが使用されているのが確認できますね。
# 2 はログファイルになります。

それではテーブル変数で類似の処理を実行してみます。

SET NOCOUNT ON
GO

DECLARE @tmp TABLE(Col1 uniqueidentifier)
DECLARE @i int = 0

WHILE @i < 30000
BEGIN
    INSERT INTO @tmp VALUES (NEWID())
    SET @i += 1
END

SELECT
    SUBSTRING(sys.fn_PhysLocFormatter(%%physloc%%), 2, 1)
    , COUNT(*)
FROM
    @tmp
GROUP BY
    SUBSTRING(sys.fn_PhysLocFormatter(%%physloc%%), 2, 1)
ORDER BY
    1

実行結果がこちら。
image

 

どちらも tempdb が使用されているのが確認できますね。

一時テーブルとテーブル変数ではインデックスの作成有無やコンパイルへの影響が異なってきますので、どちらを使用するかは状況に応じて検討する必要がありますが、どちらも tempdb が使われるという状況は変わりません。
テーブル変数は変数だからメモリ上に確保される雰囲気がありますが、tempdb が使用されていますので使用する場合はディスクの負荷について考慮したほうがよさそうですね。

Written by masayuki.ozawa

10月 14th, 2011 at 7:55 pm

Posted in SQL Server

Tagged with

Leave a Reply

*