Advertisement
Loading

Quel avantage pour les tables temporaires ?

https://www.flickr.com/photos/110751683@N02/13334080323/

Les tables temporaires #table peuvent également tirer profit des index inline en termes de performances, mais de manière un peu plus subtil.

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

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 :

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 :

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…

123
Olivier Maître Olivier Maître - Administrateur de base de données
Olivier Maître est certifié MCITP SQL server database administrator et MCTS SQL Server Database Development.
 
Sur iTPro.fr, nous vous aidons à tirer le meilleur profit de vos environnements IT. Découvrez les analyses, les chroniques et plus de 4280 dossiers experts , profitez d’un savoir technologique unique et de ressources stratégiques exclusives pour vous accompagner dans le choix, la gestion et l’optimisation de vos environnements IT Professionnels. Bénéficiez d’une richesse éditoriale incomparable et vivez toute l’actualité IT professionnelle sur twitter avec #iTProFR
 
Enjeux de l’authentification forte pour la sécurité du SI ?Enjeux de l’authentification forte pour la sécurité du SI ?La mobilité, la consumérisation et le BYOD impliquent de nouveaux défis de sécurité pour les directions IT. Découvrez comment sécuriser les accès aux applications et aux données quelles que soient les situations de mobilité des collaborateurs.Découvrez le livre blanc

Ressources Informatiques

Comment répondre aux défis de la compromission d’identité ? La mobilité, la consumérisation et le BYOD impliquent de nouveaux défis de sécurité pour les directions IT. Découvrez comment sécuriser les accès…
   RSA | 4 pages
Découvrez le livre blanc
Les enjeux de l’authentification forte pour la sécurité du SI La mobilité, la consumérisation et le BYOD impliquent de nouveaux défis de sécurité pour les directions IT. Découvrez comment sécuriser les accès…
   RSA | 2 pages
Découvrez le livre blanc
SMART DSI : la nouvelle revue du décideur IT Conseil et expertise IT, bénéficiez des analyses, des chroniques et des dossiers de la nouvelle revue informatique SMART DSI pour conduire la transformation…
   SMART DSI | 52 pages
Découvrez la revue SMART DSI
Libérez le Big Data et le Machine Learning Nous sommes à la veille d'un changement spectaculaire où les machines seront capables de dépasser les êtres humains dans leur capacité à prendre…
   Comsoft | 12 pages
Découvrez l’article technique
BYOD : Guide des meilleures pratiques en entreprise Smartphones, tablettes, PC ultra-mobiles ont libéré les utilisateurs et transformé les interactions entre les collaborateurs. Encore faut-il que l’entre¬prise…
   HPE Aruba | 4 pages
Découvrez votre Guide de Stratégie Mobile
Optimiser la consommation énergétique du data center La gestion et la supervision des infrastructures IT n’ira qu’en s’aggravant tant que les entreprises n’adopteront pas une vraie gestion dynamique…
   Top 5 Décideur IT | 4 pages
Découvrez votre livre blanc
 

Informatique ProfessionnelleActualités, chroniques et dossiers IT experts

Tristan Karache Tristan Karache Journaliste informatique

Freddy Elmaleh Freddy Elmaleh Consultant freelance

Nabil Babaci Nabil Babaci Consultant Senior SharePoint

Vidéos Informatiques

Comment mettre en place une stratégie mobile performante ?Smartphones, tablettes, PC ultra-mobiles ont libéré les utilisateurs et transformé…Par Itpro

Hyperconvergence réussie avec les solutions Dell VxRaill'hyperconvergence intéresse et séduit les DSI par son approche intégrée et…Par Itpro

Windows 10 Professionnel : 5 Innovations pour les CollaborateursRépondre aux enjeux de mobilité des utilisateurs est un besoin réel, centré…Par Itpro

Répondre aux défis de l'hyper convergence ?Découvrez en vidéo motion les 5 atouts majeurs du partenariat DELL EMC et Misco-inmac…Par Itpro

Les Assises de la Sécurité 2015 : Yves Rochereau - Check PointCheck Point est une société spécialisée dans l'univers de la sécurité informatique…Par Itpro

Conseil & Expertise IT

Bénéficiez des analyses, des chroniques et des dossiers
de la nouvelle revue informatique SMART DSI pour conduire
la transformation numérique de votre entreprise.

Abonnez-vous à la revue SMART DSI