Optimisation des performances de la base de données tempdb

Mis à jour : 5 décembre 2005

La taille et l'emplacement physique de la base de données tempdb peuvent influer sur les performances d'un système. Par exemple, si la taille est trop petite, il se peut qu'à chaque redémarrage de l'instance SQL Server, une partie de la charge de traitement du système soit absorbée par l'ajustement automatique de tempdb à la taille nécessaire à la gestion de la charge de travail. Vous pouvez éviter cette surcharge en augmentant les tailles du fichier journal et des données de tempdb. Pour plus d'informations sur la détermination de l'espace disque nécessaire à tempdb, consultez Planification des capacités de tempdb.

Recommandations sur l'emplacement et la taille de tempdb

Pour optimiser les performances de tempdb, il est recommandé de lui appliquer la configuration suivante dans un environnement de production :

• Définissez le mode de récupération de tempdb à la valeur SIMPLE. Ce mode récupère automatiquement l'espace strictement nécessaire au journal.
  Pour plus d'informations, consultez ALTER DATABASE (Transact-SQL) ou Procédure : affichage ou modification du mode de récupération d'une base de données (SQL Server Management Studio).

• Autorisez les fichiers de tempdb à croître automatiquement en fonction des besoins. De cette façon, le fichier peut augmenter de volume jusqu'à ce que le disque soit plein.

  Remarque :
  Si l'environnement de production ne peut accepter les risques de délai d'attente susceptibles d'intervenir pendant les opérations de croissance automatique, pré-allouez l'espace nécessaire à la charge de travail attendue.

• Définissez l'incrément de croissance de la taille du fichier avec une taille suffisante afin d'éviter que la valeur de croissance des fichiers de la base de données tempdb ne soit trop faible. Si, au vu de la quantité de données à écrire dans la base de données tempdb, l'incrément de croissance est trop faible, la base de données tempdb risque d'avoir à se développer en permanence, affectant ainsi les performances. Nous recommandons d'appliquer les règles générales suivantes lors de la définition de l'incrément FILEGROWTH des fichiers tempdb :

  Taille des fichiers de tempdb	Incrément FILEGROWTH
  0 à 100 Mo	10 Mo
  100 à 200 Mo	20 Mo
  200 Mo ou plus	10%*

  * Peut-être devrez-vous ajuster ce pourcentage en fonction de la vitesse du sous-système d'E/S sur lequel résident les fichiers de tempdb. Pour éviter les risques de délais d'attente de verrous, il est recommandé que l'opération de croissance automatique n'excède pas plus de deux minutes environ. Par exemple, si le sous-système d'E/S peut initialiser un fichier au rythme de 50 Mo par seconde, l'incrément FILEGROWTH ne doit pas dépasser 6 Go, quelle que soit la taille des fichiers de tempdb. Si possible, utilisez l'initialisation instantanée des fichiers de base de données pour améliorer les performances des opérations de croissance automatique.

• Pré-allouez l'espace de tous les fichiers de tempdb en définissant leur taille avec une valeur suffisamment élevée pour assumer la charge de travail habituelle de l'environnement. Cela évite que tempdb ne se développe trop fréquemment et que les performances n'en soient affectées. La base de données tempdb doit être définie de façon à autoriser la croissance automatique, mais celle-ci doit être utilisée pour augmenter l'espace disque en cas d'exceptions non prévues.

• Créez autant de fichiers que nécessaire pour optimiser la bande passante disque. L'utilisation de plusieurs fichiers réduit les conflits de stockage de tempdb et entraîne une évolutivité bien meilleure. Cependant, un nombre trop élevé de fichiers peut provoquer une dégradation des performances et une augmentation des charges de gestion. À titre d'instruction générale, créez un fichier de données pour chaque UC du serveur (en prenant en compte les paramètres du masque d'affinité),puis augmentez ou diminuez le nombre de fichiers en fonction des besoins. Notez qu'une UC à double noyau est traitée comme deux UC distinctes.

• L'attribution de la même taille à chaque fichier de données permet une optimisation des performances du remplissage proportionnel.

• Placez la base de données tempdb sur un sous-système d'E/S rapide. Si plusieurs disques sont directement attachés, utilisez l'agrégation de disques.

• Placez la base de données tempdb sur des disques différents de ceux employés par les bases de données utilisateur.

Pour obtenir des recommandations supplémentaires, consultez Utilisation de tempdb dans SQL Server 2005 (page pouvant être en anglais).

Modification des paramètres de taille et de croissance de tempdb

Les paramètres de taille et de croissance des fichiers de données ou des fichiers journaux de tempdb peuvent être modifiés à l'aide de l'une des méthodes suivantes :

• instruction ALTER DATABASE
• SQL Server Management Studio

Les valeurs des paramètres de taille et de croissance sont utilisées à chaque création de tempdb. Par exemple, si vous augmentez la taille du fichier de données de tempdb à 20 Mo et l'incrément de croissance à 15 %, les nouvelles valeurs entrent immédiatement en vigueur. Si les activités transactionnelles ultérieures entraînent une augmentation de la taille de tempdb, la taille du fichier de données est rétablie à 20 Mo à chaque redémarrage de l'instance de SQL Server.

Affichage des paramètres de taille et de croissance de tempdb

Les paramètres de taille et de croissance des fichiers de données ou des fichiers journaux de tempdb peuvent être visualisés à l'aide de l'une des méthodes suivantes :

• SQL Server Management Studio

• Exécution de la requête suivante.

  SELECT 
      name AS FileName, 
      size*1.0/128 AS FileSizeinMB,
      CASE max_size 
          WHEN 0 THEN 'Autogrowth is off.'
          WHEN -1 THEN 'Autogrowth is on.'
          ELSE 'Log file will grow to a maximum size of 2 TB.'
      END,
      growth AS 'GrowthValue',
      'GrowthIncrement' = 
          CASE
              WHEN growth = 0 THEN 'Size is fixed and will not grow.'
              WHEN growth > 0 AND is_percent_growth = 0 
                  THEN 'Growth value is in 8-KB pages.'
              ELSE 'Growth value is a percentage.'
          END
  FROM tempdb.sys.database_files;
  GO
  

Voir aussi

Tâches

Résolution des problèmes d'espace disque insuffisant dans tempdb

Concepts

Base de données tempdb
Planification des capacités de tempdb

Autres ressources

Optimisation de bases de données
ALTER DATABASE (Transact-SQL)
sys.database_files (Transact-SQL)

Aide et Informations

Assistance sur SQL Server 2005

Historique des modifications