Partager via

  • Article

Optimisation des performances de la base de données tempdb

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 définie pour tempdb 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.

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 :

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

Détection d'erreurs du chemin d'accès de l'E/S disque

Lorsqu'elle est définie en CHECKSUM, l'option PAGE_VERIFY découvre des pages de bases de données endommagées provoquées par les erreurs du chemin d'accès de l'E/S disque et signale ces erreurs, telles que MSSQLSERVER_823, MSSQLSERVER_824, ou MSSQLSERVER_825, dans le journal des erreurs SQL. Les erreurs de chemin d'E/S disque peuvent endommager la base de données et résultent généralement d'une défaillance matérielle des disques ou de pannes d'alimentation survenant au moment de l'écriture de la page sur le disque. Pour plus d'informations sur les erreurs d'E/S, consultez Microsoft SQL Server I/O Basics, Chapter 2 (en anglais).

Dans les versions antérieures de SQL Server, l'option de base de données PAGE_VERIFY est définie à NONE pour la base de données tempdb et ne peut pas être modifiée. Dans SQL Server 2008, la valeur par défaut pour la base de données tempdb est CHECKSUM pour les nouvelles installations de SQL Server. Lorsque vous mettez à niveau une installation SQL Server, la valeur par défaut reste NONE. Nous vous recommandons de définir l'option PAGE_VERIFY de la base de données tempdb en CHECKSUM.