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

Cette rubrique propose des procédures et des recommandations pour vous aider à diagnostiquer et à résoudre les problèmes dus à un espace disque insuffisant dans la base de données tempdb. Un espace disque insuffisant dans tempdb peut générer des perturbations significatives dans l'environnement de production SQL Server et peut empêcher les applications en cours d'exécution de terminer les opérations en cours.

Espace requis pour tempdb

La base de données système tempdb constitue une ressource globale accessible par tous les utilisateurs connectés à une instance SQL Server. La base de données tempdb permet de stocker les objets suivants : objets utilisateur, objets internes et banques de versions. Dans SQL Server 2005, tempdb nécessite plus d'espace disque que dans les versions précédentes de SQL Server. Pour plus d'informations, consultez Planification des capacités de tempdb.

Vous pouvez utiliser la vue de gestion dynamique sys.dm_db_file_space_usage pour surveiller l'espace disque utilisé par les objets utilisateur, les objets internes et les banques de versions, dans les fichiers de tempdb. En outre, pour surveiller l'activité d'allocation et de désallocation de pages dans tempdb au niveau des sessions ou des tâches, vous pouvez utiliser les vues de gestion dynamique sys.dm_db_session_space_usage et sys.dm_db_task_space_usage. Ces vues permettent d'identifier des requêtes, des tables temporaires et des variables de table de grande ampleur qui utilisent un espace disque volumineux dans tempdb.

Diagnostic des problèmes d'espace disque dans tempdb

Le tableau ci-dessous répertorie les messages d'erreur qui indiquent un espace disque insuffisant dans la base de données tempdb. Ces erreurs figurent dans le journal d'erreurs de SQL Server et peuvent également être retournées à toute application en cours d'exécution.

Des problèmes d'espace disque dans tempdb sont également indiqués lorsque la base de données est configurée pour croître automatiquement et que la taille de la base de données augmente rapidement.

Analyse de l'espace disque dans tempdb

Les exemples ci-dessous montrent comment déterminer la quantité d'espace disponible dans tempdb, ainsi que l'espace utilisé par la banque des versions et les objets utilisateur et internes.

Détermination de la quantité d'espace disponible dans tempdb

La requête ci-dessous retourne le nombre total de pages disponibles et l'espace disponible total en mégaoctets (Mo) inclus dans tous les fichiers, dans tempdb.

SELECT SUM(unallocated_extent_page_count) AS [free pages], 
(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM sys.dm_db_file_space_usage;

Détermination de la quantité d'espace utilisée par la banque des versions

La requête ci-dessous retourne le nombre total de pages utilisées par la banque des versions et l'espace total en Mo utilisé par la banque des versions dans tempdb.

SELECT SUM(version_store_reserved_page_count) AS [version store pages used],
(SUM(version_store_reserved_page_count)*1.0/128) AS [version store space in MB]
FROM sys.dm_db_file_space_usage;

Détermination de la transaction la plus longue

Si la banque des versions utilise beaucoup d'espace dans tempdb, vous devez déterminer quelle transaction est la plus longue à s'exécuter. Utilisez la requête ci-dessous pour répertorier les transactions actives en les ordonnant selon leur durée d'exécution.

SELECT transaction_id
FROM sys.dm_tran_active_snapshot_database_transactions 
ORDER BY elapsed_time_seconds DESC;

Une transaction de longue durée qui n'est pas liée à une opération d'index en ligne nécessite une banque des versions de grande taille. Cette banque des versions conserve toutes les versions générées depuis le début de la transaction. Les transactions de construction d'index en ligne peuvent être longues, mais une banque des versions distincte dédiée aux opérations d'index en ligne est utilisée. Par conséquent, ces opérations n'empêchent pas la suppression des versions provenant d'autres transactions. Pour plus d'informations, consultez Utilisation de la ressource de versioning de ligne.

Détermination de la quantité d'espace utilisée par les objets internes

La requête ci-dessous retourne le nombre total de pages utilisées par les objets internes et l'espace total en Mo utilisé par les objets internes dans tempdb.

SELECT SUM(internal_object_reserved_page_count) AS [internal object pages used],
(SUM(internal_object_reserved_page_count)*1.0/128) AS [internal object space in MB]
FROM sys.dm_db_file_space_usage;

Détermination de la quantité d'espace utilisée par les objets utilisateur

La requête ci-dessous retourne le nombre total de pages utilisées par les objets utilisateur et l'espace total en Mo utilisé par les objets utilisateur dans tempdb.

SELECT SUM(user_object_reserved_page_count) AS [user object pages used],
(SUM(user_object_reserved_page_count)*1.0/128) AS [user object space in MB]
FROM sys.dm_db_file_space_usage;

Détermination de la quantité totale d'espace (disponible et utilisé)

La requête ci-dessous retourne la quantité totale d'espace disque utilisée par tous les fichiers dans tempdb.

SELECT SUM(size)*1.0/128 AS [size in MB]
FROM tempdb.sys.database_files

Analyse de l'espace utilisé par les requêtes

Un des problèmes les plus courants liés à l'utilisation d'espace dans tempdb est associé aux requêtes de grande ampleur qui utilisent une grande quantité d'espace. En général, cet espace est utilisé pour les objets internes, tels que les tables de travail ou les fichiers de travail. Bien que l'analyse de l'espace utilisé par les objets internes indique la quantité d'espace utilisée, elle n'identifie pas directement la requête qui utilise cet espace.

Les méthodes ci-dessous permettent d'identifier les requêtes qui utilisent le plus d'espace dans tempdb. La première méthode examine les données au niveau des lots et nécessite moins de données que la seconde méthode. La seconde méthode peut être utilisée pour identifier la requête, la table temporaire ou la variable de table spécifique qui consomme l'espace disque, mais des données supplémentaires doivent être collectées pour obtenir la réponse.

Méthode 1 : Informations au niveau des lots

Si la requête de lots ne contient que quelques requêtes et qu'une seule d'entre-elles est une requête complexe, cela représente en général suffisamment d'informations pour savoir quel lot consomme l'espace à la place de la requête spécifique.

Pour continuer avec cette méthode, un travail de l'Agent SQL Server doit être configuré pour interroger les vues de gestion dynamique sys.dm_db_session_space_usage et sys.dm_db_task_space_usage en utilisant une fréquence d'interrogation de quelques minutes. Une fréquence d'interrogation de trois minutes est utilisée dans l'exemple ci-dessous. Vous devez interroger les deux vues car sys.dm_db_session_space_usage n'inclut pas l'activité d'allocation de la tâche active actuellement. La comparaison des pages allouées à deux intervalles de temps vous permet de calculer le nombre de pages allouées entre ces intervalles.

Les exemples ci-dessous fournissent les requêtes requises pour le travail de l'Agent SQL Server.

A. Obtention de l'espace consommé par les objets internes dans toutes les tâches en cours d'exécution dans chaque session.

L'exemple suivant crée la vue all_task_usage. Lorsqu'elle est interrogée, la vue retourne l'espace total utilisé par les objets internes dans toutes les tâches en cours d'exécution dans tempdb.

CREATE VIEW all_task_usage
AS 
    SELECT session_id, 
      SUM(internal_objects_alloc_page_count) AS task_internal_objects_alloc_page_count,
      SUM(internal_objects_dealloc_page_count) AS task_internal_objects_dealloc_page_count 
    FROM sys.dm_db_task_space_usage 
    GROUP BY session_id;
GO

B. Obtention de l'espace consommé par les objets internes dans la session en cours pour les tâches en cours d'exécution et terminées

L'exemple suivant crée la vue all_session_usage. Lorsqu'elle est interrogée, la vue retourne l'espace total utilisé par toutes les tâches d'objets internes en cours d'exécution et terminées dans tempdb.

CREATE VIEW all_session_usage 
AS
    SELECT R1.session_id,
        R1.internal_objects_alloc_page_count 
        + R2.task_internal_objects_alloc_page_count AS session_internal_objects_alloc_page_count,
        R1.internal_objects_dealloc_page_count 
        + R2.task_internal_objects_dealloc_page_count AS session_internal_objects_dealloc_page_count
    FROM sys.dm_db_session_space_usage AS R1 
    INNER JOIN all_task_usage AS R2 ON R1.session_id = R2.session_id;
GO

Supposez que lorsque ces vues sont interrogées à trois minutes d'intervalle, les jeux de résultats fournissent les informations ci-dessous.

• À 5h00 P.M., la session 71 a alloué 100 pages et désalloué 100 pages depuis le début de la session.
• À 5h03 P.M., la session 71 a alloué 20100 pages et désalloué 100 pages depuis le début de la session.

Lorsque vous analysez ces informations, vous pouvez conclure qu'entre ces deux mesurages : La session a alloué 20 000 pages pour les objets internes et n'a pas désalloué de pages. Cela indique un problème potentiel.

Remarque :
En tant qu'administrateur de base de données, vous pouvez décider d'effectuer les interrogations à des intervalles inférieurs à trois minutes. Toutefois, si une requête s'exécute moins de trois minutes, elle ne consommera probablement pas une quantité significative d'espace dans tempdb.

Pour déterminer quel lot s'exécute pendant ce temps, utilisez le Générateur de profils SQL Server pour capturer les classes d'événements RPC:Completed et SQL:BatchCompleted.

Au lieu d'utiliser le Générateur de profils SQL Server, vous pouvez exécuter DBCC INPUTBUFFER une fois toutes les trois minutes pour toutes les sessions, comme cela est illustré dans l'exemple ci-dessous.

DECLARE @max int;
DECLARE @i int;
SELECT @max = max (session_id)
FROM sys.dm_exec_sessions
SET @i = 51
  WHILE @i <= @max BEGIN
         IF EXISTS (SELECT session_id FROM sys.dm_exec_sessions
                    WHERE session_id=@i)
         DBCC INPUTBUFFER (@i)
         SET @i=@i+1
         END;

Méthode 2 : Informations au niveau des requêtes

Le simple examen du tampon d'entrée ou de l'événement du Générateur de profils SQL Server SQL:BatchCompleted n'indique pas toujours quelle requête utilise le plus d'espace disque dans tempdb. Les méthodes ci-dessous peuvent être utilisées pour répondre à cette question, mais ces méthodes nécessitent la collecte de données supplémentaires aux procédures définies pour la méthode 1.

Pour procéder avec cette méthode, définissez un travail de l'Agent SQL Server qui interroge la vue de gestion dynamique sys.dm_db_task_space_usage. La fréquence d'interrogation doit être courte, une fois par minute, par rapport à la méthode 1. Ce court intervalle est dû au fait que sys.dm_db_task_space_usage ne retourne pas de données si la requête (tâche) n'est pas en cours d'exécution.

Dans la requête d'interrogation, la vue définie sur la vue de gestion dynamique sys.dm_db_task_space_usage est jointe à sys.dm_exec_requests pour retourner les colonnes sql_handle, statement_start_offset, statement_end_offset et plan_handle.

CREATE VIEW all_request_usage
AS 
  SELECT session_id, request_id, 
      SUM(internal_objects_alloc_page_count) AS request_internal_objects_alloc_page_count,
      SUM(internal_objects_dealloc_page_count)AS request_internal_objects_dealloc_page_count 
  FROM sys.dm_db_task_space_usage 
  GROUP BY session_id, request_id;
GO
CREATE VIEW all_query_usage
AS
  SELECT R1.session_id, R1.request_id, 
      R1.request_internal_objects_alloc_page_count, R1.request_internal_objects_dealloc_page_count,
      R2.sql_handle, R2.statement_start_offset, R2.statement_end_offset, R2.plan_handle
  FROM all_request_usage R1
  INNER JOIN sys.dm_exec_requests R2 ON R1.session_id = R2.session_id and R1.request_id = R2.request_id;
GO

Si le plan de requête est en cache, vous pouvez récupérer le texte Transact-SQL de la requête et le plan d'exécution de requête au format Showplan XML à tout moment. Pour obtenir le texte Transact-SQL de la requête qui est exécutée, utilisez la valeur sql_handle et la fonction de gestion dynamique sys.dm_exec_sql_text. Pour obtenir le plan d'exécution de requête, utilisez la valeur plan_handle et la fonction de gestion dynamique sys.dm_exec_query_plan.

SELECT * FROM sys.dm_exec_sql_text(@sql_handle);
SELECT * FROM sys.dm_exec_query_plan(@plan_handle);

Si le plan de requête n'est pas en cache, vous pouvez utiliser une des méthodes ci-dessous pour obtenir le texte Transact-SQL de la requête et le plan d'exécution de requête.

A. Utilisation de la méthode d'interrogation

Interrogez la vue all_query_usage et exécutez la requête ci-dessous pour obtenir le texte de requête :

SELECT R1.sql_handle, R2.text 
FROM all_query_usage AS R1
OUTER APPLY sys.dm_exec_sql_text(R1.sql_handle) AS R2;

Comme sql_handle doit être unique pour chaque lot individuel, vous n'êtes pas tenu d'enregistrer les doublons des entrées sql_handle.

Pour enregistrer le descripteur de plan et le plan XML, exécutez la requête ci-dessous.

SELECT R1.plan_handle, R2.query_plan 
FROM all_query_usage AS R1
OUTER APPLY sys.dm_exec_query_plan(R1.plan_handle) AS R2;

B. Utilisation des événements du Générateur de profils SQL Server

Au lieu d'interroger les fonctions sys.dm_exec_sql_text et sys.dm_exec_query_plan, vous pouvez utiliser les événements du Générateur de profils SQL Server. Certains événements du Générateur de profils peuvent être utilisés pour capturer le plan de requête et le texte de requête qui est généré. Par exemple, l'événement 165 retourne des statistiques de performances liées aux statistiques de suivi, de texte SQL, des plans de requêtes et des requêtes.

Analyse de l'espace utilisé par les tables temporaires et les variables de table

Vous pouvez utiliser une approche similaire pour les requêtes d'interrogation pour analyser l'espace utilisé par les tables et les variables temporaires. Les applications qui acquièrent une grande quantité de données utilisateur dans des tables ou des variables temporaires peuvent générer des problèmes d'utilisation d'espace dans tempdb. Ces tables ou variables appartiennent aux objets utilisateur. Vous pouvez utiliser les colonnes user_objects_alloc_page_count et user_objects_dealloc_page_count de la vue de gestion dynamique sys.dm_db_session_space_usage et suivre les méthodes décrites précédemment.

Analyse de l'allocation et de la désallocation de pages par session

Le tableau ci-dessous affiche les résultats retournés par les vues de gestion dynamique sys.dm_db_file_space_usage, sys.dm_db_session_space_usage et sys.dm_db_task_space_usage pour une session spécifiée. Chaque ligne représente une activité d'allocation ou de désallocation dans tempdb pour une session spécifiée. L'activité est répertoriée dans la colonne Événement. Les colonnes restantes affichent les valeurs qui seraient retournées dans les colonnes des vues de gestion dynamique.

Pour ce scénario, supposez que la base de données tempdb démarre avec 872 pages dans des extensions non allouées et 100 pages dans des extensions réservées aux objets utilisateur. La session alloue 10 pages pour une table utilisateur, puis les désalloue toutes. Les 8 premières pages sont dans une extension mixte. Les 2 pages restantes sont dans une extension uniforme.

Voir aussi

Concepts

Optimisation des performances de la base de données tempdb
Base de données tempdb
Planification des capacités de tempdb

Autres ressources

Dépannage du moteur de base de données

Aide et Informations

Assistance sur SQL Server 2005