Sql Server performansını etkileyen en önemli etkenlerden biri depolama(storage) performansıdır. Çünkü Yazma, okuma işlemlerinde aygıtlar içerisinde en yavaşı olan depolama birimleri ve özellikle disklerdir. Veri depolama yapılarında(disk, ssd vb.) tutulur ve çalışma esnasında hafızaya(memory) taşıma, güncelleme, kopyalama vb. gibi bir çok işlem görebilir. Diskten hafızaya oradan tekrar diske taşınabilir. Sql Server’ın yürüttüğü bu işlemlerde performans kaybı oluştuğunda I/O Bottleneck (darboğaz) durumu oluşabilir. Bu performans kayıpları bazı bekleme türlerinde(wait types) kendisini gösterir. Bu durumda işlemlerimizin optimum performansla yürütülmeye devam edilebilmesi için depolama performansının gözlenmesi gerekir.
Bu bekleme türlerine (wait types) dikkat!
sys.dm_os_wait_stats view’ı çalıştırılan tüm görevlere dair bekleme durumları ile ilgili edinebileceğimiz view’dır. Aşağıdaki bekleme türlerinde performas kaybı gözlediğimizde sistemde I/O Bottleneck oluştuğunu düşünebiliriz.
•PAGEIOLATCH
•WRITELOG
•TRACEWRITE
•SQLTRACE_FILE_WRITE_IO_COMPLETION
•ASYNC_IO_COMPLETION
•IO_COMPLETION
•LOG_BUFFER
Nasıl İzlenir?
Microsoft’un «best practice» en iyi deneyimlerinde bottleneck(darboğaz) durumu ile ilgili bazı sayaçlar (performance counter) ve referans değerler belirlenmiştir. Bu darboğazı gözlemlemek için Performance Monitor aracı ve sayaçlar (performance counters) kullanılır. SQL server ile ilgili yüzlerce sayaç vardır ancak disk performansını gözlemlemek için gerekli olanları aşağıda özetlemeye çalıştım. Bunları sys.dm_os_performance_counters dmv’si aracılığıyla görebiliriz. Bu izleme yavaş diskler, tıkanan işlemler vb. sorunlarda bize yardımcı olur.
SQL Server’ın kullandığı depolama ünitelerinin performansı üç farklı yönden ölçülür. Bunlar:
- Saniyedeki I/O işlemleri (I/O operations per second, IOPS)
- Akan veri miktarı diyebileceğimiz disk throughput
- Gecikme (Disk Latency)
Bunların nasıl ölçülebileceğini sırayla açıklayalım
- IOPS şu performans sayaçlarıyla ölçülür:
Disk Reads / Sec : Saniyedeki okuma sayısı
Disk Writes / Sec : Saniyedeki yazma sayısı
- Throughput kavramı periyodik belirli bir sürede aktarılan veri miktarını ifade eder. Şu sayaçlarla ölçülür:
Disk Read Bytes / Sec : Okuma işlemleri sırasında diskten saniyede okunan veri miktarı
Disk Bytes /Sec : Okuma ya da yazma işlemleri boyunca diskten okunan ya da yazılan veri miktarı
Disk Write Bytes / Sec : Yazma işlemleri boyunca saniyede diske yazılan veri miktarı
- Latency(gecikme) I/O isteğinin oluşmasından cevap gelmesi arasında geçen süreyi ifade eder. Şu sayaçlarla ölçülür:
Avg. Disk sec / Read : Diskten veriyi okumak için geçen ortalama zaman
Avg. Disc Sec / Write : Diske veriyi yazmak için geçen ortalama zaman
Microsoft’un her iki sayaç için best practice değerleri şunlardır:
Log Dosyaları için 1-5 ms (1ms ideal)
OLTP Data dosyaları için 4-20 ms (10 ms ideal)
OLAP Data dosyaları için <= 30 ms
Aşağıdaki tabloda I/O performansını ölçmek için kullanılan diğer bazı sayaçların anlamlarını ve referans değerlerini görebiliriz.
| Referans Değeri | ||
| Physical Disk | ||
| %DiskTime | Fiziksel ya da mantıksal disk bölümlerinin ne yoğunlukta kullanıldığı | Avg Disk Queue Length değerinin 100 katından fazla olmamalı |
| Avg. Disk Queue Length | Her bir disk ya da disk bölümü için I/O kuyruğu oluşturan ortalama bekleyen kuyruk (okuma ve yazma) sayısı | =2 |
| Split IO/Sec | Diske yapılan I/O’nun birden çok I/O’ya bölünme hızını gösterir.Sıfırdan farklı bir değer diskteki fragmantasyonu ve performansın etkilendiğini gösterir. | =0 |
| Process | ||
| IO Data Bytes/sec | Her bir prosesin disk kullanımı toplam diskten ne kadarını kullanıyor? En çok I/O tüketen proses. | |
| IO Other Bytes/sec | Sql Server’ın veritabanı hariç kullandığı sistem kaynağı |