Vous êtes ici :
Ce script SQL permet d’afficher les latences en millisecondes par volume disque, en lecture ou en écriture : ces éléments sont cumulés par SQL Server depuis le dernier démarrage de l’instance. Il faut donc que SQL Server soit utilisé depuis un moment. Comme cela cumule jour et nuit, et aussi bien production que sauvegardes ou réindexations, l’indication est plus une tendance générale de saturation ou non du système de stockage.
Transact-SQL
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 |
/*------------------------------------------------------------------- [SCRIPT] IO Latency per drive [DATEMAJ] 20191227 -------------------------------------------------------------------*/ SELECT [Drive], CASE WHEN num_of_reads = 0 THEN 0 ELSE (io_stall_read_ms / num_of_reads) END AS [Read Latency], CASE WHEN io_stall_write_ms = 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 io_stall_write_ms = 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] FROM ( SELECT LEFT(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 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 GROUP BY LEFT(mf.physical_name, 2) ) AS tab ORDER BY [Overall Latency] OPTION (RECOMPILE); |
Table of Contents