Datafly.pro

La base tempdb est un élément critique de SQL Server : c'est une ressource globale et unique pour toute l'instance, toutes ses bases de données et tous ses utilisateurs connectés.

L'espace de tempdb peut être sollicité pour de nombreux usages :

- stocker des objets temporaires d'utilisateurs

• CREATE TABLE #MaTable
• CREATE TABLE ##MaTable
• DECLARE @MaTable

• tris lorsqu'il n'y pas d'index,
• clauses GROUP BY et UNION,
• tables de travail,
• réindexations,
• versions de lignes pour les niveaux d'isolation SNAPSHOT et READ COMMITTED SNAPSHOT
• tables internes des triggers (inserted, deleted)
• curseurs
• fonctions de type table
• etc...

Même si on l'a bien optimisée et dimensionnée conformément aux bonnes pratiques (créer plusieurs fichiers, prédimensionner), il sera malheureusement fréquent de voir cette base augmenter brutalement de taille, souvent le signe de code SQL mal écrit ou d'indexation insuffisante. Voici quelques requêtes utiles pour le diagnostic.

Voir si tempdb a grossi par rapport à sa taille configurée

Transact-SQL obligatoire : Management Studio ne nous donne nulle part cette information !...

SELECT
    alt.filename
    ,alt.name
    ,alt.size * 8.0 / 1024.0 AS originalsize_MB
    ,files.size * 8.0 / 1024.0 AS currentsize_MB
FROM
    master.dbo.sysaltfiles alt INNER JOIN tempdb.dbo.sysfiles files ON
        alt.fileid = files.fileid
WHERE
    dbid = db_id('tempdb')

Vérifier l'espace alloué

Pour obtenir les informations générales sur l'espace alloué et libre, lancer la requête suivante :

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

L'information détaillée sur l'espace alloué dans tempdb est visible dans 2 DMV :

• sys.dm_db_task_space_usage pour les tâches en cours d'exécution
• sys.dm_db_session_space_usage, une fois l'exécution terminée

Il y a 4 colonnes à surveiller :

• user_objects_alloc_page_count et user_objects_dealloc_page_count : pour les pages allouées et désallouées pour des objets utilisateur (#Tables, etc...)
• internal_objects_alloc_page_count et internal_objects_dealloc_page_count : pour les pages allouées et désallouées pour des objets internes (que SQL Server a créé pour l'exécution des requêtes)

La requête suivante nous permet d'identifier qui est le coupable :

/*-------------------------------------------------------------------
[SCRIPT] Tempdb consommateurs  
[DATABASE] 
[DESCRIPTION] Les plus gros consommateurs de tempdb
[MAJ PAR] DATAFLY - Arian Papillon
[DATEMAJ] 24/03/2013 
-------------------------------------------------------------------*/
SELECT   ta.session_id
        ,status
        ,login_name
        ,host_name
        ,program_name
        ,db_name
        ,login_time
        ,allocated_pages
        ,total_alloc_page_count
        ,total_dealloc_page_count
        ,user_objects_alloc_page_count
        ,user_objects_dealloc_page_count
        ,internal_objects_alloc_page_count
        ,internal_objects_dealloc_page_count
        ,rq.text
FROM     ( SELECT ssp.session_id
                 ,se.status
                 ,se.login_name
                 ,se.host_name
                 ,se.program_name
                 ,DB_NAME(se.database_id) AS db_name
                 ,se.login_time
                 ,( SUM(ssp.user_objects_alloc_page_count + tsp.user_objects_alloc_page_count)
                    + SUM(ssp.internal_objects_alloc_page_count + tsp.internal_objects_alloc_page_count) )
                  - ( SUM(ssp.user_objects_dealloc_page_count + tsp.user_objects_dealloc_page_count)
                      + SUM(ssp.internal_objects_dealloc_page_count + tsp.internal_objects_dealloc_page_count) ) AS allocated_pages
                 ,SUM(ssp.user_objects_alloc_page_count + tsp.user_objects_alloc_page_count)
                  + SUM(ssp.internal_objects_alloc_page_count + tsp.internal_objects_alloc_page_count) AS total_alloc_page_count
                 ,SUM(ssp.user_objects_dealloc_page_count + tsp.user_objects_dealloc_page_count)
                  + SUM(ssp.internal_objects_dealloc_page_count + tsp.internal_objects_dealloc_page_count) AS total_dealloc_page_count
                 ,SUM(ssp.user_objects_alloc_page_count + tsp.user_objects_alloc_page_count) AS user_objects_alloc_page_count
                 ,SUM(ssp.user_objects_dealloc_page_count + tsp.user_objects_dealloc_page_count) AS user_objects_dealloc_page_count
                 ,SUM(ssp.internal_objects_alloc_page_count + tsp.internal_objects_alloc_page_count) AS internal_objects_alloc_page_count
                 ,SUM(ssp.internal_objects_dealloc_page_count + tsp.internal_objects_dealloc_page_count) AS internal_objects_dealloc_page_count
           FROM   sys.dm_db_session_space_usage ssp ( READUNCOMMITTED )
                  JOIN sys.dm_db_task_space_usage tsp ( READUNCOMMITTED ) ON ssp.session_id = tsp.session_id
                  JOIN sys.dm_exec_sessions se ( READUNCOMMITTED ) ON ssp.session_id = se.session_id
GROUP BY          ssp.session_id
                 ,se.status
                 ,se.login_name
                 ,se.host_name
                 ,se.program_name
                 ,DB_NAME(se.database_id)
                 ,se.login_time
         ) AS ta
         LEFT JOIN sys.dm_exec_connections c ( READUNCOMMITTED ) ON ta.session_id = c.session_id
         OUTER APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) AS rq
ORDER BY allocated_pages DESC
        ,total_alloc_page_count DESC

Réduire tempdb

Si l'accroissement de tempdb est purement accidentel et occasionnel, il sera peut être souhaité de réduire la taille de ses fichiers avec la commande DBCC SHRINKFILE (file_name,target_size).

Bien souvent, cela ne fonctionne pas ou mal : de l'espace reste alloué, empêchant la réduction des fichiers. La solution radicale serait de profiter d'un redémarrage de l'instance. Si ce n'est pas possible, et en cas d'urgence, on peut tenter les opérations suivantes :

• Déconnecter (KILL) les sessions utilisateur fortement consommatrices
• Lancer la commande DBCC FREESYSTEMCACHE ('ALL'). Attention à l'impact de cette commande sur les performances, elle vide tous les caches mais peut aussi désallouer des objets temporaires dans tempdb.
• Re-tenter le redimensionnement.