SelectEtoile : Base de connaissance SGBD

SQL 2014 : INLINE INDEX 2/2

1

Nous avions vu ici que les index inline apportaient une optimisation aux variables tables lorsqu'ils étaient couvrant.
Voyons aujourd'hui ce qu'il en est pour les tables temporaires (#table)

Nous pouvions jusqu'à présent créer des index sur les tables temporaires par l'instruction classique CREATE INDEX.
L'inconvénient de cette méthode est qu'elle pénalise la mise en cache. En effet, les tables temporaires peuvent être mise en cache et réutilisée depuis SQL 2005, afin de limiter les contentions sur les pages d'allocation et les tables système de la tempdb.
Simplement, cette mise en cache ne fonctionnera pas si l'on ajoute une clause CREATE INDEX sur la table temporaire : allez jeter un oeil sur ce post pour en savoir plus.
A partir du moment ou l'on peut créer des index INLINE, les deux avantages de création d'index et mise en cache vont se cumuler. Brillant non ?

Démonstration :

Voici un petit script, qui va exécuter les actions suivantes :
- création d'une procédure stockée qui créé une table temporaire avec un INLINE NONCLUSTERED INDEX, puis insert 1000 lignes
- purge du cache CACHESTORE_TEMPTABLES
- lancement de 100 appels à cette procédure
- vérification du nombre de table temporaire créé
A votre avis, combien de fois allons-nous avoir de création de table ? Voyons cela :

--Création d'une procédure stockée
CREATE PROCEDURE P_TestTemp
AS
BEGIN
    -- Création d'une table temporaire avec création d'index INLINE
    CREATE TABLE #Temp
    (
        Col1 int PRIMARY KEY,
        Col2 int,
        Col3 Varchar(255),
        INDEX IDX_NCL_Col2 NONCLUSTERED(col2)
    )

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

Super! Une seule table temporaire créée. Vous imaginez l'impact si l'on a une procédure stockée qui Contient un CREATE INDEX sur une table temporaire et qui est appelé plusieurs millier de fois par seconde ? L'utilisation de l'index INLINE réduira drastiquement la contention en tempDB !

O.

Ajouter un Commentaire


Code de sécurité
Rafraîchir