Latences IO par fichier – Scripts Center

Ce script SQL permet d’afficher les latences en millisecondes par volume fichier de bases de données ou de journal, 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, checkdb ou réindexations, l’indication est plus une tendance générale de saturation ou non des IO sur certains fichiers. Les trois colonnes de moyenne avg_read_stall_ms, avg_write_stall_ms et avg_io_stall_ms sont les plus significatives. Dans un monde idéal, les latences sur un fichier de données ne devraient pas dépasser 15 ms et sur un fichier journal 1 à 2 ms…

/*-------------------------------------------------------------------
[SCRIPT] IO statistics per file
[DATEMAJ] 20191227 
-------------------------------------------------------------------*/
-- Calculates average stalls per read, per write, and per total input/output for each database file  (Query 26) (IO Stalls by File)
SELECT DB_NAME(fs.database_id) AS [Database Name],
       CONVERT(DECIMAL(18, 2), mf.size / 128.0) AS [File Size (MB)],
       mf.physical_name,
       io_stall_read_ms,
       num_of_reads,
       CAST(io_stall_read_ms / (1.0 + num_of_reads) AS NUMERIC(10, 1)) AS [avg_read_stall_ms],
       io_stall_write_ms,
       num_of_writes,
       CAST(io_stall_write_ms / (1.0 + num_of_writes) AS NUMERIC(10, 1)) AS [avg_write_stall_ms],
       io_stall_read_ms + io_stall_write_ms AS [io_stalls],
       num_of_reads + num_of_writes AS [total_io],
       CAST((io_stall_read_ms + io_stall_write_ms) / (1.0 + num_of_reads + num_of_writes) AS NUMERIC(10, 1)) AS [avg_io_stall_ms]
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS fs
    INNER JOIN sys.master_files AS mf WITH (NOLOCK)
        ON fs.database_id = mf.database_id
           AND fs.[file_id] = mf.[file_id]
ORDER BY avg_io_stall_ms DESC
OPTION (RECOMPILE);

/*-------------------------------------------------------------------

[SCRIPT] IO statistics per file

[DATEMAJ] 20191227

-------------------------------------------------------------------*/

-- Calculates average stalls per read, per write, and per total input/output for each database file (Query 26) (IO Stalls by File)

SELECT DB_NAME(fs.database_id) AS [Database Name],

CONVERT(DECIMAL(18, 2), mf.size / 128.0) AS [File Size (MB)],

mf.physical_name,

io_stall_read_ms,

num_of_reads,

CAST(io_stall_read_ms / (1.0 + num_of_reads) AS NUMERIC(10, 1)) AS [avg_read_stall_ms],

io_stall_write_ms,

num_of_writes,

CAST(io_stall_write_ms / (1.0 + num_of_writes) AS NUMERIC(10, 1)) AS [avg_write_stall_ms],

io_stall_read_ms + io_stall_write_ms AS [io_stalls],

num_of_reads + num_of_writes AS [total_io],

CAST((io_stall_read_ms + io_stall_write_ms) / (1.0 + num_of_reads + num_of_writes) AS NUMERIC(10, 1)) AS [avg_io_stall_ms]

FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS fs

INNER JOIN sys.master_files AS mf WITH (NOLOCK)

ON fs.database_id = mf.database_id

AND fs.[file_id] = mf.[file_id]

ORDER BY avg_io_stall_ms DESC

OPTION (RECOMPILE);