SQL Server Performance Tuning

-- 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.