In einer Notfallsituation braucht man sofortige Antworten: Was tut der SQL Server gerade? Wer blockiert wen? Welche Query verursacht die CPU-Last? Diese DMV-Queries liefern innerhalb von Sekunden ein vollständiges Bild.

Alle aktuell laufenden Queries

SELECT
    r.session_id,
    r.status,
    r.cpu_time,
    r.total_elapsed_time / 1000 AS elapsed_sec,
    r.logical_reads,
    r.writes,
    r.wait_type,
    DB_NAME(r.database_id) AS database_name,
    t.text AS query_text,
    SUBSTRING(t.text, (r.statement_start_offset/2)+1,
        ((CASE r.statement_end_offset
          WHEN -1 THEN DATALENGTH(t.text)
          ELSE r.statement_end_offset END
          - r.statement_start_offset)/2)+1) AS current_statement
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.session_id > 50  -- System-Sessions ausschließen
ORDER BY r.total_elapsed_time DESC;

Blockierungsketten erkennen

-- Blocking-Chains: Wer blockiert wen?
SELECT
    blocked.session_id AS blocked_session,
    blocking.session_id AS blocking_session,
    blocked.wait_type,
    blocked.wait_time / 1000.0 AS wait_sec,
    DB_NAME(blocked.database_id) AS db,
    bt.text AS blocking_query
FROM sys.dm_exec_requests blocked
JOIN sys.dm_exec_sessions blocking ON blocked.blocking_session_id = blocking.session_id
CROSS APPLY sys.dm_exec_sql_text(blocking.sql_handle) bt
WHERE blocked.blocking_session_id > 0;

Top CPU-Verbraucher

-- Top 10 Queries nach CPU (aus dem Plan Cache)
SELECT TOP 10
    total_cpu_time = qs.total_worker_time / 1000,
    avg_cpu_time = qs.total_worker_time / qs.execution_count / 1000,
    execution_count = qs.execution_count,
    query_text = SUBSTRING(qt.text, qs.statement_start_offset/2+1,
        (CASE WHEN qs.statement_end_offset = -1
              THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
              ELSE qs.statement_end_offset END - qs.statement_start_offset)/2+1)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY qs.total_worker_time DESC;

Diese drei Queries decken die häufigsten Notfallsituationen ab: überlastete Instanz, Blockierungen und CPU-Spitzen. Sie sollten Teil jedes DBA-Toolkits und in SSMS als gespeicherte Favoriten hinterlegt sein.