[SQL Server] TempDB : les tables variables sont-elles exemptes de locks ? Démonstration…

Ce petit billet démontre que, contrairement à une idée reçue, les tables variables n’empêchent pas les locks, même si ceux-ci sont légers.

Entrée dans le vif du sujet…

La requête ci-dessous crée une table variable et lui insère jusqu’à 1000 enregistrements :

SET NOCOUNT ON;
DBCC TRACEON(1200,-1,3604)
DECLARE @Tab TABLE (Table_ID INT IDENTITY, Test CHAR(800) NULL)DECLARE @i INT = 0
WHILE (@i < 1000)
 BEGIN
    INSERT INTO @Tab DEFAULT VALUES
      SET @i+= 1
 END
DBCC TRACEOFF(1200,-1,3604)
GO

Le trace flag 1200 est, ici, utilisé afin d’obtenir un rapport détaillé de tous les locks acquis et libérés durant le traitement de la requête. Le trace flag 3604 permet, quant à lui, l’affichage des informations obtenues. La valeur -1 de DBCC TRACEON sert juste à activer globalement les trace flags spéciés.

Résultat (extrait) :


Comme on peut le noter ci-dessus, le traitement de tables variables n’empêche pas forcément la pose de verrous (locks) par SQL Server. En revanche, le blocage reste léger.

A noter que les requêtes SELECT sur les tables variables sont toujours implicitement accompagnées de l’hint
NOLOCK (ce qui favorise les lectures « sales »). En guise de démonstration :

DECLARE @Tab TABLE(Tab_ID INT);
SELECT *
FROM @Tab
OPTION (QUERYTRACEON 3604, QUERYTRACEON 8607)
GO

Le trace flag 8607 donne des informations relatives à un arbre logique (au sein du plan d’exécution) utilisé par l’optimiseur durant le processus de traitement de la requête.

Résultat (extrait) :


Ci-dessus, on peut apercevoir l’utilisation implicite de NOLOCK par SQL Server.

Pour aller plus loin…

Pour avoir une comparaison entre les tables temporaires et les tables variables, vous pouvez aller ici.

Publicités

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s