SQL Server Disk I/O Latency: Diagnose Data and Transaction Log Bottlenecks
Measures read/write latency by file using differences between samples and separates query, backup and storage causes.
Operational Safety & Risk Notice
This runbook contains operational intervention steps; several are write-impacting (KILL, terminate, DROP, RESIZE). Verify in a staging environment first, and apply sequentially in production with verification.
1. Symptom & Signals
- M31'de veri dosyası okuma gecikmesi sürekli >20 ms veya log yazma gecikmesi >5 ms (iş yüküne göre ayarlanacak başlangıç eşikleri).
- `PAGEIOLATCH_*` veya `WRITELOG` beklemeleri artar; işlem commit'leri ve sorgular yavaşlar.
2. Business & Technical Impact
Okuma sorguları ve transaction commit'leri uzar; eşzamanlı iş yükü sıraya girer. Depolama doygunluğu yedeklemeyi ve Always On redo işlemlerini de etkileyebilir.
3. Diagnosis & Root-Cause Analysis
SELECT v.database_id, v.file_id, DB_NAME(v.database_id) AS db_name,
mf.type_desc, mf.physical_name,
v.num_of_reads, v.io_stall_read_ms,
v.num_of_writes, v.io_stall_write_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS v
JOIN sys.master_files AS mf
ON mf.database_id = v.database_id AND mf.file_id = v.file_id
ORDER BY v.io_stall_read_ms + v.io_stall_write_ms DESC;1-5 dakika arayla aynı çıktıyı alın. `read_ms = Δio_stall_read_ms / NULLIF(Δnum_of_reads,0)`, `write_ms = Δio_stall_write_ms / NULLIF(Δnum_of_writes,0)`.
4. Remediation & Action Plan
ALTER DATABASE [OrnekDb] MODIFY FILE
(NAME = N'OrnekDb_Data', FILEGROWTH = 512MB);Bu **mevcut disk gecikmesini tek başına gidermez**; büyüme sıçramalarını azaltır. Log dosyasında instant file initialization uygulanmaz.
5. **Yapmayın:** `DBCC SHRINKFILE` ile alanı yeniden dağıtarak sorunu çözmeye çalışmayın; yeniden büyüme ve fragmentasyon yaratır. `NOLOCK` I/O çözümü değildir.
5. Verification & Health Checks
- Aynı dosyalar için yeni iki örnek arasındaki gecikme düştü ve M31 değerlendirme penceresi boyunca normal.
- Disk kuyrukları, `PAGEIOLATCH_*`/`WRITELOG` bekleme hızları ve uygulama gecikmeleri iyileşti.
6. Rollback & Contingency Plan
- `FILEGROWTH` değişikliği önceki değere alınabilir; disk servis katmanı değişikliği sağlayıcının geri alma planına bağlıdır.
- Duraklatılan planlı işleri tekrar etkinleştirin; atlanan yedeklerin telafisini doğrulayın.