La durée de vie d'une table temporaire

Quand SQL Server supprime-t-il une table temporaire ? Tout de suite après un lot, mais une procédure stockée la garde en cache et un pool de connexions garde la session ouverte.

Article coécrit avec Claude, qui a mené les tests en laboratoire et une partie des recherches.

Pendant une formation d’administration SQL Server, cette semaine, un stagiaire m’a demandé combien de temps une petite table temporaire reste dans tempdb après la fermeture de la session. La documentation répond en une ligne, et sa réponse est juste quand la table a été créée dans un lot. Elle ne l’est plus quand la table vient d’une procédure stockée, ou d’une application qui utilise un pool de connexions.

Une table temporaire locale est une table dont le nom commence par #. Elle est créée dans la base système tempdb et n’est visible que de la session qui l’a créée. Une table temporaire globale commence par ## et elle est visible de toutes les sessions. Vous les voyez toutes les deux dans tempdb.sys.objects, où le nom d’une table locale est complété par un suffixe, puisque plusieurs sessions peuvent créer une table #t en même temps.

La page CREATE TABLE donne la règle : une table locale créée dans une procédure stockée est supprimée à la fin de la procédure, et toutes les autres tables locales le sont à la fin de la session. C’est exact du point de vue de votre code, qui ne voit plus la table. Ce n’est pas toujours ce que vous voyez dans tempdb.

Sauf mention contraire, les mesures de cet article ont été faites sur SQL Server 2022 CU26 et SQL Server 2025 CU7, en conteneurs Linux, avec la base de démonstration PachaDataFormation. Les deux versions ont donné les mêmes résultats.

Une table créée dans un lot

C’est le cas de la question. Ouvrez une première session et créez la table :

-- Session 1
CREATE TABLE #petite (id int);
INSERT INTO #petite VALUES (1);

SELECT name, object_id
FROM tempdb.sys.objects
WHERE object_id = OBJECT_ID('tempdb..#petite');

Le nom affiché est #petite, suivi d’une longue suite de _ et d’un numéro. Depuis une deuxième session, cherchez-la par son nom :

-- Session 2
SELECT name, object_id
FROM tempdb.sys.objects
WHERE name LIKE '#petite[_]%';

Le [_] est nécessaire, _ étant un joker de LIKE. Fermez la session 1 et relancez la requête de la session 2 : la table n’y est plus.

Pour mesurer le délai, j’ai fait attendre la session 1 trois secondes avant de se terminer, pendant que la session 2 interrogeait tempdb.sys.objects toutes les 10 ms. Sur six essais, la table avait disparu entre 4 et 8 ms environ après la dernière instruction de la session 1. Il n’y a donc pas de délai.

Une table créée dans une procédure stockée

Créer une table, puis la supprimer, coûte des écritures dans les tables système de tempdb et des allocations de pages. Une procédure appelée des milliers de fois par minute le paierait à chaque appel. SQL Server évite ce coût en mettant la table en cache : à la fin de la procédure, il garde la table, avec sa définition, et la redonne à l’exécution suivante. La page de tempdb le dit en deux phrases : la mise en cache rend très rapides les suppressions et créations d’objets temporaires, et elle réduit la contention sur l’allocation des pages et sur les métadonnées. Elle range cette mise en cache parmi les nouveautés de SQL Server 2016, mais le livre blanc de Microsoft Working with tempdb in SQL Server 2005 la décrit déjà pour SQL Server 2005.

Vous le voyez avec une procédure minimale :

USE PachaDataFormation;
GO
CREATE OR ALTER PROCEDURE dbo.DemoTableTemporaire
AS
BEGIN
    CREATE TABLE #t (id int PRIMARY KEY, v int);
    INSERT INTO #t VALUES (1, 1);
    SELECT OBJECT_ID('tempdb..#t') AS object_id_pendant;
END;
GO

EXEC dbo.DemoTableTemporaire;
GO

-- La procédure est finie : la table est toujours là
SELECT name, object_id, create_date
FROM tempdb.sys.objects
WHERE type = 'U'
  AND name LIKE '#[0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F]';

La table a pris un nom de huit chiffres hexadécimaux, comme #ACA32BA5, qui est l’écriture hexadécimale de son object_id. Elle a gardé l’object_id qu’elle avait pendant la procédure. Exécutez de nouveau la procédure, depuis la même session ou depuis une autre : object_id_pendant donne la même valeur. C’est la même table qui resert. Le filtre de la dernière requête trouve aussi les variables de type table mises en cache : sur un serveur actif, cherchez plutôt l’object_id que la procédure a renvoyé.

Un DROP TABLE #t à la fin de la procédure n’y change rien : la table est mise en cache de la même façon. Le DROP TABLE explicite reste une bonne habitude, il ne supprime simplement pas l’objet de tempdb.

La table en cache ne garde presque rien. Avec une procédure qui insère 10 000 lignes, la table occupait 272 pages pendant l’exécution. Juste après, l’objet en cache n’en utilisait plus que 2, pour 0 ligne, et toujours 2 pages dix secondes plus tard. Le livre blanc de 2005 précise que SQL Server garde en cache une page de données et une page IAM. Sur SQL Server 2025, j’ai aussi lu le nombre de pages réservées : 9, c’est-à-dire une extension de huit pages et la page IAM. Pour le stagiaire, la réponse est donc : la table existe encore dans tempdb, mais elle est vide, et elle ne coûte que quelques pages.

Les conditions de la mise en cache

La documentation actuelle ne donne pas ces conditions. Le livre blanc de 2005 en donne la liste : pas de contrainte nommée, pas d’instruction DDL sur la table après sa création, comme CREATE INDEX ou CREATE STATISTICS, pas de création par du SQL dynamique, et une table créée dans une procédure stockée, un déclencheur ou une fonction. Je l’ai vérifiée sur les deux versions, en partant de la procédure ci-dessus et en ne changeant qu’une chose à la fois. J’ai utilisé deux critères : un nouvel objet en cache dans tempdb.sys.objects après l’exécution, et le même object_id sur trois exécutions lancées depuis trois sessions différentes. Les deux critères ont donné le même verdict dans tous les cas.

La procédureMise en cache
CREATE TABLE avec une clé primaire sans nomoui
la même, avec DROP TABLE #t à la finoui
un index déclaré dans le CREATE TABLE (INDEX ix_v (v))oui
SELECT ... INTO #toui
une contrainte nommée (CONSTRAINT pk_t PRIMARY KEY)non
CREATE INDEX après le CREATE TABLEnon
ALTER TABLE #t ADD ... après le CREATE TABLEnon
CREATE STATISTICS sur la tablenon
procédure créée WITH RECOMPILEnon
le même code dans sp_executesql, hors procédurenon

Je n’ai pas cherché la raison de chaque ligne : ce sont des observations sur ces deux versions. Déclarez vos index dans le CREATE TABLE, ce qui est possible depuis SQL Server 2014, plutôt que par un CREATE INDEX qui suit. Ne nommez pas non plus les contraintes d’une table temporaire : la page CREATE TABLE montre qu’une contrainte nommée provoque en plus l’erreur 2714 quand deux sessions créent la table en même temps, puisque le nom de la table reçoit un suffixe et pas celui de la contrainte.

La suppression de la table en cache

La table en cache vit avec le plan de la procédure. Quand le plan quitte le cache, la table est supprimée. J’ai provoqué la sortie du plan de quatre façons : DBCC FREEPROCCACHE, sp_recompile sur la procédure, CREATE OR ALTER PROCEDURE et DROP PROCEDURE. Les quatre ont supprimé la table, mais pas immédiatement. Sur une soixantaine d’essais, la table a disparu entre 1,1 et 14,7 secondes après le déclencheur. Les disparitions tombaient toujours à des instants espacés de cinq secondes, quel que soit le moment du déclencheur.

Les tables en cache peuvent aussi être libérées sans toucher aux plans. Elles sont rangées dans un cache store nommé Temporary Tables & Table Variables, et DBCC FREESYSTEMCACHE accepte ce nom :

DBCC FREESYSTEMCACHE ('Temporary Tables & Table Variables');

Après cette commande, la table de ma procédure n’existait plus douze secondes plus tard, et le plan de la procédure était toujours en cache. À l’exécution suivante, la procédure a recréé sa table, qui est retournée en cache. Je n’ai fait qu’un essai sur 2022 et un sur 2025. La page de DBCC FREESYSTEMCACHE ne montre que 'ALL' dans sa syntaxe : le nom d’un cache n’y est pas documenté, et j’ai proposé une correction.

Le compteur de performance Temp Tables For Destruction, de l’objet General Statistics, compte les tables temporaires et variables de type table qui attendent d’être détruites par le thread système de nettoyage. Vous pouvez le lire dans sys.dm_os_performance_counters. Dans mes essais, il passait à 1 dans les 50 ms qui suivaient le sp_recompile, et revenait à 0 quand la table disparaissait.

D’après le livre blanc de 2005, SQL Server libère aussi les tables en cache quand tempdb manque d’espace. Un plan quitte enfin le cache sous pression mémoire. Je n’ai mesuré aucun de ces deux cas.

Ne lancez pas DBCC FREEPROCCACHE sur un serveur de production pour faire le ménage dans tempdb : il vide le cache de toutes les requêtes, qui vont toutes recompiler, et il ne libère que quelques pages par procédure. Si vous voulez vraiment voir disparaître la table d’une procédure, sp_recompile sur cette seule procédure suffit. Pour vider le cache des tables temporaires de toute l’instance sans recompiler, utilisez DBCC FREESYSTEMCACHE ('Temporary Tables & Table Variables').

Le pool de connexions

Le deuxième cas tient à l’application. Microsoft.Data.SqlClient, le pilote .NET, garde par défaut les connexions ouvertes dans un pool. Quand l’application appelle Close(), la connexion retourne dans le pool, et la session SQL Server reste ouverte. Pour SQL Server, la session ne s’est pas terminée, et la table temporaire non plus.

Je l’ai mesuré avec un petit programme .NET 10 et Microsoft.Data.SqlClient 7.1.1, sur les deux versions. Le programme ouvre une connexion, crée une table #pool, puis ferme la connexion. Une autre connexion, sans pool, observe la session et la table :

ÉtapeSession ouverteTable présente
Close(), la connexion retourne dans le poolouioui
Open() d’une nouvelle connexion, qui reprend la même sessionouioui
première commande sur cette connexionouinon
même scénario avec Pooling=False, après Close()nonnon

La page Microsoft sur le pool de SqlClient dit que Close remet la connexion à zéro et la rend au pool, et que le pool remet à zéro l’état de la session avant de la confier à une autre connexion logique. La seconde phrase correspond à ma mesure, la première non : la table existait toujours après le Close(), et même après l’Open() qui reprenait la session. Elle n’a disparu qu’avec la première commande envoyée sur la connexion reprise, qui a vu OBJECT_ID('tempdb..#pool') à NULL, avec le même numéro de session. La même page recommande de ne pas compter sur une table temporaire d’une connexion logique à l’autre. La page des procédures stockées système relie sp_reset_connection à la réutilisation d’une connexion du pool, sans en dire plus.

Si la connexion n’est jamais reprise, le pool la ferme lui-même au bout d’environ quatre à huit minutes d’inactivité, comme l’indique la documentation du pool de connexions ADO.NET. Dans mon essai, sur SQL Server 2022, la table #inactif d’une connexion laissée dans le pool a disparu entre 7 min 16 s et 7 min 31 s après le Close(), en même temps que la session. C’est une seule mesure, à lire comme un ordre de grandeur.

Les autres pilotes ne se comportent pas forcément ainsi, et je ne les ai pas mesurés. Le pilote JDBC de Microsoft n’a pas de pool à lui : c’est le serveur d’applications ou une bibliothèque tierce qui le fournit, et cette bibliothèque doit elle-même remettre la connexion dans son état d’origine. Avec ODBC, le pool du gestionnaire de pilotes est désactivé par défaut. Sous Linux, avec le gestionnaire unixODBC, la page sur l’authentification intégrée demande à l’application d’exécuter elle-même sp_reset_connection avant de réutiliser une connexion du pool. Avec un pool qui ne remet pas la session à zéro, la table temporaire devrait rester visible du code qui reprend la connexion. Je le déduis de ces pages, sans l’avoir mesuré.

Pour une petite table, cela n’a aucune importance. Pour une table de plusieurs gigaoctets, créée par un traitement qui passe ensuite à autre chose, l’espace reste pris dans tempdb pendant tout ce temps. Supprimez explicitement, par DROP TABLE, les tables temporaires que votre code crée dans un lot, surtout quand il passe par un pool.

Les tables temporaires globales

Une table ## suit une autre règle. La page CREATE TABLE dit qu’elle est supprimée à la fin de la session qui l’a créée, quand plus aucune autre tâche ne l’utilise, et que l’association entre une tâche et la table ne dure que le temps d’une instruction. Une session qui a lu la table une fois ne la retient donc pas.

C’est ce que j’ai mesuré, avec une nuance. Quand une deuxième session lit la table, avec ou sans transaction, puis attend, la table disparaît à la fin de la session qui l’a créée. Quand la deuxième session la lit dans une transaction avec WITH (HOLDLOCK), qui garde le verrou jusqu’à la fin de la transaction, la table survit à la session qui l’a créée. Elle disparaît moins d’une seconde après le COMMIT de la deuxième session.

Depuis SQL Server 2019, l’option GLOBAL_TEMPORARY_TABLE_AUTO_DROP change cette règle. Elle vaut ON par défaut. À OFF, une table globale n’est supprimée que par un DROP TABLE explicite ou au redémarrage de l’instance. Sur SQL Server, elle se règle dans tempdb, par ALTER DATABASE SCOPED CONFIGURATION.

Les grandes tables

Reste la libération de l’espace. D’après la page DROP TABLE, quand une table ou ses index occupent 128 extensions ou plus, c’est-à-dire 8 Mo, SQL Server la supprime en deux temps. La suppression logique est immédiate, et un processus de fond libère les pages ensuite. Le livre blanc de 2005 dit la même chose des objets temporaires de plus de 8 Mo. L’espace d’une grosse table temporaire peut donc ne pas être disponible au moment même où la table disparaît. Avec la récupération accélérée (ADR), qui peut être activée sur tempdb depuis SQL Server 2025, la suppression se fait en deux temps quelle que soit la taille. Je n’ai pas mesuré ce délai.