Quel avantage pour les tables temporaires ?

En effet, la possibilité de créer des index non clustered directement dans la commande de création de la table va favoriser la mise en cache de la table temporaire.

Je m’explique : auparavant, lorsque l’on créait une table temporaire dans une procédure stockée, elle n’était pas mise en cache lorsque l’on ne mélangeait les instructions DML et DDL.

Autrement dit, dès que l’on ajoutait un index non clustered sur la table temporaire (par l’instruction CREATE INDEX), la table n’était plus mise en cache. D’autres facteurs influent également sur la mise en cache, tel que le SQL dynamique par exemple, mais c’est un autre sujet. Dans les exemples qui suivent, toutes les conditions de mises en cache des tables temporaires sont remplies. Petite démo pour illustrer mon propos :

Dans l’exemple suivant, je créé une procédure stockée qui créé une table temporaire, sans index. J’appelle ensuite la procédure stockée 100 fois, et vérifie le nombre de tables créées en m’appuyant sur la dmv sys.dm_os_memory_ cache_counters, et vérifiant le nombre d’objets créés dans le cache de type = ‘CACHESTORE_TEMPTABLES’ :

• Création d’une procédure stockée

CREATE PROCEDURE P_TestTemp
AS
BEGIN
— Création d’une table temporaire
CREATE TABLE #Temp
(
Col1 int,
Col2 int,
Col3 Varchar(255)
)
–Ajout de 1000 lignes dans la table
DECLARE @i INT = 0
WHILE @i < 1000
BEGIN
INSERT INTO #Temp VALUES(@i, @i+10, replicate(‘a’, 255))
SET @i=@i+1
END
END
GO

• Purge du cache

DBCC FREESYSTEMCACHE(‘ALL’)
— Appel 100 fois de la proc stock
DECLARE @i int=0
WHILE @i<=100
BEGIN
EXEC P_TestTemp
SET @i=@i+1
END

• Affichage du nombre de table créée

SELECT CAST(entries_count AS VARCHAR(10)) ‘Nb tables temporaires créées’ FROM sys.dm_os_memory_cache_counters WHERE type = ‘CACHESTORE_TEMPTABLES’

Nous voyons bien qu’une seule table a été créée, la mise en cache a fonctionné :

(((IMG7607)))

Mais si l’on recommence le test en ajoutant simplement un index non clustered sur notre table temporaire façon SQL 2012 et versions précédentes, via un CREATE INDEX donc, la table n’est plus mise en cache. Illustration : (je ne reprends pas à chaque fois l’ensemble du script de test, je ne mentionne ici que les transformations apportées à la procédure stockée de test P_TestTemp. Tout le reste est strictement identique : l’appel à la proc stock, la purge du cachet et la collecte du nombre de tables créées, l’affichage du résultat).

• Modification de la procédure par l’ajout d’un index nonclustered

ALTER PROCEDURE P_TestTemp
AS
BEGIN
— Création d’une table temporaire
CREATE TABLE #Temp
(
Col1 int,
Col2 int,
Col3 Varchar(255)
)

• Création de l’index

CREATE NONCLUSTERED INDEX IDX_NCL_Col1Col3 ON
#Temp(Col1, Col3)
[…]

Le résultat est le suivant : 100 tables ont été créées :

(((IMG7608)))

Nous arrivions jusqu’à présent à plus ou moins contourner ce problème, en ajoutant une contrainte de clé primaire qui créé automatiquement un index clustered sur les colonnes concernées, et ainsi profiter de la présence d’un index clustered tout en cachant la table :

ALTER PROCEDURE P_TestTemp
AS
BEGIN
— Création d’une table temporaire
CREATE TABLE #Temp
(
Col1 int PRIMARY KEY,
Col2 int,
Col3 Varchar(255)
)
[…]

Cela fonctionne, mais manque clairement de souplesse. Comment utiliser des tables temporaires indexées tout en bénéficiant de la mise en cache ?

SQL Server 2014 apporte la réponse : nous pouvons maintenant créer les index non clustered directement dans la commande de création de la table, nous permettant ainsi de créer une table temporaire #Table indexée, tout en bénéficiant de la mise en cache.

Démonstration : je modifie ma procédure stockée pour créer une table temporaire indexée comme suit, en enlevant tout autre ordre DDL de création d’index :

ALTER PROCEDURE P_TestTemp
AS
BEGIN
— Création d’une table temporaire
CREATE TABLE #Temp
(
Col1 int PRIMARY KEY,
Col2 int,
Col3 Varchar(255),
INDEX IDX_NCL_Col2 NONCLUSTERED(col2)
)
[…]

J’appelle 100 fois ma procédure stockée, et donc la création de ma table temporaire indexée, et j’obtiens bien une unique création de table :

(((IMG7609)))

Voilà une optimisation qui peut s’avérer bien utile : la tempdb fait l’objet de nombreuses créations / destructions d’objet, des contentions sur les pages d’allocation (PFS, GAM, SGAM) sont fréquentes. Aussi, tous les favorisant la mise en cache sont plutôt bienvenus.

Conclusion

Le « INLINE INDEX CREATION » a permis de rouvrir le débat entre les tables temporaires et les variables de type table. Mais malgré la possibilité de créer des index sur les variables table, le moteur n’est toujours pas capable de gérer des statistiques de distribution sur ce type d’objet, ce qui limite finalement la portée de l’optimisation.

Qui plus est, les tables temporaires peuvent dorénavant être indexées et mises en cache : avantage donc encore une fois aux tables temporaires…