SQL Server Disk Gecikmeleri

Veri tabanı sağlık taramalarında yapılması gereken ve hatta veritabanı seviyesinde performans problemleri yaşanmaya başlandığında kontrol edilmesi gereken alanlardan biri de veri tabanlarınızın çalıştığı / koştuğu disk yapılarınızdır. Yaşanan darboğazın yada sorunun nereden kaynaklandığını SQL Server bilgisi verebilecek özellikler ile donatılmıştır.

SQL Server ’da disk gecikmelerini kontrol etmenin aslında birkaç yolu vardır. Fakat bu yazı dahilinde en sık kullanılan DMV – Dynamic Management View – anlatıyor olacağım.

Microsoft 2005 yılında sys.dm_io_virtual_file_stats adında bir DMV kullanılabilir olduğunun duyurdu. SQL Server en son açılıştan bu yana yürütme planları, kaynak kullanımı, index’lerin fiziksel mantıksal kullanımlarıyla ilgili verileri kaydeder. Bunları sorgulayarak sistemin durumu hakkında bilgi alabiliriz. dm_io_virtual_file_stats fonksiyonu bunlardan biri olup veri tabanlarına ait data ve log dosyalarının I/O kullanım istatistiklerini döndürür.
sys.dm_io_virtual_file_stats (
{ database_id | NULL }
, { file_id | NULL }
)
Aşağıdaki sorguyu kullanarak bu DMV yi detaylandırabiliriz.

SELECT
   [ReadLatency] =
        CASE WHEN [num_of_reads] = 0
            THEN 0 ELSE ([io_stall_read_ms] / [num_of_reads]) END,
   [WriteLatency] =
        CASE WHEN [num_of_writes] = 0
            THEN 0 ELSE ([io_stall_write_ms] / [num_of_writes]) END,
   [Latency] =
        CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0)
            THEN 0 ELSE ([io_stall] / ([num_of_reads] + [num_of_writes])) END,
   [Latency Desc] = 
         CASE 
            WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0) THEN 'N/A' 
            ELSE 
               CASE WHEN ([io_stall] / ([num_of_reads] + [num_of_writes])) < 2 THEN 'Excellent'
                    WHEN ([io_stall] / ([num_of_reads] + [num_of_writes])) < 6 THEN 'Very good'
                    WHEN ([io_stall] / ([num_of_reads] + [num_of_writes])) < 11 THEN 'Good'
                    WHEN ([io_stall] / ([num_of_reads] + [num_of_writes])) < 21 THEN 'Poor'
                    WHEN ([io_stall] / ([num_of_reads] + [num_of_writes])) < 101 THEN 'Bad'
                    WHEN ([io_stall] / ([num_of_reads] + [num_of_writes])) < 501 THEN 'Eyvalar olsun!'
               ELSE 'Gözüme Gözünme!!'
               END 
         END, 
   [AvgBPerRead] =
        CASE WHEN [num_of_reads] = 0
            THEN 0 ELSE ([num_of_bytes_read] / [num_of_reads]) END,
   [AvgBPerWrite] =
        CASE WHEN [num_of_writes] = 0
            THEN 0 ELSE ([num_of_bytes_written] / [num_of_writes]) END,
   [AvgBPerTransfer] =
        CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0)
            THEN 0 ELSE
                (([num_of_bytes_read] + [num_of_bytes_written]) /
                ([num_of_reads] + [num_of_writes])) END,
   LEFT ([mf].[physical_name], 2) AS [Drive],
   DB_NAME ([vfs].[database_id]) AS [DB],
   [mf].[physical_name]
FROM
   sys.dm_io_virtual_file_stats (NULL,NULL) AS [vfs]
   JOIN sys.master_files AS [mf]
   ON [vfs].[database_id] = [mf].[database_id]
      AND [vfs].[file_id] = [mf].[file_id]
 --WHERE DB_NAME ([vfs].[database_id])='DBA' -- db name
ORDER BY [Latency] DESC
-- ORDER BY [ReadLatency] DESC
-- ORDER BY [WriteLatency] DESC;
GO

Sorgu çıktısı aşağıdaki gibidir.

“Latency Desc” isimli kolon sorgu sonucunu anlamlandırma noktasında bizlere yardımcı olacaktır. Tabi ki aşağıdaki değerlere bağlı olarak.

0 to 1 ms	Excellent
2 to 5 ms	Very good
6 to 10 ms	Good
11 to 20 ms	Poor
21 to 100 ms	Bad
101 to 500 ms	Eyvahlar olsun!
more than 500 ms	Gözüme Gözükme!!!

Burada dikkat edilecek kısım aslında 101 ms’nin üzerinde gördüğünüz değerlerdir. Bu değerler arasını görüyorsanız veritabanı seviyesinde yapabileceklerinizin ne kadar iyi olursa olsun disk bazlı bir darboğaz yaşanıyor demektir. Örneğimizde tempdb veri ve log dosyalarının disk gecikmesinin sorunlu olduğunu rahatlıkla görebiliyoruz. SQL Server yeniden başlatıldığında bu veriler sıfırlanır.
SQL Server ‘da Database dosyalarının mümkün olduğunca farklı fiziksel diskler üzerinde olması önemli bir performans sağlamaktadır. Aşağıdaki script veritabanı dosyalarının bulunduğu sürücüler üzerindeki trafiği gösterir.

select left(mf.physical_name, 1) as drive_letter, sample_ms,
sum(vfs.num_of_writes) as total_num_of_writes,
sum(vfs.num_of_bytes_written) as total_num_of_bytes_written,
sum(vfs.io_stall_write_ms) as total_io_stall_write_ms,
sum(vfs.num_of_reads) as total_num_of_reads,
sum(vfs.num_of_bytes_read) as total_num_of_bytes_read,
sum(vfs.io_stall_read_ms) as total_io_stall_read_ms,
sum(vfs.io_stall) as total_io_stall,
sum(vfs.size_on_disk_bytes) as total_size_on_disk_bytes
from sys.master_files mf
join sys.dm_io_virtual_file_stats(NULL, NULL) vfs
on mf.database_id=vfs.database_id and mf.file_id=vfs.file_id
group by left(mf.physical_name, 1), sample_ms

Bu sorgunun sonucunda trafiğin büyük bir kısmı belli sürücü(-ler) üzerinde akıyorsa oradaki bazı dosyaları başka sürücülere taşımamız fayda sağlayacaktır.

DMC Bilgi Teknolojileri
Çağlar Özenç