SelectEtoile : Base de connaissance SGBD

Mise en cache des tables temporaires

1

Présentation

L'utilisation d'objets temporaires par le moteur SQL est fréquent, que ce soit pour des besoins internes (libération d'espace mémoire, table de travail, gestion des tris etc....) ou lié à des actions utilisateurs directement (Création de tables temporaire entre autre).
Mais à chaque création d'objet temporaire, le moteur doit accéder aux pages d'allocation de la tempDB (PFS, GAM et SGAM) ainsi qu'à certaines de ces tables système (sys.columns et sys.objects notamment).
Le nombre impressionnant d'objets temporaire que le moteur est capable de gérer simultanément entrainait parfois des contentions sur ces pages / objets.
Et oui, je parle à l'imparfait car depuis la version 2005, Une optimisation visant à limiter ces contentions est apparus : le moteur peut mettre en cache les tables temporaires créées au sein de procédures stockées, sous certaines conditions, et c'est ce que nous allons voir.


La mise en cache

Différents facteurs vont entraver la mise en cache des tables temporaires. Les plus fréquents seront :
- utilisation de contraintes nommées
- utilisation d'instruction DDL
- recompilation manuel (with recompile)
- SQL dynamique

 

Démonstration

Et pour démontrer cela, je vais utiliser le facteur 2 : utilisation d'instruction DDL:
Nous allons donc mettre en place une procédure stockée qui va tout d'abord créer une table temporaire toute simple, puis appeler 100 fois cette procédure. Nous contrôlerons alors le nombre de tables temporaires créées.
Nous referons le même test mais en ajoutant un index sur la table temporaire (CREATE INDEX : instruction DDL donc) et nous comparerons les résultats.
NB : pour savoir combien de table temporaire sont créée, nous disposons de deux méthodes:
- utilisation de la DMV sys.dm_os_memory_cache_counters, en vérifiant le nombre d’objets créé dans le cache de type = 'CACHESTORE_TEMPTABLES'
- utilisation du compteur "Temp Table creation rate" de la DMV sys.dm_os_performance_counter (Compteur cumulatif).
Pour ma part, je choisis la première méthode et pour me simplifier la vie, vous constaterez que je purge le cache avant chaque début de test (à ne faire qu'en environnement de test évidemment).
Allons-y :

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



Modifions la procédure stockée en ajoutant un index sur notre table temporaire

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


 

Conclusion

L'optimisation s'avère bien utile et l'on comprend aisément l'intérêt de la mise en cache sur des tempDB très sollicités. Mais faut-il pour autant abandonner l'usage des index sur les tables temporaires ?
Se serait génial si l'on pouvait bénéficier de cette mise en cache tout en pouvant indexer nos tables temporaires... Et bien, c'est désormais possible grâce à SQL 2014 et sa possibilité de créer des index INLINE... Mais ça fera l'objet d'un prochain post...

O.

 


Ajouter un Commentaire


Code de sécurité
Rafraîchir