[SQL Server] TempDB : optimisation de la réutilisation des objets temporaires (Temporary Object Reuse)

Ce petit billet donne quelques astuces relatives à la réutilisation d’objets temporaires (tables variables, tables temporaires…) en cache. Il présente également quelques éléments d’audit, et  démonte l’idée selon laquelle les tables temporaires sont seulement stockées sur disque et les tables variables seulement en mémoire.

Pour connaître l’utilité et le fonctionnement de tempDB, les différents types d’objets temporaires et les éléments d’optimisation usuels, allez ici.

Rappel sur l’influence de tempDB sur les plans d’exécution

La base de données tempDB joue souvent un rôle non-négligeable dans les performances d’une requête.

Quand une requête est compilée (i.e., son plan d’exécution mis en cache), les objets temporaires créés par l’utilisateur dans tempDB qui sont requis pour le fonctionnement de la requête sont parfois mis en cache. Cela possède un nom : temporary object reuse.

Ces objets-là sont, par contre, cachés partiellement (et non totalement). En effet, jusqu’à neuf pages d’un objet temporaire créé par l’utilisateur sont mises en cache pour réutilisation.

Tout cela permet à SQL Server d’améliorer les performances de la requête concernée lors de sa prochaine exécution dans la mesure où l’objet temporaire partiellement mis en cache préexiste. En revanche, cela prend de la place au sein de tempDB.

En cas de manque de mémoire, SQL Server supprime, via le lazy writer, le plan d’exécution (suivant son ancienneté déterminée par un algorithme de type LRU ou Least Recent Use ; plus de détails ici, 3ème footnote), ainsi que les objets temporaires concernés du cache.

Réutilisation d’objets temporaires en cache

Qu’est-ce qui permet la réutilisation d’objets temporaires ?…

Pour pouvoir permettre à SQL Server de réutiliser efficacement des objets temporaires en cache, qu’il s’agisse de tables variables, de tables temporaires,… il est important de prévoir leur encapsulation au sein d’une requête non-ad hoc. En l’occurrence, une procédure stockée ou une fonction (user-defined ou table-valued).

Les triggers peuvent également contribuer à la réutilisation des objets temporaires en cache.

… Et leur non-réutilisation ?

De nombreux cas de figures peuvent contribuer à la non-réutilisation d’objets temporaires en cache, et notamment :

  • Une requête DDL (Data Definition Language) est execute sur un objet temporaire lors de la creation de celui-ci, il n’y a pas de mise-en-cache. C’est le cas, par exemple, de la création d’un index (ou de statistiques) sur une table temporaire créée.
  • L’utilisation de contraintes peut empêcher la réutilisation d’objets en cache.
  • La réutilisation d’objets temporaires n’est pas possible s’ils sont créés via du T-SQL dynamique.
  • Idem s’ils sont encapsulés au sein d’une procédure stockée utilisant WITH RECOMPILE.
  • L’utilisation de contraintes nommées.

Remarques sur les statistiques

Dans le cas des objets temporaires, l’auto-création de statistiques (auto-stats) est liée à une table temporaire en cache via son object_id. En revanche, cela ne signifie pas que dans le cas de plusieurs contextes d’exécution (i.e., sous-ensembles d’un plan d’exécution) d’une même requête, chaque objet temporaire peut avoir des statistiques automatiquement créés pour lui. Pourquoi ? Pour la simple raison que les auto-stats sont utilisés pour la compilation du plan d’exécution parent, sachant que les contextes d’exécution sont dérivés de ce même plan.

Notons que la création explicite de statistiques (via CREATE STATISTICS) ne contribue pas à la réutilisation d’objets temporaires en cache, cette opération étant une opération DDL.

Audit pratique des objets temporaires en cache

Pour connaître la liste des objets temporaires en cache, il suffit de requêter la table sys.tables de tempDB, puis de surveiller les lignes de la colonne name. En effet, si un objet temporaire a son nom en hexadécimal (de 8 caractères) préfixé d’un #, cela signifie qu’il est en cache.

Petite démonstration de la mise-en-cache d’objets temporaires

Créons des tables temporaires et des tables variables au sein d’une procédure stockée appelée MaProcStock :

USE tempdb
GO
CREATE PROCEDURE dbo.MaProcStock
AS
 BEGIN
   CREATE TABLE #T1_temp(machin int NULL);
   CREATE TABLE #T2_temp(truc int NULL);
   CREATE TABLE #T3_temp(bidule int NULL);

   DECLARE @T1_var AS TABLE (tartenpion int NULL);
   DECLARE @T2_var AS TABLE (machine int NULL);
   DECLARE @T3_var AS TABLE (trucmuche int NULL);
 END
GO

Affichons maintenant la liste des objets temporaires en cache après exécution de la procédure stockée :

USE tempdb
GO
DBCC FREEPROCCACHE;
EXEC dbo.MaProcStock;
GO

SELECT t.*
FROM tempdb.sys.tables t
GO

Résultat :


Comme on peut le constater, nous avons 6 objets temporaires en cache, soient ceux qui ont été créés au sein de notre procédure stockée plus haut.

Une autre manière de connaître le nombre d’objets temporaires en cache et en cours d’utilisation est d’utiliser les DMV (Dynamic Management Views) afin de récolter les informations de CACHESTORE_TEMPTABLES via sys.dm_os_memory_cache_counters :

SELECT dm.name,
   dm.type,
   dm.entries_count,
   dm.entries_in_use_count
FROM sys.dm_os_memory_cache_counters dm
WHERE dm.type =N'CACHESTORE_TEMPTABLES'
GO

Résultat :


En outre, le Perfmon peut fournir les mêmes informations via le compteur SQL Server : Plan Cache. Mais si vous êtes éperdument amoureux des DMV, vous pouvez requêter directement le Perfmon via sys.dm_os_performance_counters :

SELECT p.object_name,p.counter_name,
   p.cntr_value
FROM sys.dm_os_performance_counters p
WHERE p.object_name LIKE N'MSSQL%Plan Cache%'
AND p.instance_name = N'Temporary Tables & Table Variables'
AND p.counter_name IN (N'Cache Object Counts', N'Cache Objects in use')
GO

Résultat :


Petite démonstration de la non-mise-en-cache d’objets temporaires

Comme évoqué en début de billet, il existe des cas de figures où les objets temporaires ne peuvent pas être réutilisés en cache. Pour valider cette théorie, reprenons notre procédure stockée créée précédemment en indexant la table temporaire T1_temp dès sa création :

USE tempdb
GO
CREATE PROCEDURE dbo.MaProcStock
AS
 BEGIN
    CREATE TABLE #T1_temp(machin int NULL);
    CREATE TABLE #T2_temp(truc int NULL);</span>
    CREATE TABLE #T3_temp(bidule int NULL);</span>

    DECLARE @T1_var AS TABLE (tartenpion int NULL);
    DECLARE @T2_var AS TABLE (machine int NULL);</span>
    DECLARE @T3_var AS TABLE (trucmuche int NULL);</span>

    CREATE INDEX T1_temp_NC_Index ON #T1(machin);
 END
GO

Nettoyons le cache, puis affichons maintenant la liste des objets temporaires en cache après exécution de la procédure stockée :

USE tempdb
GO
DBCC FREEPROCCACHE;
EXEC dbo.MaProcStock;
GO

SELECT t.*
FROM sys.tables t
GO

Résultat :


Comme on peut le noter, sur les 6 objets temporaires créés, seuls 5 ont été pris en compte. Et vous pouvez deviner lesquels…

Pour aller plus loin…

Le billet a permis de voir que SQL Server permet de placer en cache des (portions ou non) d’objets temporaires créés au sein de tempDB permettant ainsi leur réutilisation. Dans un autre billet, nous ferons une comparaison des tables temporaires avec les tables variables au niveau de leurs avantages et de leurs inconvénients.


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