[SQL Server] TempDB : présentation et éléments d’optimisation

Ce billet présente tempDB, ainsi que quelques éléments d’optimisation.

Présentation de tempDB

A quoi sert tempDB ?

TempDB est une base de données système similaire aux bases de données applicatives, à la différence que les données stockées ne sont pas persistantes. En effet, tempDB permet de stocker des activités transactionnelles ainsi que des objets volatiles, à savoir :

  • Des objets créés par l’utilisateur : tables temporaires (locales ou globales), tables et indexes non-systèmes, tables renvoyées par des fonctions tables, tables variables,…
  • Des objets internes :
    • Fichiers de travail pour…
      • … le tri venant de la création ou reconstruction d’indexes (SORT_IN_TEMPDB).
      • … diverses opérations transactionnelles telles que les requêtes utilisant ORDER BY, GROUP BY, UNION, SORT, SELECT DISTINCT,…
    • Tables de travail pour…
      • … les commandes DBCC CHECKTABLE et DBCC CHECKDB.
      • … traiter les curseurs.
      • … les objets du Service Broker (messagerie, notifications,…).
      • … les opérations de hachage, comme les agrégations et jointures.
  • Le Version Store (ou banque de versions) qui est une collection de pages de données utilisées pour le contrôle des versions de ligne (ou versioning de ligne). On y retrouve :
    • Les versions de ligne nécessaires aux triggers de type AFTER, aux ensembles de résultats actifs multiples (Multiple Active ResultSet ou MARS) et aux opérations de reconstruction d’indexes en ligne (à partir de SQL Server 2008 Enterprise Edition).
    • Les versions de ligne générées par le niveau d’isolation SNAPSHOT ou READ-COMMITTED SNAPSHOT.

Les autres éléments principaux différenciant tempDB des bases applicatives sont les suivants :

  • Pas de sauvegarde ou de restauration possible.
  • Pas de possibilité de suppression, attachement ou détachement.
  • Pas de possibilité de database snapshot.
  • Pas de haute-disponibilité.
  • Certaines options ne peuvent pas être utilisées (i.e., autoshrink, autoclose, database read-only).

D’autres restrictions existent. Plus de détails ici : http://msdn.microsoft.com/en-us/library/ms190768.aspx.

A noter que :

  • tempDB est en mode de récupération SIMPLE (non-modifiable), ce qui permet à SQL Server de tronquer régulièrement et automatiquement les logs. De plus, les opérations transactionnelles sont faiblement journalisées.
  • Chaque instance SQL Server ne peut avoir qu’une seule base de données tempDB.

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 favorise la réutilisation d’objets temporaires via un système appelé 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.

Eléments d’optimisation de tempDB

Stockage

Comme on peut le comprendre plus haut, tempDB est une base de données qui n’est pas exempte d’une très forte croissance. Dans la mesure où une réduction de sa taille passe nécessairement par un redémarrage de l’instance SQL Server (et donc une inaccessibilité temporaire des bases applicatives, le shrink n’étant pas recommandé pour des raisons de performances), il est alors important de prévoir une partition suffisamment volumineuse pour tempDB.

En outre, tempDB générant beaucoup d’activités en écriture, placer cette base de données dans un LUN dédié, et de préférence des disques en RAID1 ou RAID10, constitue une bonne pratique d’exploitation.

Dimensionnement

Par défaut, tempDB est créé avec un seul fichier de données de 8 Mo (tempdev)
et un seul fichier de logs de 1 Mo (templog), tous les deux configurés pour grossir automatiquement (i.e., autogrowth activé et à 10% par défaut). Beaucoup d’instances SQL Server de production nécessitent de gros volumes de données pour tempDB. Or, quand SQL Server est redémarré, la base de données tempDB est recréée (via une copie de la base système model) à sa taille initiale ce qui, par la suite, peut provoquer une forte consommation des ressources systèmes du fait de la réallocation de nouveaux extends pour le stockage de données temporaires au sein de tempDB, cette dernière étant amenée à grossir.

Pour anticiper cela, il est recommandé de pré-allouer tempDB d’une taille suffisamment conséquente de façon à réduire la charge de travail de SQL Server. Pour avoir une idée de la taille à prévoir pour tempDB une bonne astuce consiste à faire une vérification de l’intégrité de la plus grosse base de données de l’instance (ou, du moins, la plus sollicitée) via DBCC CHECKDB, mais en utilisant l’option ESTIMATEONLY et surveiller notamment la valeur de Estimated tempdb space needed for checktables (kb) qui pourra servir de valeur de référence :

DBCC CHECKDB(LaPlusGrosseBD) WITH ESTIMATEONLY
GO

Autrement, si la base tempDB est placée au sein d’une partition dédiée, et suffisamment volumineuse, préallouer 80% de l’espace disponible à tempDB est une bonne pratique.

Pour changer la taille de tempDB (les valeurs sont en Mo, et arbitraires) :

  • Fichier de données tempdev (exemple : passage à 1 Go) :
USE master
GO
ALTER DATABASE tempdb MODIFY FILE
(NAME = 'tempdev', SIZE = 1024MB)
GO
  • Fichier de logs templog (exemple : passage à 500 Mo) :
USE master
GO
ALTER DATABASE tempdb MODIFY FILE
(NAME = 'templog', SIZE = 500MB)
GO

Multiplication de fichiers de données

Si l’activité est critique et susceptible de beaucoup solliciter tempDB, vous pouvez créer un nombre de fichiers de données tempDB égal à de ¼ à ½ CPUs. Cela permet de notamment faire face à tout risque de contention durant l’allocation d’extents, en optimisant les opérations I/O.

Le script T-SQL suivant peut être utilisé (exemple de création de 3 fichiers de données supplémentaires de 1 Go et 100Mo de filegrowth s’ajoutant au fichier de données principal tempdev.mdf sur une machine contenant 8 CPUs) :

ALTER DATABASE [tempdb] ADD FILE
(NAME =N'tempdb2',FILENAME= N'T:\MSSQL\DATA\tempdb2.mdf',
SIZE = 1000MB , FILEGROWTH = 100MB )
GO
ALTER DATABASE [tempdb] ADD FILE
(NAME =N'tempdb3',FILENAME= N'T:\MSSQL\DATA\tempdb3.mdf',
SIZE = 1000MB , FILEGROWTH = 100MB )
GO
ALTER DATABASE [tempdb] ADD FILE
(NAME =N'tempdb4',FILENAME= N'T:\MSSQL\DATA\tempdb4.mdf',
SIZE = 1000MB , FILEGROWTH = 100MB )
GO
Notons que:

  • Les fichiers de données de tempDB doivent être de même taille et avec les mêmes propriétés de croissance, comme le filegrowth (pour comprendre comment SQL Server gère l’allocation d’extents au sein d’un filegroup de fichiers multiples, référez-vous ici).
  • tempDB ne peut contenir qu’un seul filegroup.
  • Il est important de savoir qu’un trop grand nombre de fichiers de données peut impacter les performances I/O du fait de risques potentiels relatifs à la présence d’un grand nombre de threads parallèles en attente (plus d’explications ici). En règle générale, au-delà de 8 fichiers de données, le maintien des fichiers multiples prend le pas sur les bénéfices relatifs à l’optimisation de l’allocation d’extents.
  • Il n’est pas obligatoire de placer les fichiers de tempDB sur des LUNs/disques différents, à moins qu’il y ait régulièrement des goulots d’étranglements I/O.
  • La règle très connue du « 1 fichier de données par CPU » n’est plus d’actualité à partir de SQL Server 2005. De plus,  concernant l’activation du traceflag –T1118, ce n’est plus nécessité absolue à partir de SQL Server 2005, mais cela peut toujours aider si les contentions persistent. Plus de détails ici: http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-(1230)-tempdb-should-always-have-one-data-file-per-processor-core.aspx et http://www.sqlskills.com/blogs/paul/misconceptions-around-tf-1118/.

Initialisation instantanée de fichiers

L’initialisation instantanée de fichiers (ou Instant file initialization) est une fonctionnalité très utile pour une base de données comme tempDB car elle permet d’optimiser le processus de croissance de son (ou ses) fichier(s) de données. Plus de détail ici.

Options de création et mise-à-jour automatiques de statistiques

Parmi les options activées par défaut pour tempDB, deux ont une importance non-négligeable : auto create statistics et auto update statistics.

Ces deux options doivent être laissées activées. En effet, elles permettent à SQL Server d’automatiquement créer et mettre-à-jour les statistiques des objets temporaires dans le but de booster les performances des opérations qui les utilisent.

Pour aller plus loin…

Dans ce billet, nous avons abordé les grandes lignes relatives à tempDB. Dans le futur, d’autres sujets autour de tempDB seront traités (i.e., traitement de contentions, volumétrie,…).

Références

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