DMV Spickzettel:

Die 20 wichtigsten DMVs mit Beispielabfragen

Dynamic Management Views (DMVs) sind das Diagnose-Rückgrat von SQL Server. Sie liefern Echtzeit-Einblick in das, was der Server gerade tut — welche Abfragen laufen, worauf sie warten, welche Indizes benutzt werden, wieviel Speicher welche Komponente verbraucht. Dieser Anhang ist der Spickzettel: Eine DMV pro Abschnitt, Zweck in einem Satz, und eine direkt ausführbare Abfrage mit deutschen Kommentaren.

Die Abfragen sind bewusst kurz gehalten — maximal 8 Zeilen. Sie zeigen das Wichtigste, nicht alles. Für tiefere Analysen werden die jeweiligen Kapitel verwiesen.

Wait Statistics und laufende Verbindungen

1. sys.dm_os_wait_stats — Wait Statistics Baseline

Zeigt kumulierte Wartezeiten seit dem letzten SQL Server-Start. Der Einstiegspunkt jeder Performance-Diagnose. Ausführlich in Kapitel 9.

-- Top-10 Wait Types, bereinigt um System-Idle-Waits

SELECT TOP 10 wait_type,

    waiting_tasks_count,

    wait_time_ms / 1000.0 AS warte_sek,

    signal_wait_time_ms * 100.0 / NULLIF(wait_time_ms,0) AS signal_pct

FROM sys.dm_os_wait_stats

WHERE wait_type NOT LIKE 'SLEEP%'

  AND wait_type NOT LIKE 'BROKER%'

  AND wait_type NOT IN ('WAITFOR','DISPATCHER_QUEUE_SEMAPHORE')

ORDER BY wait_time_ms DESC;

2. sys.dm_os_waiting_tasks — aktuelle Waits live

Zeigt im Gegensatz zu dm_os_wait_stats nicht die historische Summe, sondern was im aktuellen Moment wartet. Nützlich bei akutem Problem, um sofort zu sehen wer auf wen wartet.

-- Alle Tasks die gerade warten, mit Blocking-Info

SELECT session_id, wait_type, wait_duration_ms,

       blocking_session_id, resource_description

FROM sys.dm_os_waiting_tasks

WHERE session_id > 50  -- Systemsessions ausblenden

ORDER BY wait_duration_ms DESC;

3. sys.dm_exec_requests — laufende Queries und Blocking-Chain

Die wichtigste DMV für "was passiert gerade". Zeigt alle aktiv ausgeführten Anfragen inklusive Blocking-Information. Kombiniert mit sys.dm_exec_sql_text ergibt sich die vollständige Blocking-Chain.

-- Alle aktiven Requests mit SQL-Text und Blocking-Info

SELECT r.session_id, r.status, r.blocking_session_id,

       r.wait_type, r.wait_time, r.cpu_time,

       t.text AS sql_text

FROM sys.dm_exec_requests r

CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t

WHERE r.session_id > 50

ORDER BY r.cpu_time DESC;

4. sys.dm_exec_sessions — alle Verbindungen

Gibt einen Überblick über alle aktiven Sessions — inklusive Login-Name, Host, Datenbank und kumulierten Ressourcenverbrauch. Nützlich zum Identifizieren von "wer ist das überhaupt".

-- Alle Sessions mit Ressourcenverbrauch

SELECT session_id, login_name, host_name, db_name(database_id) AS datenbank,

       cpu_time, reads, writes, logical_reads, status

FROM sys.dm_exec_sessions

WHERE is_user_process = 1  -- nur Benutzersessions

ORDER BY cpu_time DESC;

Abfrage-Performance und Plan Cache

5. sys.dm_exec_query_stats — Top-CPU/IO-Queries aus dem Plan Cache

Aggregierte Ausführungsstatistiken pro Plan. Der Standard-Einstieg, um die teuersten Abfragen zu finden — nach CPU, nach IO, nach Ausführungsanzahl. Kapitel 15 und 18.

-- Top-10 Abfragen nach CPU-Verbrauch gesamt

SELECT TOP 10

    qs.total_worker_time / qs.execution_count AS avg_cpu_us,

    qs.total_logical_reads / qs.execution_count AS avg_reads,

    qs.execution_count,

    t.text AS sql_text

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) t

ORDER BY qs.total_worker_time DESC;

6. sys.dm_exec_sql_text — Query-Text zu einem Handle

Wird fast immer via CROSS APPLY zusammen mit anderen DMVs verwendet, um aus einem sql_handle oder statement_start_offset den lesbaren SQL-Text zu holen.

-- Query-Text zu einem bekannten Handle holen

-- (Normalerweise via CROSS APPLY, nicht direkt)

SELECT text, dbid, objectid

FROM sys.dm_exec_sql_text(0x0200000...);  -- Handle aus anderen DMVs

7. sys.dm_exec_query_plan — Ausführungsplan zu einem Handle

Liefert den XML-Ausführungsplan zu einem plan_handle. Zusammen mit dm_exec_query_stats der Weg zu "ich will den Plan für die teuerste Abfrage sehen".

-- Plan zur teuersten CPU-Abfrage holen

SELECT TOP 1

    qp.query_plan,

    qs.total_worker_time,

    t.text

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) t

ORDER BY qs.total_worker_time DESC;

IO und Storage

8. sys.dm_io_virtual_file_stats — IO-Latenzen pro Datenbankdatei

Die wichtigste DMV zur IO-Diagnose. Zeigt Lese- und Schreib-Latenzen pro Datenbankdatei. Unter 5 ms ist gut, über 20 ms ist ein Problem. Kapitel 10.

-- IO-Latenzen für alle Datenbankdateien

SELECT DB_NAME(vfs.database_id) AS datenbank,

    mf.physical_name, vfs.io_stall_read_ms / NULLIF(vfs.num_of_reads,0) AS read_ms,

    vfs.io_stall_write_ms / NULLIF(vfs.num_of_writes,0) AS write_ms

FROM sys.dm_io_virtual_file_stats(NULL, NULL) vfs

JOIN sys.master_files mf ON vfs.database_id=mf.database_id AND vfs.file_id=mf.file_id

ORDER BY read_ms DESC;

Memory Management

9. sys.dm_os_performance_counters — PLE, Compilations, Batch Requests

Enthält hunderte von Performance-Countern direkt aus SQL Server. PLE (Page Life Expectancy) und Compilations/sec sind die wichtigsten für die tägliche Überwachung. Kapitel 11.

-- PLE und Compilation-Rate auf einen Blick

SELECT object_name, counter_name, cntr_value

FROM sys.dm_os_performance_counters

WHERE counter_name IN (

    'Page life expectancy',

    'SQL Compilations/sec',

    'SQL Re-Compilations/sec',

    'Batch Requests/sec');

10. sys.dm_os_sys_memory — verfügbarer System-Speicher

Zeigt den aktuellen Speicherstatus des Betriebssystems — wie viel ist noch frei, wie hoch ist der Memory-Druck. Wichtig, wenn SQL Server und andere Prozesse um Speicher konkurrieren.

-- Betriebssystem-Speicherstatus

SELECT total_physical_memory_kb / 1024 AS total_mb,

    available_physical_memory_kb / 1024 AS verfuegbar_mb,

    system_memory_state_desc AS speicher_zustand

FROM sys.dm_os_sys_memory;

11. sys.dm_os_buffer_descriptors — Buffer Pool Inhalt pro Datenbank

Zeigt welche Datenbank wie viele Pages im Buffer Pool hält. Wenn eine Datenbank den Buffer Pool dominiert und PLE niedrig ist, ist das der Ausgangspunkt. Kapitel 11.

-- Buffer Pool-Nutzung nach Datenbank

SELECT DB_NAME(database_id) AS datenbank,

    COUNT(*) * 8 / 1024 AS buffer_mb

FROM sys.dm_os_buffer_descriptors

GROUP BY database_id

ORDER BY buffer_mb DESC;

12. sys.dm_os_memory_clerks — Memory-Verbrauch nach Komponente

Zeigt wer (welche SQL Server-Komponente) wie viel Arbeitsspeicher verbraucht. Nützlich, wenn der Gesamt-Memory-Verbrauch hoch ist und man wissen will ob Buffer Pool, Plan Cache oder andere Clerks schuld sind.

-- Top-10 Memory Clerks nach Verbrauch

SELECT TOP 10 type AS clerk_typ,

    SUM(pages_kb) / 1024 AS verbrauch_mb

FROM sys.dm_os_memory_clerks

GROUP BY type

ORDER BY verbrauch_mb DESC;

Indizes

13. sys.dm_db_index_physical_stats — Fragmentierung

Zeigt Fragmentierung pro Index. Ab 30% Fragmentierung ist ein Rebuild sinnvoll, zwischen 10–30% ein Reorganize. Kapitel 17.

-- Fragmentierung aller Indizes in der aktuellen Datenbank

SELECT OBJECT_NAME(object_id) AS tabelle, index_id,

    avg_fragmentation_in_percent AS frag_pct, page_count

FROM sys.dm_db_index_physical_stats(

    DB_ID(), NULL, NULL, NULL, 'LIMITED')

WHERE avg_fragmentation_in_percent > 10

ORDER BY frag_pct DESC;

14. sys.dm_db_index_usage_stats — Index-Nutzungsstatistiken

Zeigt wie oft ein Index für Seeks, Scans und Lookups benutzt wurde — und wie oft er durch DML aktualisiert werden musste. Indizes mit 0 Seeks und vielen Updates sind Kandidaten für die Löschliste. Kapitel 17.

-- Indizes mit 0 Seeks aber vielen Updates (= unnötiger Overhead)

SELECT OBJECT_NAME(object_id) AS tabelle, index_id,

    user_seeks, user_scans, user_lookups, user_updates

FROM sys.dm_db_index_usage_stats

WHERE database_id = DB_ID()

  AND user_seeks = 0 AND user_updates > 1000

ORDER BY user_updates DESC;

15. sys.dm_db_missing_index_details — fehlende Indizes

SQL Server protokolliert intern Abfragen, die von einem fehlenden Index profitiert hätten. Diese DMV zeigt die Kandidaten. Achtung: Die Empfehlungen sind nicht immer optimal — immer prüfen, bevor man sie einfach erstellt. Kapitel 17.

-- Fehlende Indizes nach geschätztem Impact sortiert

SELECT mid.statement AS tabelle,

    mid.equality_columns, mid.include_columns,

    migs.avg_user_impact AS geschaetzter_gewinn_pct

FROM sys.dm_db_missing_index_details mid

JOIN sys.dm_db_missing_index_groups mig ON mid.index_handle=mig.index_handle

JOIN sys.dm_db_missing_index_group_stats migs ON mig.index_group_handle=migs.group_handle

ORDER BY migs.avg_user_impact DESC;

Transaktionen, Locks und Isolation

16. sys.dm_tran_active_transactions — aktive Transaktionen

Zeigt alle offenen Transaktionen. Lange offene Transaktionen sind häufig die Wurzel von Blocking-Ketten — hier sieht man sie. Kapitel 14.

-- Alle aktiven Transaktionen mit Alter

SELECT tst.session_id,

    tat.name AS trans_name,

    DATEDIFF(SECOND, tat.transaction_begin_time, GETDATE()) AS alter_sek

FROM sys.dm_tran_active_transactions tat

JOIN sys.dm_tran_session_transactions tst ON tat.transaction_id=tst.transaction_id

ORDER BY alter_sek DESC;

17. sys.dm_tran_locks — gehaltene Locks

Zeigt alle aktuell gehaltenen Locks — Typ, Ressource, Session. Bei einem Blocking-Problem kann man hier sehen, wer welche Sperre hält. Kapitel 14.

-- Alle gehaltenen Locks (nur Benutzersessions)

SELECT request_session_id, resource_type,

    resource_database_id, resource_associated_entity_id,

    request_mode, request_status

FROM sys.dm_tran_locks

WHERE resource_type <> 'DATABASE'

ORDER BY request_session_id;

18. sys.dm_tran_version_store_space_usage — Version Store (RCSI/Snapshot)

Zeigt wie viel TempDB-Speicher der Version Store für Row-Versioning belegt — relevant, wenn RCSI oder Snapshot Isolation aktiv ist und TempDB unter Druck steht. Kapitel 29.

-- Version Store-Speicher pro Datenbank

SELECT DB_NAME(database_id) AS datenbank,

    reserved_space_kb / 1024 AS version_store_mb

FROM sys.dm_tran_version_store_space_usage

ORDER BY reserved_space_kb DESC;

Stored Procedures und Funktionen

19. sys.dm_exec_procedure_stats — Stored Procedure Performance

Wie dm_exec_query_stats, aber aggregiert auf Stored-Procedure-Ebene statt auf Plan-Ebene. Zeigt welche Prozeduren die meiste CPU oder IO verbrauchen. Kapitel 26.

-- Top-10 Stored Procedures nach CPU-Verbrauch

SELECT TOP 10

    OBJECT_NAME(ps.object_id) AS prozedur,

    ps.total_worker_time / ps.execution_count AS avg_cpu_us,

    ps.execution_count

FROM sys.dm_exec_procedure_stats ps

ORDER BY ps.total_worker_time DESC;

20. sys.dm_exec_function_stats — Scalar UDF Performance

Zeigt den Ressourcenverbrauch von Scalar User Defined Functions. Oft der Fund, der erklärt warum eine simple Abfrage Stunden läuft — wenn eine UDF zeilenweise aufgerufen wird. Wie bei Sparfuchs in Kapitel 33: fn_GetSteuersatz, 360.000 Aufrufe pro Query. Kapitel 26.

-- Scalar UDFs mit den meisten Aufrufen

SELECT TOP 10

    OBJECT_NAME(fs.object_id) AS funktion,

    fs.execution_count,

    fs.total_worker_time / fs.execution_count AS avg_cpu_us

FROM sys.dm_exec_function_stats fs

ORDER BY fs.execution_count DESC;

Hinweise zur Verwendung

 

Hinweis: DMV-Werte sind kumulativ oder flüchtig
Die meisten DMVs zeigen kumulative Werte seit dem letzten SQL Server-Start (z.B. dm_os_wait_stats, dm_exec_query_stats). Sie werden bei einem Neustart zurückgesetzt. dm_os_waiting_tasks dagegen zeigt nur den aktuellen Moment und ist nach Sekunden veraltet. Wichtig beim Interpretieren: Hohe absolute Werte sagen wenig ohne Kontext — entscheidend ist der Anteil und die Baseline. Mehr dazu in Kapitel 9.
Berechtigung: Die meisten DMVs benötigen VIEW SERVER STATE auf Serverebene oder VIEW DATABASE STATE auf Datenbankebene. Ohne diese Berechtigung sieht man nur die eigene Session — oder gar nichts.

 

Für die vollständige Interpretation der DMV-Ergebnisse, die Einbettung in eine Diagnose-Methodik und die Kombination mehrerer DMVs in aussagekräftige Analysen: Kapitel 9 (Wait Statistics), Kapitel 31 (Analyse-Methodik) und das Collect-SqlPerf.ps1-Script aus Anhang F, das viele dieser Abfragen automatisch ausführt und dokumentiert.