-- 1. Aktuelle teure Abfragen (nach CPU) 
SELECT TOP 10 
t.text AS Query, 
qs.total_worker_time / qs.execution_count AS AvgCPU_ms, 
qs.total_logical_reads / qs.execution_count AS AvgReads, 
qs.execution_count 
FROM sys.dm_exec_query_stats qs 
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) t 
ORDER BY AvgCPU_ms DESC; 
 
-- 2. I/O-Waits (PAGEIOLATCH = I/O Engpass) 
SELECT wait_type, waiting_tasks_count, wait_time_ms 
FROM sys.dm_os_wait_stats 
WHERE wait_type LIKE 'PAGEIOLATCH%' OR wait_type LIKE 'WRITELOG%' 
ORDER BY wait_time_ms DESC; 
 
-- 3. Unbenutzte Indizes (0 Reads, viele Updates) 
SELECT OBJECT_NAME(i.object_id) AS TableName, i.name AS IndexName, 
s.user_seeks, s.user_scans, s.user_lookups, s.user_updates 
FROM sys.indexes i 
JOIN sys.dm_db_index_usage_stats s ON i.object_id = s.object_id AND i.index_id = s.index_id 
WHERE s.user_seeks = 0 AND s.user_scans = 0 AND s.user_lookups = 0 
AND s.user_updates > 0 
ORDER BY s.user_updates DESC; 
 
-- 4. Fehlende Indizes (Missing index requests) 
SELECT 
mid.statement AS TableName, 
migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) AS Impact, 
'CREATE INDEX idx_missing ON ' + mid.statement + ' (' + ISNULL(mid.equality_columns,'') + 
ISNULL(mid.inequality_columns,'') + ') INCLUDE (' + ISNULL(mid.included_columns,'') + ')' 
FROM sys.dm_db_missing_index_groups mig 
JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle 
JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle 
WHERE mid.database_id = DB_ID() 
ORDER BY Impact DESC;

-- Extended Events Session für Deadlock-Aufzeichnung 
CREATE EVENT SESSION [TrackDeadlocks] ON SERVER  
ADD EVENT sqlserver.xml_deadlock_report 
ADD TARGET package0.event_file(SET filename = N'C:\XEvents\Deadlocks.xel') 
WITH (MAX_MEMORY = 4096 KB, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS, 
MAX_DISPATCH_LATENCY = 30 SECONDS, STARTUP_STATE = ON); 
GO 
ALTER EVENT SESSION [TrackDeadlocks] ON SERVER STATE = START;

Der DTA analysiert eine Workload (z. B. eine Profiler-Trace-Datei, eine T-SQL-Skriptdatei oder den Query Store) und gibt Empfehlungen für Indizes, Indexed Views und Partitionierungen. Besonders wertvoll für:

• Identifikation fehlender Indizes
• Erkennung überflüssiger Indizes
• Optimierung der Index-Partitionierung

-- Workload aus Query Store für DTA exportieren 
SELECT q.query_id, qt.query_sql_text 
FROM sys.query_store_query q 
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id 
WHERE q.last_execution_time > DATEADD(day, -7, GETUTCDATE());

-- Aktuelle Blockings-Situation analysieren 
SELECT  
blocked.session_id AS Blocked_Session, 
blocked.blocking_session_id AS Blocker_Session, 
blocked.wait_type, blocked.wait_time, 
t.text AS Blocked_Query 
FROM sys.dm_exec_requests blocked 
CROSS APPLY sys.dm_exec_sql_text(blocked.sql_handle) t 
WHERE blocked.blocking_session_id > 0;

-- Kandidaten für ungenutzte Indizes (nach 14+ Tagen Uptime) 
SELECT OBJECT_NAME(i.object_id) AS TableName, i.name AS IndexName, 
s.user_seeks, s.user_scans, s.user_lookups, s.user_updates, 
'DROP INDEX ' + QUOTENAME(i.name) + ' ON ' + QUOTENAME(OBJECT_SCHEMA_NAME(i.object_id)) + '.' + QUOTENAME(OBJECT_NAME(i.object_id)) AS DropCommand 
FROM sys.indexes i 
LEFT JOIN sys.dm_db_index_usage_stats s ON i.object_id = s.object_id AND i.index_id = s.index_id AND s.database_id = DB_ID() 
WHERE i.type_desc IN ('NONCLUSTERED', 'CLUSTERED') 
AND (s.user_seeks + s.user_scans + s.user_lookups = 0 OR s.user_seeks IS NULL) 
AND s.user_updates > 0 
ORDER BY s.user_updates DESC;

I/O-Probleme sind für bis zu 50% aller Performance-Probleme verantwortlich. Erkennungskriterien:

• Wait-Statistiken: PAGEIOLATCH_* über 200 ms durchschnittliche Wartezeit
• PerfMon-Latenzen: Avg. Disk sec/Read > 10-15 ms konsistent[reference:1]
• Page Life Expectancy (PLE) unter 300 Sekunden (RAM-Engpass führt zu vermehrten I/Os)

-- I/O-Wait-Typen analysieren 
SELECT wait_type, waiting_tasks_count, wait_time_ms, max_wait_time_ms 
FROM sys.dm_os_wait_stats 
WHERE wait_type IN ('PAGEIOLATCH_SH', 'PAGEIOLATCH_EX', 'WRITELOG', 'IO_COMPLETION') 
ORDER BY wait_time_ms DESC;

Das PowerShell-Modul msSQLTools bündelt viele der oben beschriebenen Techniken in einer einfach verwendbaren Befehlszeile. Es analysiert DMVs, erstellt Health-Reports und kann in CI/CD-Pipelines integriert werden.

# msSQLTools für Performance-Tuning (Download über dtc-sql.de) 
 
# Blockings-Analyse mit Ausgabe der führenden Blockierer 
Get-mssBlockingTree -SqlInstance "PRODSQL01" 
 
# Kompletter Health-Check in HTML (inkl. Waits, Index-Statistiken, Blockings) 
Invoke-mssHealthCheck -SqlInstance "PRODSQL01" -OutputHtml "C:\Reports\Health.html" 
 
# I/O-Latenz über mehrere Instanzen hinweg auswerten 
Get-mssPerformanceWaiter -SqlInstance "PRODSQL01", "PRODSQL02" -WaitType "PAGEIOLATCH*" -WaitMinutes 60 
 
# Query Store automatisch aktivieren und Baseline erfassen 
Enable-mssQueryStore -SqlInstance "PRODSQL01" -Database "AdventureWorks" -MaxSizeMB 2048

• Query Store auf allen produktiven Datenbanken aktivieren (insb. OLTP) – erfasst historische Leistungsdaten erkennt Regressionen automatisch.
• Wöchentliche DMV-Checks für fehlende Indizes, ungenutzte Indizes und teure Abfragen durchführen.
• Extended Events statt Profiler – vermeidet unnötigen Overhead auf Produktionssystemen.
• PerfMon-Datensätze für kritische Counter (CPU, Memory, Disk, Locks) kontinuierlich sammeln – idealerweise 24x7.
• Indexpflege regelmäßig durchführen (Fragmentation minimal halten).
• Statistiken täglich aktualisieren (besonders bei größeren Datenänderungen).
• TempDB optimieren: Mehrere Dateien gleicher Größe (Anzahl ≈ logische CPU-Kerne/2).
• MsSQLTools nutzen – automatisierte Health-Reports und Waiter-Analyse sparen Zeit.