SQL Serverの一時テーブルとテーブル変数の違いは何ですか?


404

これはかなりの数の神話と矛盾する見解を持つ領域のようです。

では、SQL Serverのテーブル変数とローカルの一時テーブルの違いは何ですか?

+4

スタックオーバーフローに関する質問:[SQL Serverでテーブル変数とテンポラリテーブルをいつ使用すべきですか?](http://stackoverflow.com/q/11857789/73226) 07 7月. 162016-07-07 04:30:51

618

内容

Contents

警告

この回答では、SQL Server 2000で導入された「古典的な」テーブル変数について説明します。SQL Server 2014のメモリ内OLTPでは、メモリ最適化テーブルタイプが導入されました。それらのテーブル変数インスタンスは、以下で説明するものと多くの点で異なります。(more details

ストレージの場所

変わりはない。両方ともに格納されていますtempdb

私はそれがテーブル変数のためにこれがいつもそうであるというわけではないことを提案したのを見ました、しかしこれは以下から確かめられることができます

DECLARE @T TABLE(X INT)

INSERT INTO @T VALUES(1),(2)

SELECT sys.fn_PhysLocFormatter(%%physloc%%) AS [File:Page:Slot]
FROM @T

結果の例(場所をtempdb2行が格納されます)

File:Page:Slot
----------------
(1:148:0)
(1:148:1)

論理的な場所

@table_variablesあたかも現在のデータベースの一部であるかのように振る舞います。#tempテーブルはありません。テーブル変数の場合(2005年以降)、列の照合は明示的に指定されていない場合は現在のデータベースの照合となります。#tempデフォルトの照合順序を使用するテーブルtempdbMore details)さらに、ユーザー定義のデータ型とXMLコレクションを使用するにはtempdbに含まれている必要があります。#tempテーブルだがテーブル変数は現在のデータベースからそれらを使用することができます(Source

SQL Server 2012では、包含データベースが導入されています。the behavior of temporary tables in these differs(h/t Aaron)

包含データベースでは、一時表データは包含データベースの照合順序で照合されます。

  • 一時テーブルに関連付けられているすべてのメタデータ(テーブル名、列名、インデックスなど)はカタログ照合に含まれます。
  • 名前付き制約は一時テーブルでは使用できません。
  • 一時テーブルは、ユーザー定義型、XMLスキーマコレクション、またはユーザー定義関数を参照できません。

さまざまなスコープへの可視性

@table_variablesそれらが宣言されているバッチおよびスコープ内でのみアクセスできます。#temp_tables子バッチ内でアクセス可能です(ネストされたトリガ、プロシージャ、exec呼び出します。#temp_tables外側のスコープで作成されます(@@NESTLEVEL=0)セッションが終了するまで持続するため、)バッチをまたぐことができます。ただし、次に説明するように、どちらの種類のオブジェクトも子バッチ内で作成して呼び出し側のスコープ内でアクセスすることはできません(global##tempテーブルできるでも)。

一生

@table_variablesが含まれているバッチに暗黙的に作成されるDECLARE @.. TABLEステートメントは実行され(そのバッチ内のユーザーコードが実行される前に)、最後に暗黙的に削除されます。

パーサーはあなたがしようとする前にテーブル変数を使用することはできませんがDECLARE暗黙の創造を下に見ることができるという声明。

IF (1 = 0)
BEGIN
DECLARE @T TABLE(X INT)
END

--Works fine
SELECT *
FROM @T

#temp_tablesTSQLが発生すると明示的に作成されます。CREATE TABLEステートメントが見つかり、明示的にドロップすることができますDROP TABLEまたは、バッチの終了時に暗黙的に削除されます(次のように子バッチに作成された場合)。@@NESTLEVEL > 0またはセッションが終了したとき

注意:ストアドルーチン内で両方の種類のオブジェクトcan be cached新しいテーブルを繰り返し作成および削除するのではなく。このキャッシュがいつ発生する可能性があるかについては制限があります。#temp_tablesしかし、その制限は@table_variablesとにかく防ぐ。キャッシュのメンテナンスオーバーヘッド#tempテーブルはちょっとテーブル変数よりも大きいas illustrated here

オブジェクトメタデータ

これは、どちらの種類のオブジェクトでも基本的に同じです。システムベーステーブルに保存されます。tempdb。のために見ることはもっと簡単です#tempしかしテーブルとしてOBJECT_ID('tempdb..#T')システムテーブルにキー入力するために使用することができ、内部的に生成された名前は、で定義された名前とより密接に関連しています。CREATE TABLEステートメント。テーブル変数の場合object_id関数が機能せず、内部名が完全にシステム生成され、変数名とは無関係です。以下は、メタデータがまだ(おそらくユニークな)カラム名をキー入力することによってまだ存在していることを示しています。一意の列名がないテーブルの場合、object_idは次のようにして決定できます。DBCC PAGEそれらが空でない限り。

/*Declare a table variable with some unusual options.*/
DECLARE @T TABLE
(
[dba.se] INT IDENTITY PRIMARY KEY NONCLUSTERED,
A INT CHECK (A > 0),
B INT DEFAULT 1,
InRowFiller char(1000) DEFAULT REPLICATE('A',1000),
OffRowFiller varchar(8000) DEFAULT REPLICATE('B',8000),
LOBFiller varchar(max) DEFAULT REPLICATE(cast('C' as varchar(max)),10000),
UNIQUE CLUSTERED (A,B) 
     WITH (FILLFACTOR = 80, 
        IGNORE_DUP_KEY = ON, 
        DATA_COMPRESSION = PAGE, 
        ALLOW_ROW_LOCKS=ON, 
        ALLOW_PAGE_LOCKS=ON)
)

INSERT INTO @T (A)
VALUES (1),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13)

SELECT t.object_id,
       t.name,
       p.rows,
       a.type_desc,
       a.total_pages,
       a.used_pages,
       a.data_pages,
       p.data_compression_desc
FROM  tempdb.sys.partitions AS p
       INNER JOIN tempdb.sys.system_internals_allocation_units AS a
        ON p.hobt_id = a.container_id
       INNER JOIN tempdb.sys.tables AS t
        ON t.object_id = p.object_id
       INNER JOIN tempdb.sys.columns AS c
        ON c.object_id = p.object_id
WHERE c.name = 'dba.se'

出力

Duplicate key was ignored.

    +-----------+-----------+------+-------------------+-------------+------------+------------+-----------------------+
| object_id |  name  | rows |   type_desc   | total_pages | used_pages | data_pages | data_compression_desc |
+-----------+-----------+------+-------------------+-------------+------------+------------+-----------------------+
| 574625090 | #22401542 |  13 | IN_ROW_DATA    |      2 |     2 |     1 | PAGE         |
| 574625090 | #22401542 |  13 | LOB_DATA     |     24 |     19 |     0 | PAGE         |
| 574625090 | #22401542 |  13 | ROW_OVERFLOW_DATA |     16 |     14 |     0 | PAGE         |
| 574625090 | #22401542 |  13 | IN_ROW_DATA    |      2 |     2 |     1 | NONE         |
+-----------+-----------+------+-------------------+-------------+------------+------------+-----------------------+

トランザクション

上の操作@table_variables外部のユーザートランザクションとは無関係にシステムトランザクションとして実行されます。#tempテーブル操作は、ユーザートランザクション自体の一部として実行されます。このため、ROLLBACKコマンドは#tempテーブルは@table_variable手つかずの

DECLARE @T TABLE(X INT)
CREATE TABLE #T(X INT)

BEGIN TRAN

INSERT #T
OUTPUT INSERTED.X INTO @T
VALUES(1),(2),(3)

/*Both have 3 rows*/
SELECT * FROM #T
SELECT * FROM @T

ROLLBACK

/*Only table variable now has rows*/
SELECT * FROM #T
SELECT * FROM @T
DROP TABLE #T

ロギング

両方ともログレコードを生成します。tempdbトランザクションログよくある誤解は、テーブル変数の場合はそうではないということです。したがって、これを示すスクリプトでは、テーブル変数を宣言し、行をいくつか追加してからそれらを更新して削除します。

テーブル変数はバッチの開始時と終了時に暗黙的に作成および削除されるため、フルロギングを確認するには複数のバッチを使用する必要があります。

USE tempdb;

/*
Don't run this on a busy server.
Ideally should be no concurrent activity at all
*/
CHECKPOINT;

GO

/*
The 2nd column is binary to allow easier correlation with log output shown later*/
DECLARE @T TABLE ([C71ACF0B-47E9-4CAD-9A1E-0C687A8F9CF3] INT, B BINARY(10))

INSERT INTO @T
VALUES (1, 0x41414141414141414141), 
       (2, 0x41414141414141414141)

UPDATE @T
SET  B = 0x42424242424242424242

DELETE FROM @T

/*Put allocation_unit_id into CONTEXT_INFO to access in next batch*/
DECLARE @allocId BIGINT, @Context_Info VARBINARY(128)

SELECT @Context_Info = allocation_unit_id,
       @allocId = a.allocation_unit_id 
FROM  sys.system_internals_allocation_units a
       INNER JOIN sys.partitions p
        ON p.hobt_id = a.container_id
       INNER JOIN sys.columns c
        ON c.object_id = p.object_id
WHERE ( c.name = 'C71ACF0B-47E9-4CAD-9A1E-0C687A8F9CF3' )

SET CONTEXT_INFO @Context_Info

/*Check log for records related to modifications of table variable itself*/
SELECT Operation,
       Context,
       AllocUnitName,
       [RowLog Contents 0],
       [Log Record Length]
FROM  fn_dblog(NULL, NULL)
WHERE AllocUnitId = @allocId

GO

/*Check total log usage including updates against system tables*/
DECLARE @allocId BIGINT = CAST(CONTEXT_INFO() AS BINARY(8));

WITH T
      AS (SELECT Operation,
           Context,
           CASE
            WHEN AllocUnitId = @allocId THEN 'Table Variable'
            WHEN AllocUnitName LIKE 'sys.%' THEN 'System Base Table'
            ELSE AllocUnitName
           END AS AllocUnitName,
           [Log Record Length]
        FROM  fn_dblog(NULL, NULL) AS D)
SELECT Operation = CASE
              WHEN GROUPING(Operation) = 1 THEN 'Total'
              ELSE Operation
             END,
       Context,
       AllocUnitName,
       [Size in Bytes] = COALESCE(SUM([Log Record Length]), 0),
       Cnt = COUNT(*)
FROM  T
GROUP BY GROUPING SETS( ( Operation, Context, AllocUnitName ), ( ) )
ORDER BY GROUPING(Operation),
        AllocUnitName 

返品

詳細ビュー

Screenshot of results

サマリービュー(暗黙的なドロップテーブルとシステムベーステーブルのログ記録を含む)

Screenshot of results

As far as I've been able to discern両方の操作で、ほぼ同量のロギングが生成されます。

その間ロギングの重要性は非常に似ていますが、重要な違いの1つは、関連するログレコードが#temp含まれているユーザートランザクションが完了するまでテーブルを消去できないため、長期にわたるトランザクションがある時点で書き込まれます。#tempテーブルは、ログの切り捨てを防ぎます。tempdb一方、テーブル変数に対して生成された自律型トランザクションはそうではありません。

テーブル変数はサポートしていませんTRUNCATEそのため、テーブルからすべての行を削除する必要がある場合は、ログ記録に不利になることがあります(非常に小さいテーブルの場合でも)。DELETEcan work out better anyway

基数

テーブル変数を含む実行計画の多くは、それらからの出力として推定された単一行を表示します。テーブル変数のプロパティを調べると、SQL Serverはテーブル変数にゼロrows(1行が0行のテーブルから出力されると推定される理由は@Paul Whiteによって説明されていますhere

ただし、前のセクションで示した結果は正確な結果を示しています。rows数えるsys.partitions。問題は、ほとんどの場合、テーブルが空の間にテーブル変数を参照するステートメントがコンパイルされることです。文が後に(再)コンパイルされた場合@table_variableこれは代わりに表の基数に使用されます(これは明示的なrecompileあるいは、ステートメントが、遅延コンパイルまたは再コンパイルを引き起こす別のオブジェクトも参照しているためです。)

DECLARE @T TABLE(I INT);

INSERT INTO @T VALUES(1),(2),(3),(4),(5)

CREATE TABLE #T(I INT)

/*Reference to #T means this statement is subject to deferred compile*/
SELECT * FROM @T WHERE NOT EXISTS(SELECT * FROM #T)

DROP TABLE #T

遅延コンパイル後のPlanの正確な推定行数が表示されます。

Shows accurate row count

SQL Server 2012 SP2では、トレースフラグ2453が導入されています。詳細は "リレーショナルエンジン"の下にありますhere

このトレースフラグを有効にすると、自動再コンパイルでカーディナリティの変更が考慮されるようになります。

列統計なし

より正確なテーブルカーディナリティを持っていても、推定された行数がより正確になるわけではありません(テーブル内のすべての行に対して操作を実行しない限り)。SQL Serverはテーブル変数の列統計をまったく保持していないため、比較述語に基づいて推測に頼ります(たとえば、10%のテーブルが返されます)。=一意でない列に対しては30%、>比較)。対照的に、列統計ありますのために維持#tempテーブル

SQL Serverは各列に加えられた変更の数のカウントを維持します。計画のコンパイル以降の変更の数が再コンパイルしきい値(RT)を超えると、計画は再コンパイルされ、統計が更新されます。RTはテーブルの種類とサイズによって異なります。

からPlan Caching in SQL Server 2008

RTは以下のように計算されます。(nは、クエリプランがコンパイルされたときのテーブルの基数を表します。)

常設テーブル
- n <= 500の場合、RT = 500。
- n> 500の場合、RT = 500 + 0.20 * n。

一時テーブル
- n <6の場合、RT = 6。
- 6 <= n <= 500の場合、RT = 500。
- n> 500の場合、RT = 500 + 0.20 * n。
テーブル変数
- RTが存在しません。したがって、テーブル変数の基数の変更のために再コンパイルは行われません。(ただし、下記のTF 2453に関するメモを参照してください)

KEEP PLANヒントを使用してRTを次のように設定できます。#tempテーブルは、永続テーブルと同じです。

これらすべての正味の効果は、多くの場合、生成された実行計画が#tempテーブルは次のものよりも優れた桁数です。@table_variablesSQL Serverがより多くの作業に役立つ情報を持っているので、多くの行が関係しているとき。

NB1:テーブル変数には統計がありませんが、トレースフラグ2453で "Statistics Changed"再コンパイルイベントが発生する可能性があります( "自明な"計画には適用されません)。追加のものN=0 -> RT = 1。つまり、テーブル変数が空のときにコンパイルされたすべてのステートメントは、再コンパイルされて修正されます。TableCardinality空でないときに最初に実行されます。コンパイル時表のカーディナリティーはプランに格納され、ステートメントが同じカーディナリティーで再度実行された場合(制御ステートメントのフローまたはキャッシュされたプランの再使用のため)、再コンパイルは行われません。

NB2:ストアドプロシージャのキャッシュされた一時テーブルの場合、再コンパイルの話は上記よりもはるかに複雑です。見るTemporary Tables in Stored Proceduresすべての悲惨な詳細については。

再コンパイル

上記の変更ベースの再コンパイル#tempテーブルも関連付けることができますadditional compiles単にそれらがコンパイルを引き起こすテーブル変数に対して禁止されている操作を許可するからです(例えばDDLの変更)。CREATE INDEXALTER TABLE

ロッキング

それhas been statedそのテーブル変数はロックに関与しません。これはそうではありません。以下の出力を実行すると、SSMSメッセージタブに、insertステートメントに対して取得および解放されたロックの詳細が表示されます。

DECLARE @tv_target TABLE (c11 int, c22 char(100))

DBCC TRACEON(1200,-1,3604)

INSERT INTO @tv_target (c11, c22)

VALUES (1, REPLICATE('A',100)), (2, REPLICATE('A',100))

DBCC TRACEOFF(1200,-1,3604)

クエリのSELECTPaul White氏は、テーブル変数から、暗黙のうちに暗黙のNOLOCKヒント。これを以下に示します

DECLARE @T TABLE(X INT); 

SELECT X
FROM @T 
OPTION (RECOMPILE, QUERYTRACEON 3604, QUERYTRACEON 8607)

出力

*** Output Tree: (trivial plan) ***

       PhyOp_TableScan TBL: @T Bmk ( Bmk1000) IsRow: COL: IsBaseRow1002 Hints( NOLOCK )

ただし、これがロックに与える影響はごくわずかです。

SET NOCOUNT ON;

CREATE TABLE #T( [ID] [int] IDENTITY NOT NULL,
            [Filler] [char](8000) NULL,
            PRIMARY KEY CLUSTERED ([ID] DESC))


DECLARE @T TABLE ( [ID] [int] IDENTITY NOT NULL,
            [Filler] [char](8000) NULL,
            PRIMARY KEY CLUSTERED ([ID] DESC))

DECLARE @I INT = 0

WHILE (@I < 10000)
BEGIN
INSERT INTO #T DEFAULT VALUES
INSERT INTO @T DEFAULT VALUES
SET @I += 1
END

/*Run once so compilation output doesn't appear in lock output*/
EXEC('SELECT *, sys.fn_PhysLocFormatter(%%physloc%%) FROM #T')

DBCC TRACEON(1200,3604,-1)
SELECT *, sys.fn_PhysLocFormatter(%%physloc%%)
FROM @T

PRINT '--*--'

EXEC('SELECT *, sys.fn_PhysLocFormatter(%%physloc%%) FROM #T')

DBCC TRACEOFF(1200,3604,-1)

DROP TABLE #T

どちらの結果もインデックスキーの順序にはならず、SQL ServerはSQL Serverを使用したことを示します。allocation ordered scan両方のための。

上記のスクリプトを2回実行したところ、2回目の実行結果は以下のようになりました。

Process 58 acquiring Sch-S lock on OBJECT: 2:-1325894110:0 (class bit0 ref1) result: OK

--*--
Process 58 acquiring IS lock on OBJECT: 2:-1293893996:0 (class bit0 ref1) result: OK

Process 58 acquiring S lock on OBJECT: 2:-1293893996:0 (class bit0 ref1) result: OK

Process 58 releasing lock on OBJECT: 2:-1293893996:0 

SQL Serverはオブジェクトに対するスキーマ安定性ロックを取得するだけなので、テーブル変数のロック出力はごくわずかです。しかし#tempそれはオブジェクトレベルを取り出すという点でそれはほぼ同じくらい軽いですSロック。ANOLOCKヒントやREAD UNCOMMITTEDを使用している場合は、分離レベルをもちろん明示的に指定できます#tempテーブルも。

周囲のユーザートランザクションのログ記録に関する問題と同様に、ロックがより長い間保持されることを意味する可能性があります。#tempテーブル下記のスクリプトで

  --BEGIN TRAN;  

     CREATE TABLE #T (X INT,Y CHAR(4000) NULL);

     INSERT INTO #T (X) VALUES(1) 

     SELECT CASE resource_type
          WHEN 'OBJECT' THEN OBJECT_NAME(resource_associated_entity_id, 2)
          WHEN 'ALLOCATION_UNIT' THEN (SELECT OBJECT_NAME(object_id, 2)
                         FROM tempdb.sys.allocation_units a 
                         JOIN tempdb.sys.partitions p ON a.container_id = p.hobt_id
                         WHERE a.allocation_unit_id = resource_associated_entity_id)
          WHEN 'DATABASE' THEN DB_NAME(resource_database_id)                   
          ELSE (SELECT OBJECT_NAME(object_id, 2)
             FROM  tempdb.sys.partitions
             WHERE partition_id = resource_associated_entity_id)
         END AS object_name,
         *
     FROM  sys.dm_tran_locks
     WHERE request_session_id = @@SPID

     DROP TABLE #T

     -- ROLLBACK 

どちらの場合も明示的なユーザートランザクションの外側で実行した場合、チェック時に唯一のロックが返されます。sys.dm_tran_locksの共有ロックです。DATABASE

コメントを解除するBEGIN TRAN ... ROLLBACKロールバックを可能にし、他のトランザクションがコミットされていないデータを読み取らないようにするために、オブジェクト自体とシステムテーブル行の両方にロックが保持されていることを示す26行が返されます。同等のテーブル変数操作は、ユーザートランザクションによるロールバックの対象ではなく、次のステートメントをチェックインするためにこれらのロックを保持する必要はありませんが、Profilerで取得および解放されたトレーストレース1200の使用起こる。

索引

SQL Server 2014より前のバージョンでは、一意の制約または主キーを追加することによる副作用として、インデックスはテーブル変数に対して暗黙的にしか作成できません。これはもちろん、一意のインデックスのみがサポートされていることを意味します。一意のクラスタ化インデックスを持つテーブルの非一意の非クラスタ化インデックスは、単純に宣言することでシミュレートできます。UNIQUE NONCLUSTEREDそして、CIキーを目的のNCIキーの最後に追加します。do this behind the scenes anyway一意でないNCIを指定できたとしても)

先に示したようにindex_optionsは、次のように制約宣言で指定できます。DATA_COMPRESSIONIGNORE_DUP_KEY、そしてFILLFACTOR(ただし、インデックスの再構築には効果があるだけで、テーブル変数のインデックスを再構築することはできないため、設定しても意味がありません。)

さらにテーブル変数はサポートしていませんINCLUDEd列、フィルター索引(2016年まで)またはパーティション化#tempテーブルdo(パーティションスキームはtempdb

SQL Server 2014のインデックス

SQL Server 2014のテーブル変数定義では、一意でないインデックスをインラインで宣言できます。そのための構文例を以下に示します。

DECLARE @T TABLE (
C1 INT INDEX IX1 CLUSTERED, /*Single column indexes can be declared next to the column*/
C2 INT INDEX IX2 NONCLUSTERED,
       INDEX IX3 NONCLUSTERED(C1,C2) /*Example composite index*/
);

SQL Server 2016のインデックス

CTP 3.1から、テーブル変数に対してフィルタ処理されたインデックスを宣言できるようになりました。RTMによってそれ5月含まれている列も許可される場合がありますwill likely not make it into SQL16 due to resource constraints

DECLARE @T TABLE
(
c1 INT NULL INDEX ix UNIQUE WHERE c1 IS NOT NULL /*Unique ignoring nulls*/
)

並列処理

に挿入する(または変更する)クエリ@table_variables並列計画はできません。#temp_tablesこのように制限されていません。

次のように書き換えることで、SELECT並行して行われる部分ですが、隠された一時テーブルを使用することになります(behind the scenes)

INSERT INTO @DATA ( ... ) 
EXEC('SELECT .. FROM ...')

そのような制限はありません。選択するテーブル変数からas illustrated in my answer here

その他の機能上の違い

  • #temp_tables関数内では使用できません。@table_variablesスカラー表または複数ステートメント表のUDFの中で使用できます。
  • @table_variables名前付き制約を持つことはできません。
  • @table_variablesすることはできませんSELECTしたINTOALTER-ed、TRUNCATEdまたはのターゲットになるDBCCなどのコマンドDBCC CHECKIDENTまたはのSET IDENTITY INSERTなどのテーブルヒントはサポートしていません。WITH (FORCESCAN)
  • CHECKテーブル変数に対する制約は、単純化、暗黙の述語、または矛盾の検出のためにオプティマイザによって考慮されません。
  • テーブル変数は次のものに適していないようです。rowset sharing optimisationこれらに対する削除および更新計画は、より多くのオーバーヘッドに遭遇する可能性があることを意味します。PAGELATCH_EX待ちます。(Example

メモリだけ?

冒頭で述べたように、両方ともページに保存されます。tempdb。しかし、これらのページをディスクに書き込むことに関しては、動作に違いがあるかどうかについては触れませんでした。

私は今これについて少量のテストをしました、そして今のところそのような違いを見たことがありません。SQL Serverの私のインスタンスで行った特定のテストでは、250ページがデータファイルが書き込まれる前のカットオフポイントのようです。

注意:以下の動作は、SQL Server 2014以降では発生しません。SQL Server 2012 SP1/CU10 or SP2/CU1熱心な作家はもはやディスクにページを書くことに熱心ではありません。その変更の詳細については、SQL Server 2014: tempdb Hidden Performance Gem

下記のスクリプトを実行する

CREATE TABLE #T(X INT, Filler char(8000) NULL)
INSERT INTO #T(X)
SELECT TOP 250 ROW_NUMBER() OVER (ORDER BY @@SPID)
FROM master..spt_values
DROP TABLE #T

そしてモニタリングはtempdbProcess Monitorを使ったデータファイル私は何も見ませんでした(時々オフセット73,728のデータベースブートページへのものを除いて)。変更後250251私は以下のように書いているのを見始めました。

ProcMon

上のスクリーンショットは5 * 32ページの書き込みと1ページの書き込みを示しており、161ページがディスクに書き込まれたことを示しています。テーブル変数を使ってテストしたときも、250ページという同じカットオフポイントがありました。以下のスクリプトは、見てみると別の方法を示しています。sys.dm_os_buffer_descriptors

DECLARE @T TABLE (
    X    INT,
    [dba.se] CHAR(8000) NULL)

INSERT INTO @T
         (X)
SELECT TOP 251 Row_number() OVER (ORDER BY (SELECT 0))
FROM  master..spt_values

SELECT is_modified,
       Count(*) AS page_count
FROM  sys.dm_os_buffer_descriptors
WHERE database_id = 2
       AND allocation_unit_id = (SELECT a.allocation_unit_id
                    FROM  tempdb.sys.partitions AS p
                   INNER JOIN tempdb.sys.system_internals_allocation_units AS a
                        ON p.hobt_id = a.container_id
                       INNER JOIN tempdb.sys.columns AS c
                        ON c.object_id = p.object_id
                    WHERE c.name = 'dba.se')
GROUP BY is_modified 

結果

is_modified page_count
----------- -----------
0      192
1      61

192ページがディスクに書き込まれ、ダーティフラグがクリアされたことを示す。また、ディスクに書き込まれても、ページがすぐにバッファプールから削除されるわけではないことも示しています。このテーブル変数に対するクエリは、依然としてメモリから完全に満たされる可能性があります。

アイドル状態のサーバーでmax server memoryに設定2000 MBそしてDBCC MEMORYSTATUSバッファプールページの報告約1,843,000 KB(約23,000ページ)として割り当てられています。1,000行/ページのバッチで上記のテーブルに挿入し、記録された各反復について。

SELECT Count(*)
FROM  sys.dm_os_buffer_descriptors
WHERE database_id = 2
       AND allocation_unit_id = @allocId
       AND page_type = 'DATA_PAGE' 

テーブル変数と#temptableはほぼ同じグラフを与え、バッファプールを最大限に使い切ってから完全にメモリ内に収まっていないという点に到達したため、どちらのメモリも消費できるかどうかについては特に制限はないようです。

Pages in Buffer Pool


35

勉強ではなく特定の経験に基づいて指摘したいことがいくつかあります。DBAとして、私はとても新しいので、必要に応じて訂正してください。

  1. #tempテーブルは、デフォルトでSQL Serverインスタンスのデフォルトの照合順序を使用します。そのため、特に指定しない限り、masterdbがデータベースと異なる照合順序を持つ場合、#tempテーブルとデータベーステーブルの間で値を比較または更新する際に問題が発生する可能性があります。http://www.mssqltips.com/sqlservertip/2440/create-sql-server-temporary-tables-with-the-correct-collation/参照してください。
  2. 完全に個人的な経験に基づいて、利用可能なメモリはどれがよりよく実行するかに影響を与えるようです。MSDNでは、小さな結果セットを格納するためにテーブル変数を使用することを推奨していますが、ほとんどの場合、違いはそれほど顕著ではありません。ただし、大規模なセットでは、テーブル変数のほうがはるかにメモリを集中的に使用し、クエリの速度が低下する可能性があることが明らかになります。
+5

また、SQL Server 2012を使用していてデータベースが含まれている場合、#tempテーブルの照合は呼び出し側データベースの照合を継承することができます。 29 1月. 132013-01-29 19:51:03

  0

スモールセット対ラージセットの#2に関する説明http://stackoverflow.com/a/14465163/5224021 08 2月. 172017-02-08 14:56:07