Bei plötzlich auftretenden Performanceproblemen sollte überprüft werden, ob die Festplatten, die die Datenbank nutzt, noch einwandfrei funktionieren.
Hierfür kann folgende SQL Abfrage ausgeführt und ein Hardwareproblem erkannt werden.
<snip>
SELECT LEFT(mf.physical_name,100),
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,
AvgLatency = CASE WHEN (num_of_reads = 0 AND num_of_writes = 0) THEN 0
ELSE (io_stall / (num_of_reads + num_of_writes)) END,
LatencyAssessment = CASE WHEN (num_of_reads = 0 AND num_of_writes = 0) THEN ‚Keine Dateien‘ ELSE
CASE WHEN (io_stall / (num_of_reads + num_of_writes)) < 2 THEN ‚Exzellent‘
WHEN (io_stall / (num_of_reads + num_of_writes)) BETWEEN 2 AND 5 THEN ‚Sehr gut‘
WHEN (io_stall / (num_of_reads + num_of_writes)) BETWEEN 6 AND 15 THEN ‚Gut‘
WHEN (io_stall / (num_of_reads + num_of_writes)) BETWEEN 16 AND 100 THEN ‚Schlecht‘
WHEN (io_stall / (num_of_reads + num_of_writes)) BETWEEN 100 AND 500 THEN ‚Sehr schlecht‘
ELSE ‚Hoechstwahrscheinlich Defekt‘ END END,
[Avg KBs/Transfer] = 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)) / 1024) END,
LEFT (mf.physical_name, 2) AS Volume,
LEFT(DB_NAME (vfs.database_id),32) AS [Database 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
ORDER BY AvgLatency DESC
</snip>
