Windows Server ve SQL Server Seviyesinde Disk I/O Analizi

Bu makalede mssql server üzerinde sunucu ve veritabanı seviyesinde disklerimizin performansını analiz etmiş olacağız. Scriptler eşliğinde ihtiyacımız olan değerleri görüp Disk konusunda herhangi bir sorun varsa önceden aksiyon almamızı sağlayacağız.

Aşağıda bulunan ilk komutumuzda servis restart olduktan sonra veritabanı bazlı toplam I/O değerleri görülmektedir.

SELECT
DB_NAME(database_id) AS DatabaseName,
SUM(num_of_reads + num_of_writes) AS TotalIO
FROM sys.dm_io_virtual_file_stats(NULL, NULL)
GROUP BY database_id
ORDER BY TotalIO DESC;

Aşağıdaki komut ile veritabanı seviyesinde okuma yazma sayısını okuma ve yazmların MB boyutunu ve toplam I/O değerini görebiliriz.

SELECT
DB_NAME(database_id) AS DatabaseName,
SUM(num_of_reads) AS TotalReads,
SUM(num_of_writes) AS TotalWrites,
SUM(num_of_reads + num_of_writes) AS TotalIO,
SUM(num_of_bytes_read) / 1024 / 1024 AS MB_Read,
SUM(num_of_bytes_written) / 1024 / 1024 AS MB_Written
FROM sys.dm_io_virtual_file_stats(NULL, NULL)
GROUP BY database_id
ORDER BY TotalIO DESC;

Aşağıdaki komut ile sorgularımızın memory üzerinden ne kadar okuma yaptığını bu okumlar sonucu cpu kullanım durumu ve sorgularımızın execution plan yapısını görebiliriz.

select 
     q.[text],
     SUBSTRING(q.text, (qs.statement_start_offset/2)+1, 
        ((CASE qs.statement_end_offset
          WHEN -1 THEN DATALENGTH(q.text)
         ELSE qs.statement_end_offset
         END - qs.statement_start_offset)/2) + 1) AS statement_text,        
     qs.last_execution_time,
     qs.execution_count,
     qs.total_logical_reads as total_logical_read,
     qs.total_logical_reads/execution_count as avg_logical_read,
     qs.total_worker_time/1000000 as total_cpu_time_sn,
     qs.total_worker_time/qs.execution_count/1000 as avg_cpu_time_ms,
     qp.query_plan,
     DB_NAME(q.dbid) as database_name,
     q.objectid,
     q.number,
     q.encrypted
from 
    (select top 50 
          qs.last_execution_time,
          qs.execution_count,
		  qs.plan_handle, 
          qs.total_worker_time,
          qs.total_logical_reads,
          qs.statement_start_offset,
          qs.statement_end_offset
    from sys.dm_exec_query_stats qs
WHERE qs.execution_count>5 and qs.total_worker_time/qs.execution_count/1000>500
    order by qs.total_worker_time desc) qs
cross apply sys.dm_exec_sql_text(plan_handle) q
cross apply sys.dm_exec_query_plan(plan_handle) qp
order by qs.total_logical_reads desc

Sunucu üzerinde bulunan disklerimizin sağlığını öğrenmek için aşağıdaki komutun çalıştırılması gerekmektedir.

Get-PhysicalDisk |
Select FriendlyName, HealthStatus, OperationalStatus, Size

Reallocated sector / write error varsa → disk ölüyor olabilir.

Get-StorageReliabilityCounter |Select DeviceId, ReadErrorsTotal, WriteErrorsTotal

WriteErrors artıyorsa → disk alarm veriyor demektir.

Windows disk hata kontrolü Event Viewer >Windows Logs >System bölümünden filtreleme yapılarak bakılabilir.

Filtrele:
• Source: Disk
• Source: StorAHCI
• Source: Ntfs

Bad block, reset, timeout görürsen → storage alarm vermektedir.

İlgili bölüme girildikten sonra Event sources kısmından belirtilen ifadelerin seçilmesi gerekmektedir.

Windows Disk performansını canlı izlemek için backup veya başka büyük bir işlem çalışırken Performance Monitor açılır:

Counter’lar:
• PhysicalDisk
• Avg. Disk sec/Write
• Avg. Disk sec/Read
• Disk Queue Length

Eğer:
• Avg Disk sec/Write > 0.050
• Queue Length sürekli yüksekse

Disk yetişemiyor diyebiliriz.

Yukarıda görülen 3-5-5-9 ifadeleri disk numarasıdır. 5 ifadesi Disk 5’i göstermektedir.

Seçilen ifadelerin daha sonra değerlendirilmesi gerekmektedir.

Disk latency kontrolü en önemli kontrollerden birisidir.

SQL Server için ideal değerler:
• Read latency: < 20 ms
• Write latency: < 20 ms
Backup için write latency özellikle önemli.

SELECT
vfs.database_id,
DB_NAME(vfs.database_id) AS db_name,
mf.name,
mf.physical_name,
vfs.num_of_reads,
vfs.io_stall_read_ms / NULLIF(vfs.num_of_reads,0) AS read_latency_ms,
vfs.num_of_writes,
vfs.io_stall_write_ms / NULLIF(vfs.num_of_writes,0) AS write_latency_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL) vfs
JOIN sys.master_files mf
ON vfs.database_id = mf.database_id
AND vfs.file_id = mf.file_id
ORDER BY write_latency_ms DESC;

Write latency 50–100 ms üstüyse → backup günlerce sürer.

Anlık Disk Response Time (EN KULLANILANI)

Bu komut canlı olarak disk read/write response time (ms) gösterir:

Get-Counter '\PhysicalDisk(*)\Avg. Disk sec/Read',
'\PhysicalDisk(*)\Avg. Disk sec/Write'

Çıktı saniye cinsindendir → ms için 1000 ile çarp.

İdeal değerler (SQL Server için)

Değer İyi Riskli
Read < 0.020 s (20 ms) > 0.050 s
Write < 0.020 s (20 ms) > 0.050 s

Sürekli ölçüm (5 saniyede bir – okunabilir tablo)

while ($true) {
Get-Counter '\PhysicalDisk(*)\Avg. Disk sec/Read',
'\PhysicalDisk(*)\Avg. Disk sec/Write' |
Select -ExpandProperty CounterSamples |
Select InstanceName,
@{N='Read(ms)';E={[math]::Round($_.CookedValue*1000,2)}},
@{N='Write(ms)';E={[math]::Round($_.CookedValue*1000,2)}}
Start-Sleep 5
}

Backup veya büyük bir işlem çalışırken bunu aç → hangi disk çöküyor net görebilirsin.

Disk Queue Length + Response Time (daha derin analiz)

Get-Counter '\PhysicalDisk(*)\Avg. Disk Queue Length',
'\PhysicalDisk(*)\Avg. Disk sec/Read',
'\PhysicalDisk(*)\Avg. Disk sec/Write'

Yorum:
• Queue Length sürekli > 2 → disk yetişemiyor
• Response time yüksek + queue yüksek → storage bottleneck

Bu makalede windows server ve sql server üzerinde disk parametreleri konusunda nelere bakmamız gerektiğini görmüş olduk Başka makalede görüşmek dileğiyle..

“Mutlu olanlar ise cennettedirler.” (Hud, 11/108)

Bir yanıt yazın