Datenbank Festplattenprüfung

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>

High Quality Theme

There is a fine line between creating greatness and accomplishing awesomeness. This time, we worked to prove that sky is not the limit.

Fill out the form to subscribe to our newsletter.

[contact-form-7 404 "Nicht gefunden"]

High Quality Theme

There is a fine line between creating greatness and accomplishing awesomeness. This time, we worked to prove that sky is not the limit.

Fill out the form to subscribe to our newsletter.

[contact-form-7 404 "Nicht gefunden"]