TempDB verstehen, Probleme erkennen und gezielt entlasten

/* PSG Stack · SQL Server TempDB · Abfrage 1
   Lagebild TempDB: Wer belegt gerade Platz, und was hält ihn fest?

   - Nur lesend. Ändert nichts, beendet keine Sitzung.
   - Jede Teilabfrage läuft mit MAXDOP 1.
   - Liefert vier Ergebnisse:
       1 Belegung der TempDB-Datendateien nach Art
       2 Sitzungen mit der größten TempDB-Belegung
       3 Version Store: Größe je Datenbank und älteste Snapshot-Transaktionen
       4 TempDB-Protokoll: Füllstand, Wartegrund und offene Transaktionen
   - Rechte: VIEW SERVER STATE
     (ab SQL Server 2022 genügt VIEW SERVER PERFORMANCE STATE).
     Ohne dieses Recht scheitert jeder Teil mit Fehler 300 (VIEW DATABASE STATE allein genügt nicht).
   - Ab SQL Server 2017. Geprüft im PSG-Lab auf 2017, 2019, 2022 und 2025.
   - Läuft in jeder Datenbank: TempDB-Werte kommen immer aus TempDB.
   - Momentaufnahme: Werte ändern sich laufend. Bei Bedarf mehrmals ausführen.
*/

/* 1 · Belegung der TempDB-Datendateien nach Art (MB) */
SELECT
    SUM(fsu.user_object_reserved_page_count)     * 8 / 1024 AS benutzerobjekte_mb,
    SUM(fsu.internal_object_reserved_page_count) * 8 / 1024 AS interne_objekte_mb,
    SUM(fsu.version_store_reserved_page_count)   * 8 / 1024 AS version_store_mb,
    SUM(fsu.mixed_extent_page_count)             * 8 / 1024 AS gemischte_extents_mb,
    SUM(fsu.unallocated_extent_page_count)       * 8 / 1024 AS frei_mb,
    COUNT(*)                                                AS datendateien
FROM tempdb.sys.dm_db_file_space_usage AS fsu
OPTION (MAXDOP 1);

/* 2 · Sitzungen mit der größten TempDB-Belegung
   Abgeschlossene Tasks (session_space_usage) plus laufende Tasks (task_space_usage).
   Benutzerobjekte: #temp-Tabellen, Tabellenvariablen, Indexaufbau mit SORT_IN_TEMPDB.
   Interne Objekte: Sort, Hash, Spools, Cursor, LOB-Zwischenergebnisse.
   Die Zähler gelten je Sitzung: Löscht eine andere Sitzung ein Objekt (z. B. eine globale ##-Tabelle),
   bleibt der Platz der erstellenden Sitzung zugerechnet, bis diese endet. Die Summe kann daher
   über Ergebnis 1 liegen. */
WITH belegung AS (
    SELECT ssu.session_id,
           ssu.user_objects_alloc_page_count     - ssu.user_objects_dealloc_page_count     AS benutzer_seiten,
           ssu.internal_objects_alloc_page_count - ssu.internal_objects_dealloc_page_count AS interne_seiten
    FROM sys.dm_db_session_space_usage AS ssu
    WHERE ssu.database_id = 2
    UNION ALL
    SELECT tsu.session_id,
           tsu.user_objects_alloc_page_count     - tsu.user_objects_dealloc_page_count,
           tsu.internal_objects_alloc_page_count - tsu.internal_objects_dealloc_page_count
    FROM sys.dm_db_task_space_usage AS tsu
    WHERE tsu.database_id = 2
), je_sitzung AS (
    SELECT session_id,
           SUM(benutzer_seiten) AS benutzer_seiten,
           SUM(interne_seiten)  AS interne_seiten
    FROM belegung
    GROUP BY session_id
)
SELECT TOP (20)
    js.session_id,
    es.login_name,
    es.host_name,
    es.program_name,
    es.status,
    CAST(CASE WHEN js.benutzer_seiten > 0 THEN js.benutzer_seiten ELSE 0 END * 8 / 1024.0 AS decimal(12,1)) AS benutzerobjekte_mb,
    CAST(CASE WHEN js.interne_seiten  > 0 THEN js.interne_seiten  ELSE 0 END * 8 / 1024.0 AS decimal(12,1)) AS interne_objekte_mb,
    r.command,
    r.wait_type,
    r.granted_query_memory * 8 / 1024                       AS speicherzusage_mb,
    DB_NAME(r.database_id)                                  AS datenbank,
    LEFT(st.text, 4000)                                     AS sql_text
FROM je_sitzung AS js
JOIN sys.dm_exec_sessions AS es ON es.session_id = js.session_id
LEFT JOIN sys.dm_exec_requests AS r ON r.session_id = js.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
WHERE es.is_user_process = 1
  AND (js.benutzer_seiten > 0 OR js.interne_seiten > 0)
ORDER BY (CASE WHEN js.benutzer_seiten > 0 THEN js.benutzer_seiten ELSE 0 END
        + CASE WHEN js.interne_seiten  > 0 THEN js.interne_seiten  ELSE 0 END) DESC
OPTION (MAXDOP 1);

/* 3a · Version Store in TempDB je Datenbank (MB) */
SELECT
    DB_NAME(vs.database_id)                       AS datenbank,
    vs.reserved_page_count * 8 / 1024             AS version_store_mb
FROM sys.dm_tran_version_store_space_usage AS vs
WHERE vs.reserved_page_count > 0
ORDER BY vs.reserved_page_count DESC
OPTION (MAXDOP 1);

/* 3b · Älteste Snapshot-Transaktionen: Sie halten den Version Store fest
   snapshot_isolation = 0: Transaktion ohne SNAPSHOT-Isolation, die Zeilenversionen erzeugt
   (z. B. Schreiber unter READ_COMMITTED_SNAPSHOT). */
SELECT TOP (10)
    ast.session_id,
    ast.elapsed_time_seconds                      AS sekunden_offen,
    ast.is_snapshot                               AS snapshot_isolation,
    es.login_name,
    es.host_name,
    es.program_name,
    es.status,
    es.last_request_end_time                      AS letzte_anfrage_beendet
FROM sys.dm_tran_active_snapshot_database_transactions AS ast
LEFT JOIN sys.dm_exec_sessions AS es ON es.session_id = ast.session_id
ORDER BY ast.elapsed_time_seconds DESC
OPTION (MAXDOP 1);

/* 4a · TempDB-Protokoll: Größe, Füllstand und Wartegrund */
SELECT
    CAST(ls.total_log_size_in_bytes / 1048576.0 AS decimal(12,1)) AS protokoll_mb,
    CAST(ls.used_log_space_in_percent AS decimal(5,1))             AS belegt_prozent,
    d.log_reuse_wait_desc                                          AS wartegrund
FROM tempdb.sys.dm_db_log_space_usage AS ls
CROSS JOIN sys.databases AS d
WHERE d.database_id = 2
OPTION (MAXDOP 1);

/* 4b · Offene Transaktionen mit TempDB-Protokoll, größte zuerst */
SELECT TOP (10)
    st.session_id,
    dt.database_transaction_begin_time                                    AS begonnen,
    DATEDIFF(SECOND, dt.database_transaction_begin_time, SYSDATETIME())   AS sekunden_offen,
    CAST(dt.database_transaction_log_bytes_used     / 1048576.0 AS decimal(12,1)) AS protokoll_genutzt_mb,
    CAST(dt.database_transaction_log_bytes_reserved / 1048576.0 AS decimal(12,1)) AS protokoll_reserviert_mb,
    es.login_name,
    es.program_name,
    es.status
FROM sys.dm_tran_database_transactions AS dt
JOIN sys.dm_tran_session_transactions AS st ON st.transaction_id = dt.transaction_id
LEFT JOIN sys.dm_exec_sessions AS es ON es.session_id = st.session_id
WHERE dt.database_id = 2
ORDER BY dt.database_transaction_log_bytes_used DESC
OPTION (MAXDOP 1);

1 · Belegung nach Art (MB)
benutzerobjekte_mb  interne_objekte_mb  version_store_mb  frei_mb
40                  48                  53                127

2 · Sitzungen mit der größten Belegung
session_id  status    benutzerobjekte_mb  interne_objekte_mb
54          sleeping  0.0                 42.4
56          sleeping  39.2                0.0
60          running   0.0                 6.0

3b · Älteste Snapshot-Transaktionen
session_id  sekunden_offen  snapshot_isolation  status
53          25              1                   sleeping

4a · TempDB-Protokoll
protokoll_mb  belegt_prozent  wartegrund
136.0         99.5            ACTIVE_TRANSACTION

4b · Offene Transaktionen mit TempDB-Protokoll
session_id  sekunden_offen  protokoll_genutzt_mb  status
56          20              78.0                  sleeping