Quelle est la différence entre une table temporaire et une variable de table dans SQL Server?


404

Cela semble être un domaine avec pas mal de mythes et d'opinions contradictoires.

Alors, quelle est la différence entre une variable de table et une table temporaire locale dans SQL Server?

+4

Question liée au dépassement de capacité: [Quand devrais-je utiliser une variable de table par rapport à une table temporaire dans SQL Server?] (Http://stackoverflow.com/q/11857789/73226) 07 juil.. 162016-07-07 04:30:51

618

Contenu

Contents

Caveat

Cette réponse traite des variables de table "classiques" introduites dans SQL Server 2000. SQL Server 2014 en mémoire OLTP introduit les types de table optimisés en mémoire.Les instances de variables de table de celles-ci diffèrent à bien des égards de celles discutées ci-dessous!(more details).

Emplacement de stockage

Aucune différence.Les deux sont stockés danstempdb.

Je l'ai vu suggérer que pour les variables de table, ce n'est pas toujours le cas, mais cela peut être vérifié à partir des éléments ci-dessous.

DECLARE @T TABLE(X INT)

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

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

Exemple de résultats (montrant l'emplacement danstempdbles 2 lignes sont stockées)

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

Emplacement logique

@table_variablesse comporter davantage comme s’ils faisaient partie de la base de données actuelle#temples tables font.Pour les variables de table (depuis 2005), les classements de colonnes non spécifiés explicitement seront ceux de la base de données actuelle, tandis que pour les#temptables, il utilisera le classement par défaut detempdb(More details).De plus, les types de données et les collections XML définis par l'utilisateur doivent être dans tempdb pour pouvoir être utilisés.#temptables mais les variables de table peuvent les utiliser à partir de la base de données courante (Source).

SQL Server 2012 introduit les bases de données contenues.the behavior of temporary tables in these differs(h/t Aaron)

Dans une base de données contenue, les données d'une table temporaire sont regroupées dans le classement de la base de données contenue.

  • Toutes les métadonnées associées aux tables temporaires (par exemple, les noms de table et de colonne, les index, etc.) figureront dans le classement du catalogue.
  • Les contraintes nommées ne peuvent pas être utilisées dans les tables temporaires.
  • Les tables temporaires ne peuvent pas faire référence à des types définis par l'utilisateur, des collections de schémas XML ou des fonctions définies par l'utilisateur.

Visibilité à différentes portées

@table_variablesne sont accessibles que dans le lot et la portée dans lesquels ils sont déclarés.#temp_tablessont accessibles au sein de lots enfants (déclencheurs imbriqués, procédure,execappels).#temp_tablescréé à la portée extérieure (@@NESTLEVEL=0) peuvent également s'étendre sur plusieurs lots, car ils persistent jusqu'à la fin de la session.Aucun des deux types d’objets ne peut être créé dans un lot enfant et être accédé dans la portée de l’appel, mais comme indiqué plus loin (global##temples tablespouvezêtre si).

Durée de vie

@table_variablessont créés implicitement quand un lot contenant unDECLARE @.. TABLECes instructions sont exécutées (avant l’exécution de tout code utilisateur de ce lot) et sont supprimées implicitement à la fin.

Bien que l’analyseur ne vous permette pas d’essayer d’utiliser la variable table avant leDECLAREdéclaration la création implicite peut être vu ci-dessous.

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

--Works fine
SELECT *
FROM @T

#temp_tablessont créés explicitement lorsque le TSQLCREATE TABLEdéclaration est rencontrée et peut être supprimée explicitement avecDROP TABLEou sera abandonné implicitement à la fin du lot (si créé dans un lot enfant avec@@NESTLEVEL > 0) ou lorsque la session se termine autrement.

NB: Dans les routines stockées, les deux types d'objetcan be cachedplutôt que de créer et de supprimer à plusieurs reprises de nouvelles tables.Il y a des restrictions sur le moment où cette mise en cache peut se produire, mais il est possible de violer pour#temp_tablesmais que les restrictions sur@table_variablesprévenir de toute façon.Les frais généraux de maintenance pour la mise en cache#temptables estlégèrementplus grand que pour les variables de tableas illustrated here.

Métadonnées d'objet

C'est essentiellement la même chose pour les deux types d'objet.Il est stocké dans les tables de base du système danstempdb.Il est plus simple de voir pour un#temptable cependantOBJECT_ID('tempdb..#T')peut être utilisé pour entrer dans les tables système et le nom généré en interne est plus étroitement corrélé au nom défini dans leCREATE TABLEdéclaration.Pour les variables de table leobject_idfonction ne fonctionne pas et le nom interne est entièrement généré par le système sans relation avec le nom de la variable.L'exemple ci-dessous montre que les métadonnées sont toujours là, en tapant un nom de colonne (espérons-le unique).Pour les tables sans nom de colonne unique, object_id peut être déterminé à l'aide deDBCC PAGEtant qu'ils ne sont pas vides.

/*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'

Sortie

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         |
+-----------+-----------+------+-------------------+-------------+------------+------------+-----------------------+

Transactions

Opérations sur@table_variablessont effectuées en tant que transactions système indépendantes de toute transaction avec un utilisateur externe,#temples opérations de table seraient effectuées dans le cadre de la transaction utilisateur elle-même.Pour cette raison, unROLLBACKcommande affectera un#temptable mais quitte le@table_variableintacte.

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

Enregistrement

Les deux générer des enregistrements de journal à latempdbjournal des transactions.Une idée fausse commune est que ce n'est pas le cas pour les variables de table, donc un script démontrant cela est ci-dessous, il déclare une variable de table, ajoute quelques lignes, puis les met à jour et les supprime.

Comme la variable de table est créée et supprimée implicitement au début et à la fin du lot, il est nécessaire d’utiliser plusieurs lots afin de voir la journalisation complète.

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 

Résultats

Vue détaillée

Screenshot of results

Vue récapitulative (inclut la journalisation pour les tables de dépôt implicite et de base système)

Screenshot of results

As far as I've been able to discernles opérations sur les deux génèrent des quantités à peu près égales de journalisation.

Tandis que lequantitéde la journalisation est très similaire une différence importante est que les enregistrements de journal liés à#temples tables ne peuvent pas être effacées tant que la transaction utilisateur contenant n’est pas terminée, donc une transaction longue qui écrit à un moment donné dans#temples tables empêcheront la troncature du journaltempdbalors que les transactions autonomes générées pour les variables de table ne le sont pas.

Les variables de table ne supportent pasTRUNCATEpeut donc être désavantageux en matière de journalisation lorsqu'il est nécessaire de supprimer toutes les lignes d'une table (bien que pour de très petites tablesDELETEcan work out better anyway)

Cardinalité

De nombreux plans d'exécution impliquant des variables de table afficheront une seule ligne estimée en tant que sortie.L’inspection des propriétés de la variable de table montre que SQL Server pense que la variable de table azérolignes (Pourquoi pense-t-il qu'une ligne sera émise à partir d'une table de lignes zéro est expliquée par @Paul Whitehere).

Cependant, les résultats présentés dans la section précédente montrent une précisionrowscomptersys.partitions.Le problème est que, dans la plupart des cas, les instructions faisant référence aux variables de table sont compilées lorsque la table est vide.Si la déclaration est (re) compilée après@table_variableest renseigné, il sera utilisé pour la cardinalité de la table (ceci peut être dû à une erreur explicite).recompileou peut-être parce que l'instruction fait également référence à un autre objet qui provoque une compilation différée ou une recompilation.)

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

Le plan indique le nombre estimé de lignes exactes après la compilation différée.

Shows accurate row count

Dans SQL Server 2012 SP2, l'indicateur de trace 2453 est introduit.Plus de détails sont sous "Moteur relationnel"here.

Lorsque cet indicateur de trace est activé, les recompilations automatiques peuvent prendre en compte la cardinalité modifiée, comme indiqué plus en détail ultérieurement.

Aucune statistique de colonne

Avoir une cardinalité de table plus précise ne signifie pas pour autant que le nombre estimé de lignes sera plus précis (à moins d'effectuer une opération sur toutes les lignes de la table).SQL Server ne gère pas du tout les statistiques de colonne pour les variables de table, il faut donc se baser sur des suppositions basées sur le prédicat de comparaison (par exemple, 10% de la table seront renvoyés pour une=contre une colonne non unique ou 30% pour un>Comparaison).Statistiques de colonne en revanchesontmaintenu pour#temples tables.

SQL Server conserve le nombre de modifications apportées à chaque colonne.Si le nombre de modifications depuis la compilation du plan dépasse le seuil de recompilation (RT), le plan sera recompilé et les statistiques mises à jour.La RT dépend du type et de la taille de la table.

DePlan Caching in SQL Server 2008

RT est calculé comme suit.(n fait référence à la cardinalité d'une table lorsqu'un plan de requête est compilé.)

Table permanente
- Si n <= 500, RT = 500.
- Si n> 500, RT = 500 + 0,20 * n.

Table temporaire
- Si n <6, RT = 6.
- Si 6 <= n <= 500, RT = 500.
- Si n> 500, RT = 500 + 0,20 * n.
Variable de table
- RT n'existe pas.Par conséquent, les recompilations ne se produisent pas en raison de modifications des cardinalités des variables de table.(Mais voir la note sur la TF 2453 ci-dessous)

laKEEP PLANindice peut être utilisé pour définir la RT pour#temptables les mêmes que pour les tables permanentes.

L’effet net de tout cela est que souvent les plans d’exécution générés pour#temples tables sont des ordres de grandeur mieux que pour@table_variableslorsque plusieurs lignes sont impliquées, SQL Server dispose de meilleures informations.

NB1: Les variables de table ne comportent pas de statistiques mais peuvent toujours provoquer un événement de recompilation "Statistiques modifiées" sous l'indicateur de suivi 2453 (ne s'applique pas aux plans "triviaux"). Cela semble se produire avec les mêmes seuils de recompilation que ceux indiqués pour les tables temporaires ci-dessus avec un indicateur. un supplémentaire que siN=0 -> RT = 1.c'est-à-dire que toutes les instructions compilées lorsque la variable de table est vide finissent par obtenir une recompilation et une correctionTableCardinalityla première fois, ils sont exécutés non vides.La cardinalité de la table des temps de compilation est stockée dans le plan et si l'instruction est exécutée à nouveau avec la même cardinalité (en raison du flux d'instructions de contrôle ou de la réutilisation d'un plan mis en cache), aucune recompilation n'a lieu.

NB2: Pour les tables temporaires mises en cache dans les procédures stockées, l’histoire de la recompilation est beaucoup plus compliquée que celle décrite ci-dessus.VoirTemporary Tables in Stored Procedurespour tous les détails sanglants.

Recompile

Ainsi que les recompiles à base de modifications décrites ci-dessus#temples tables peuvent aussi être associées àadditional compilessimplement parce qu'ils permettent des opérations interdites pour les variables de table qui déclenchent une compilation (par exemple, les modifications de DDLCREATE INDEX,ALTER TABLE)

Verrouillage

Ilhas been statedles variables de la table ne participent pas au verrouillage.Ce n'est pas le cas.En exécutant les sorties ci-dessous dans l'onglet Messages SSMS, vous trouverez les détails des verrous utilisés et validés pour une instruction d'insertion.

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)

Pour les questions quiSELECTà partir des variables de table, Paul White souligne dans les commentaires que ceux-ci viennent automatiquement avec une impliciteNOLOCKallusion.Ceci est montré ci-dessous

DECLARE @T TABLE(X INT); 

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

Sortie

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

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

L'impact de ceci sur le verrouillage pourrait cependant être assez mineur.

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

Aucun de ces résultats ne donne un ordre de clé d'index indiquant que SQL Server a utilisé unallocation ordered scanpour les deux.

J'ai exécuté le script ci-dessus deux fois et les résultats de la deuxième exécution sont présentés ci-dessous.

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 

La sortie de verrouillage pour la variable de table est en effet extrêmement minime, car SQL Server vient d’acquérir un verrou de stabilité du schéma sur l’objet.Mais pour un#temptable, il est presque aussi léger en ce qu'il sort un niveau d'objetSfermer à clé.UNENOLOCKindice ouREAD UNCOMMITTEDniveau d'isolement peut bien sûr être spécifié explicitement lorsque vous travaillez avec#tempdes tables aussi.

De même que le problème de la journalisation d’une transaction utilisateur environnante, les verrous sont conservés plus longtemps.#temples tables.Avec le script ci-dessous

  --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 

lorsqu'il est exécuté en dehors d'une transaction utilisateur explicite dans les deux cas, le seul verrou renvoyé lors de la vérificationsys.dm_tran_locksest un verrou partagé sur leDATABASE.

En décommentant leBEGIN TRAN ... ROLLBACK26 lignes sont renvoyées, indiquant que les verrous sont maintenus à la fois sur l'objet lui-même et sur les lignes de la table système pour permettre une annulation et empêcher les autres transactions de lire des données non validées.L'opération de variable de table équivalente n'est pas sujette à l'annulation avec la transaction utilisateur et n'a pas besoin de conserver ces verrous pour que nous puissions enregistrer l'instruction suivante, mais les verrous de traçage acquis et publiés dans Profiler ou à l'aide de l'indicateur de trace 1200 indiquent que de nombreux événements de verrouillage se produire.

Les index

Pour les versions antérieures à SQL Server 2014, les index ne peuvent être créés de manière implicite que sur les variables de table en tant qu'effet secondaire de l'ajout d'une contrainte unique ou d'une clé primaire.Cela signifie bien entendu que seuls les index uniques sont pris en charge.Un index non unique non clusterisé sur une table avec un index clusterisé unique peut être simulé, toutefois, en le déclarant simplementUNIQUE NONCLUSTEREDet en ajoutant la clé CI à la fin de la clé NCI souhaitée (SQL Server seraitdo this behind the scenes anywaymême si un NCI non unique pourrait être spécifié)

Comme démontré précédemment diversindex_options peut être spécifié dans la déclaration de contrainte, y comprisDATA_COMPRESSION,IGNORE_DUP_KEY, etFILLFACTOR(Bien qu'il soit inutile de le définir car cela ne ferait aucune différence pour la reconstruction d'index et vous ne pouvez pas reconstruire les index sur les variables de table!)

De plus, les variables de tableau ne supportent pasINCLUDEd colonnes, index filtrés (jusqu'en 2016) ou partitionnement,#temptables do (le schéma de partition doit être créé danstempdb).

Index dans SQL Server 2014

Les index non uniques peuvent être déclarés en ligne dans la définition de variable de table dans SQL Server 2014. Un exemple de syntaxe est présenté ci-dessous.

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*/
);

Index dans SQL Server 2016

À partir de CTP 3.1, il est maintenant possible de déclarer des index filtrés pour les variables de table.Par RTM ilpeutêtre le cas que les colonnes incluses sont également autorisées même si elleswill 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*/
)

Parallélisme

Requêtes qui insèrent dans (ou modifient autrement)@table_variablesne peut pas avoir un plan parallèle,#temp_tablesne sont pas limités de cette manière.

Il semble y avoir une solution de contournement en ce que la réécriture suivante permet à laSELECTune partie se déroule en parallèle mais finit par utiliser une table temporaire masquée(behind the scenes)

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

Il n'y a pas de telle limitation dans les requêtes quisélectionnerà partir de variables de tableas illustrated in my answer here

Autres différences fonctionnelles

  • #temp_tablesne peut pas être utilisé dans une fonction.@table_variablespeut être utilisé à l'intérieur de fichiers UDF scalaires ou à tables d'instructions multiples.
  • @table_variablesne peut pas avoir de contraintes nommées.
  • @table_variablesc'est pas possibleSELECT-edINTO,ALTER-ed,TRUNCATEd ou être la cible deDBCCcommandes telles queDBCC CHECKIDENTou deSET IDENTITY INSERTet ne supporte pas les indices de table tels queWITH (FORCESCAN)
  • CHECKLes contraintes sur les variables de table ne sont pas prises en compte par l'optimiseur à des fins de simplification, de prédicats implicites ou de détection de contradiction.
  • Les variables de table ne semblent pas qualifier pour lerowset sharing optimisationce qui signifie que supprimer et mettre à jour des plans contre ceux-ci peuvent rencontrer plus de surcharge etPAGELATCH_EXattend.(Example)

Mémoire seulement?

Comme indiqué au début, les deux fichiers sont stockés danstempdb.Cependant, je n'ai pas précisé s'il y avait une différence de comportement en ce qui concerne l'écriture de ces pages sur disque.

J'ai fait quelques essais à ce sujet et jusqu'à présent, nous n'avons constaté aucune différence de ce type.Dans le test spécifique que j'ai effectué sur mon instance de SQL Server 250, il semble que le point limite avant que le fichier de données ne soit écrit.

Remarque: le comportement ci-dessous ne se produit plus dans SQL Server 2014 ouSQL Server 2012 SP1/CU10 or SP2/CU1l'écrivain avide n'est plus aussi désireux d'écrire des pages sur un disque.Plus de détails sur ce changement surSQL Server 2014: tempdb Hidden Performance Gem.

Exécuter le script ci-dessous

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

Et surveillance écrit autempdbfichier de données avec Process Monitor, j’en ai vu aucune (sauf occasionnellement sur la page de démarrage de la base de données avec décalage 73 728).Après avoir changé250à251J'ai commencé à voir écrit comme ci-dessous.

ProcMon

La capture d'écran ci-dessus montre 5 * 32 pages écrites et une seule page écrite indiquant que 161 des pages ont été écrites sur le disque.J'ai également obtenu le même seuil de 250 pages lors de tests avec des variables de table.Le script ci-dessous le montre différemment en regardantsys.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 

Résultats

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

Montrant que 192 pages ont été écrites sur le disque et le drapeau sale effacé.Cela montre également qu'être écrit sur le disque ne signifie pas que les pages seront immédiatement expulsées du pool de mémoire tampon.Les requêtes sur cette variable de table peuvent toujours être satisfaites entièrement à partir de la mémoire.

Sur un serveur inactif avecmax server memorymis à2000 MBetDBCC MEMORYSTATUSrapportant des pages de pool de mémoire tampon allouées à environ 1 843 000 Ko (environ 23 000 pages), j'ai inséré dans les tableaux ci-dessus par lots de 1 000 lignes/pages et pour chaque itération enregistrée.

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

La variable de table et la#temptable a donné des graphes presque identiques et a réussi à maximiser le pool de mémoire tampon avant de parvenir au point où ils n'étaient pas entièrement conservés en mémoire, de sorte qu'il ne semble pas y avoir de limitation particulière quant à la quantité de mémoire pouvant être consommée.

Pages in Buffer Pool


35

J'aimerais souligner quelques points basés sur des expériences particulières plutôt que sur des études.En tant que DBA, je suis très nouveau, alors corrigez-moi si nécessaire.

  1. Les tables #temp utilisent par défaut le classement par défaut de l'instance SQL Server.Par conséquent, sauf indication contraire, vous pourriez rencontrer des problèmes lors de la comparaison ou de la mise à jour de valeurs entre les tables #temp et les tables de base de données, si la base de données maître a un classement différent de celui de la base de données.Voir: http://www.mssqltips.com/sqlservertip/2440/create-sql-server-temporary-tables-with-the-correct-collation/
  2. Complètement basée sur l'expérience personnelle, la mémoire disponible semble avoir un effet sur celui qui fonctionne le mieux.MSDN recommande d'utiliser des variables de table pour stocker des jeux de résultats plus petits, mais la plupart du temps, la différence n'est même pas perceptible.Dans de plus grands ensembles, cependant, il est devenu évident dans certains cas que les variables de table nécessitent beaucoup plus de mémoire et peuvent ralentir la requête jusqu'à une analyse.
+5

Notez également que le classement sur # tables temporaires * peut * hériter du classement de la base de données appelante si vous utilisez SQL Server 2012 et que la base de données est contenue. 29 janv.. 132013-01-29 19:51:03

  0

Précision sur le n ° 2 pour les petits ensembles vs les grands ensembles http://stackoverflow.com/a/14465163/5224021 08 févr.. 172017-02-08 14:56:07