MSSQL Sunucularında Disk Latency Analizi

MSSQL veritabanı sunucularında yaşanan performans tıkanıklıklarının (bottleneck) arkasında genellikle gözden kaçan bir I/O darboğazı yatar. İşlemci veya bellek kullanımı normal görünse bile, disklerin verilere yetişememesi sorguların kilitlenmesine ve genel sistem yavaşlığına yol açar.

Diskin genel doluluk oranını izlemek yeterli değildir; asıl kritik nokta, veri tabanı dosyalarının yer aldığı disklerin okuma ve yazma işlemlerinde ne kadar gecikmeye (latency) uğradığını saptamaktır.

Aşağıdaki T-SQL sorgusu, sunucudaki tüm veri tabanı dosyalarının bulunduğu disk harflerini gruplayarak okuma, yazma ve toplam gecikme sürelerini hesaplar:

SELECT 
@@servername as name,
tab.[Drive], tab.volume_mount_point AS [Volume Mount Point],
    CASE
        WHEN num_of_reads = 0 THEN 0
        ELSE (io_stall_read_ms/num_of_reads)
    END AS [Read Latency],
    CASE
        WHEN num_of_writes = 0 THEN 0
        ELSE (io_stall_write_ms/num_of_writes)
    END AS [Write Latency],
    CASE
        WHEN (num_of_reads = 0 AND num_of_writes = 0) THEN 0
        ELSE (io_stall/(num_of_reads + num_of_writes))
    END AS [Overall Latency],
    CASE
        WHEN num_of_reads = 0 THEN 0
        ELSE (num_of_bytes_read/num_of_reads)
    END AS [Avg Bytes/Read],
    CASE
        WHEN num_of_writes = 0 THEN 0
        ELSE (num_of_bytes_written/num_of_writes)
    END AS [Avg Bytes/Write],
    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 AS [Avg Bytes/Transfer], getdate() as Date  
	FROM (SELECT LEFT(UPPER(mf.physical_name), 2) AS Drive, SUM(num_of_reads) AS num_of_reads,
             SUM(io_stall_read_ms) AS io_stall_read_ms, SUM(num_of_writes) AS num_of_writes,
             SUM(io_stall_write_ms) AS io_stall_write_ms, SUM(num_of_bytes_read) AS num_of_bytes_read,
             SUM(num_of_bytes_written) AS num_of_bytes_written, SUM(io_stall) AS io_stall, vs.volume_mount_point
      FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
      INNER JOIN sys.master_files AS mf WITH (NOLOCK)
      ON vfs.database_id = mf.database_id AND vfs.file_id = mf.file_id
      CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.[file_id]) AS vs
      GROUP BY LEFT(UPPER(mf.physical_name), 2), vs.volume_mount_point) AS tab
ORDER BY [Overall Latency] OPTION (RECOMPILE);

Sorgu sonucunda elde edilen milisaniye (ms) türündeki Read Latency, Write Latency ve Overall Latency değerlerini yorumlarken şu eşikleri baz alabilirsiniz:

0 – 5 ms: Mükemmel performans (genellikle NVMe veya yüksek performanslı enterprise SSD / SAN altyapıları).
5 – 15 ms: İdeal kabul edilebilir aralık.
15 – 20 ms: Dikkat edilmesi gereken sınır; sistem yük altındayken uyarılara açık hale gelir.
20 ms ve üzeri: Doğrudan I/O darboğazı anlamına gelir. Kullanıcıların gecikme hissetmeye başlayacağı kritik seviyedir.

Sunucu üzerinde diskin okuma yazma değerlerini göreceğimiz başka bir komut:

SELECT DISTINCT @@servername as name, vs.volume_mount_point, vs.file_system_type, vs.logical_volume_name,
CONVERT(DECIMAL(18,2), vs.total_bytes/1073741824.0) AS [Total Size (GB)],
CONVERT(DECIMAL(18,2), vs.available_bytes/1073741824.0) AS [Available Size (GB)],  
CONVERT(DECIMAL(18,2), vs.available_bytes * 1. / vs.total_bytes * 100.) AS [Space Free %],
vs.supports_compression, vs.is_compressed,
vs.supports_sparse_files, vs.supports_alternate_streams, getdate() as Date
FROM sys.master_files AS f WITH (NOLOCK)
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.[file_id]) AS vs
ORDER BY vs.volume_mount_point OPTION (RECOMPILE);

Sorguda yer alan Avg Bytes/Read ve Avg Bytes/Write sütunları ise disk işlemlerinin boyutunu gösterir. Küçük baytlı işlemler yoğun index/seek aramalarına, yüksek baytlı işlemler ise büyük tablo taramalarına (table scan) veya toplu veri aktarımlarına işaret eder.

Sonuçların sunucu açıldığından (SQL Server servisinin başlatıldığı andan) bu yana biriken toplam veriler üzerinden hesaplandığı unutulmamalıdır. Bu nedenle, anlık değişimi izlemek adına verileri belirli periyotlarla kaydedip iki zaman aralığı arasındaki farkı analiz etmek en doğru yaklaşımı sunacaktır.

Başka bir makalede görüşmek dileğiyle..

Ey iman edenler, sabırla ve namazla Allah’tan yardım dileyin. Şüphesiz Allah, sabredenlerle beraberdir. Bakara Suresi, 153. Ayet

Bir yanıt yazın