Lorsqu’il s’agit de surveiller les bases de données système de SQL Server, la base de données tempdb est l’une des plus importantes à prendre en compte, car elle contient la plupart des objets créés en interne. Outre certaines caractéristiques uniques de la base de données tempdb elle-même (comme le magasin de versions, par exemple), qui seront abordées ultérieurement séparément, ses performances et son utilisation de l’espace sont des domaines cruciaux à surveiller (et à optimiser également), car la tempdb est la base de données la plus active et la ressource commune dans un environnement SQL Server.

En général, la configuration et les performances de la base de données tempdb dépendent fortement de l’environnement lui-même (ressources physiques), de la charge de travail (quantité d’opérations parallèles telles que la création d’objets temporaires, par exemple) et des applications qui utilisent les ressources de SQL Server. Cet article se concentrera uniquement sur les techniques de surveillance de base de la tempdb, concernant l’utilisation de l’espace disque de la tempdb par ses fichiers de base de données, ainsi que sur certaines informations spécifiques sur la base de données elle-même (date de création et modèle de récupération), et examinera l’utilisation de l’espace des éléments spécifiques de la tempdb tels que les objets utilisateur, les objets internes et le magasin de versions.

Surveiller les fichiers de la base de données tempdb et recueillir d’autres informations

Pour surveiller les statistiques de la base de données tempdb de SQL Server, vous pouvez exécuter la requête suivante. La première partie de la requête révèle la taille actuellement allouée du fichier de données, du fichier journal et de l’espace utilisé par le fichier journal :

-- Première partie du script
SELECT instance_name AS 'Base de données',
       [Data File(s) Size (KB)] / 1024 AS [Fichier de données (Mo)],
       [Log File(s) Size (KB)] / 1024 AS [Fichier journal (Mo)],
       [Log File(s) Used Size (KB)] / 1024 AS [Espace utilisé par le fichier journal (Mo)]
FROM (
    SELECT *
    FROM sys.dm_os_performance_counters
    WHERE counter_name IN ('Data File(s) Size (KB)', 'Log File(s) Size (KB)', 'Log File(s) Used Size (KB)')
    AND instance_name = 'tempdb'
) AS A
PIVOT (
    MAX(cntr_value)
    FOR counter_name IN ([Data File(s) Size (KB)], [LOG File(s) Size (KB)], [Log File(s) Used Size (KB)])
) AS B
GO

-- Deuxième partie du script
SELECT create_date AS [Date de création],
       recovery_model_desc AS [Modèle de récupération]
FROM sys.databases
WHERE name = 'tempdb'
GO

La deuxième partie montre exactement quand la tempdb a été créée et quel modèle de récupération elle utilise.

Pour obtenir la taille totale de la base de données sans les détails, utilisez cette requête :

SELECT SUM(size) / 128 AS [Taille totale de la base de données (Mo)]
FROM tempdb.sys.database_files

Étant donné que SQL Server crée automatiquement la base de données tempdb à partir de zéro à chaque démarrage du système, et que sa taille de fichier de données initiale par défaut est de 8 Mo (à moins qu’elle ne soit configurée et ajustée différemment selon les besoins de l’utilisateur), il est facile de consulter et de surveiller les statistiques des fichiers de base de données à l’aide de la requête ci-dessus. Dans ce cas, la valeur de taille initiale par défaut est définie, et le résultat montre que la base de données s’est étendue d’un facteur de cinq fois, et cette taille totale de base de données indique une activité légère à moyenne sur une période de temps plus longue d’un serveur SQL spécifié où se trouve la tempdb.

Si nécessaire, comme pour toute autre base de données régulière, les utilisateurs peuvent surveiller les index contenus dans la base de données tempdb.

Surveiller l’utilisation de l’espace par les objets spécifiques de la tempdb

Ces objets sont : l’espace libre de la base de données et trois éléments propres à la tempdb : les objets internes – créés par SQL Server pour traiter les requêtes (reconstruction d’index en ligne, récupération de tables et de vues système, jointure de hachage, etc.), les objets utilisateur – créés par les utilisateurs (tables temporaires, variables de table, etc.), le magasin de versions – archive des pages de données nécessaires pour prendre en charge les applications qui utilisent la fonctionnalité de versionnement des lignes dans SQL Server (triggers, par exemple).

Utilisez la requête suivante pour obtenir des informations sur l’utilisation de l’espace par des objets spécifiques de la tempdb :

SELECT (SUM(unallocated_extent_page_count) / 128) AS [Espace libre (Mo)],
       SUM(internal_object_reserved_page_count) * 8 AS [Objets internes (Ko)],
       SUM(user_object_reserved_page_count) * 8 AS [Objets utilisateur (Ko)],
       SUM(version_store_reserved_page_count) * 8 AS [Magasin de versions (Ko)]
FROM sys.dm_db_file_space_usage
WHERE database_id = 2 -- database_id '2' représente tempdb

Comme mentionné précédemment, la tempdb utilisée pour la référence dans cet article est placée sur un serveur SQL avec une activité généralement légère, et actuellement, aucune des fonctionnalités de versionnement des lignes n’est utilisée (colonne Magasin de versions (Ko)).

Surveiller l’utilisation de l’espace des tables temporaires

De plus, si une grande quantité de données est stockée dans des tables temporaires en raison de l’activité plus élevée du serveur SQL, vérifiez le nombre de lignes et l’espace utilisé/réservé de chacune des tables temporaires qui ont été créées dans une base de données spécifique :

USE <nom_de_la_base_de_données>

SELECT tb.name AS [Nom de la table temporaire],
       stt.row_count AS [Nombre de lignes],
       stt.used_page_count * 8 AS [Espace utilisé (Ko)],
       stt.reserved_page_count * 8 AS [Espace réservé (Ko)]
FROM tempdb.sys.partitions AS prt
INNER JOIN tempdb.sys.dm_db_partition_stats AS stt
    ON prt.partition_id = stt.partition_id
    AND prt.partition_number = stt.partition_number
INNER JOIN tempdb.sys.tables AS tb
    ON stt.object_id = tb.object_id
ORDER BY tb.name

Dans ce cas, nous avons utilisé une base de données de test personnalisée. Les objets temporaires marqués ci-dessus dans la capture d’écran sont des tables créées par l’utilisateur. Notez que tous les objets temporaires seront supprimés lors du redémarrage du service SQL Server ou de la machine, et les informations les concernant ne peuvent être récupérées que pendant la session utilisateur active.