[SQL Server] TempDB : comment traiter les problèmes de volumétrie ?

Ce billet offre quelques astuces pour identifier et faire face à des sources de croissance excessive de tempDB.

Pour comprendre l’utilité de tempDB et les éléments d’optimisation usuels, allez ici.

Identification des soucis de volumétrie avec…

… le journal d’erreurs (ERRORLOG)

Lorsqu’un problème de volumétrie relatif à tempDB intervient, SQL Server est souvent amené à en donner sa description dans son journal d’erreurs. Le tableau ci-dessous liste les principaux codes d’erreurs et messages associés aux soucis de volumétrie que peut rencontrer tempDB :

Code(s) d’erreur Message
1101, 1105
A session has to allocate more space in TEMPDB in order to continue.
Une session doit allouer de l’espace dans tempDB pour continuer à travailler.
3959
The version store is full.
La banque des versions est pleine. Intervient généralement après l’erreur 1101 ou 1105.
3967 The version store has been forced to shrink because TEMPDB is
full.
La banque de version a eu une réduction de taille forcée suite à la saturation de tempDB.
3958, 3966 A transaction is unable to find a required version record
in TEMPDB.
Une transaction est dans l’incapacité de trouver un enregistrement de version au sein de tempDB.

… le Perfmon

Le Perfmon (Performance Monitor) offre des compteurs utiles permettant d’auditer les contentions potentielles relatives aux allocation/désallocation au sein de SQL Server. Voici une liste (non-exhaustive) utile :

Compteur Description
SQL Server: Database: Data File(s) Size(KB): TEMPDB

Indique la taille globale du (ou des) fichier(s) de données de tempDB.
SQL Server: Database: Log File(s) Used Size(KB): TEMPDB
Indique la taille globale du (ou des) fichier(s) de logs de tempDB.
SQL Server: Transactions: Free Space in TEMPDB (KB) Indique la quantité d’espace disponible au sein de tempDB.

D’autres compteurs utiles peuvent être utilisés, notamment ceux permettant d’auditer le versioning en ligne : http://msdn.microsoft.com/en-us/library/ms189038.aspx.

… les DMV

Avec les DMV (Dynamic Management Views, seulement à partir de SQL Server 2005), il est possible de procéder à une identification de contentions potentielles.

  • La requête ci-dessous utilise la DMV sys.dm_db_file_space_usage pour identifier la quantité d’espace alloué aux différentes zones de tempDB :
SELECT
   SUM (user_object_reserved_page_count)*8 AS user_objects_kb,
   SUM (internal_object_reserved_page_count)*8 AS internal_objects_kb,
   SUM (version_store_reserved_page_count)*8 AS version_store_kb
FROM sys.dm_db_file_space_usage
GO

La requête ci-dessus permet d’avoir une idée de comment les objets sont utilisés au sein de tempDB. Ainsi :

  • Pour les objets temporaires créés par les utilisateurs (user_objects_kb) : une valeur trop élevée indique que les objets créés par des utilisateurs (ou applications) sont de gros consommateurs de tempDB. Cela ne signifie pas forcément qu’il y ait un problème, mais peut donner un élément de réponse.
  • Pour les objets internes (internal_objects_kb) : une valeur trop élevée indique que des plans d’exécution abusent de tempDB. Cela peut ne pas être un problème, mais peut constituer un élément de réponse nécessitant de d’auditer les requêtes concernées afin de les optimiser pour se prémunir contre tout risque potentiel de contention.
  • Pour le versioning en ligne (version_store_kb) : une valeur trop élevée peut indiquer une difficulté pour SQL Server à supprimer des pages de versioning en ligne. Il serait de bon ton de vérifier s’il n’y a pas une requête qui empêche le nettoyage de version (cas des transactions trop longues, à moins qu’il s’agisse de transactions générant de très grand nombre de versions par minute…).
  • La requête ci-dessous utilise la DMV sys.dm_tran_active_snapshot_database_transactions pour identifier les transactions actives (i.e., pas encore validées) les plus longues:
SELECT transaction_id
FROM sys.dm_tran_active_snapshot_database_transactions
ORDER BY elapsed_time_seconds DESC
GO

Les transactions de longue durée peuvent avoir un impact significatif sur la volumétrie de tempDB vu qu’elles utilisent le version store de grande taille. En effet, le version store est utilisé pour conserver toutes les versions générées depuis le début des transactions concernées.

A noter que même si les opérations de reconstruction d’index en ligne (à partir de SQL Server 2008 Enterprise Edition) sont des transactions longues, un version store distinct et dédié à ce type d’opérations est utilisé, ce qui permet à SQL Server de supprimer des version stores de transactions « traditionnelles » sans problème durant une reconstruction d’index en ligne.

Résolution de problèmes de volumétrie

Bonnes pratiques usuelles

Outre la nécessité d’avoir une partition suffisamment volumineuse pour tempDB et assez de RAM (pour le buffer cache) afin de réduire (dans la mesure du possible) l’utilisation de tempDB, les bonnes pratiques ci-dessous (liste non-exhaustive) permettent de faire face à une éventuelle croissance excessive de tempDB :

  • Assurez-vous que la valeur d’autogrowth n’est pas trop élevée (généralement, 10% de la taille de tempDB peut suffire dans la majorité des scénarii d’utilisation de tempDB).
  • Ne retournez que les enregistrements nécessaires.
  • Limitez l’utilisation d’UNION ou de DISTINCT.
  • Utilisez des requêtes plus courtes.
  • Evitez l’agrégation d’un nombre excessif de données.
  • Indexez intelligemment (voir ici, et notamment la section Index en cluster et un index non-cluster).
  • Si possible, limitez l’abus de tables temporaires (globales ou locales) si vous pouvez utiliser des tables de travail permanentes.
  • Ne triez pas les enregistrements qui n’ont pas besoin de l’être.
  • Evitez d’utiliser des curseurs statiques ou de type keyset.
  • Evitez d’utiliser des tables variables qui retournent un trop grand nombre d’enregistrements.
  • Limitez l’utilisation de jointures qui indiquent des hash joins dans le plan d’exécution. Réécrivez votre requête ou utilisez une meilleure indexation.
  • Limitez l’utilisation de triggers.
  • Planifiez la vérification de l’intégrité des bases de données (via DBCC CHECKDB) à des périodes plus calmes, surtout si la (ou les) base(s) concernée(s) est une VLDB (voir ici, et notamment la section Mise-en-œuvre de différentes stratégies de vérification de l’intégrité des données selon divers scénarii).

Dans le cas d’une opération de création ou reconstruction d’indexes:

  • Si vous utilisez l’option SORT_IN_TEMPDB (pour accélerer la réindexation), planifiez cela à des périodes moins denses en activité.
  • Si vous utilisez la reconstruction d’index en ligne (à partir de SQL Server 2008 Enterprise Edition), qui utilise le row-versioning et donc tempDB, planifiez cette opération à des périodes de faible activité.
Notons que les niveaux d’isolation utilisant le row-versioning sont également des sources de consommation de tempDB. C’est le cas des niveaux d’isolation SNAPSHOT et READ-COMMITTED SNAPSHOT. Bien que ces niveaux d’isolation renferment beaucoup d’avantages (limitation des deadlocks, moins de verrous pour une transaction,…) leur emploi doit se faire avec prudence.

Remarques sur le shrink de tempDB

Le shrink est une opération de réduction physique de (fichier(s)) de base(s) de données. Si tempDB grossit de manière excessive (suite à l’exécution d’une requête inoptimisée, par exemple), et que cette situation est juste exceptionnelle, la réduction, avec une opération de shrink, de tempDB à une taille plus correcte peut être éventuellement envisagée (via DBCC SHRINKDATABASE (tempDB,<pourcentage_cible>) ou DBCC SHRINKFILE (<nom_fichier_tempDB>, <taille_cible>)).

En règle générale, dans la mesure où la base tempDB est disposée à croître (rapidement ou non), l’utilisation d’une opération de shrink n’est pas recommandée sous peine d’initier un nouveau cycle de croissance pouvant impacter les ressources de la machine.

La meilleure façon de réduire physiquement la taille de tempDB est de redémarrer l’instance SQL Server concernée. Cela permettra de recréer tempDB à sa taille initiale (qui doit être idéalement préallouée à une taille plus conventionnelle, 8 Mo étant trop petit).

Pour reprendre la section Dimensionnement du billet [SQL Server] TempDB : Présentation et éléments d’optimisation,
la taille idéale à pré-allouer peut être déterminée de 2 façons (pas forcément indépendantes):

  • Utilisation de DBCC CHECKDB sur la plus grosse base applicative (de l’instance SQL Server à laquelle appartient tempDB)
    avec l’option ESTIMATEONLY et surveillance de la valeur de Estimated tempdb space needed for checktables (kb) qui pourra servir de valeur de référence.
  • Si la base tempDB est placée au sein d’une partition dédiée et suffisamment volumineuse, pré-allouez 80% de la taille de ladite partition.
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