Durant mes audits, j’ai vu un certain nombre de fois où le(s) sous système(s) disque(s) étai(en)t une des causes principales de problèmes de performances. J’ai eu quelques fois à refaire le plan de répartition des fichiers de bases de données ainsi que l’architecture disque sous jacente qui n’était pas optimale. Pour pouvoir réaliser cette tâche, il est préférable de connaître les caractéristiques d’entrées / sorties appliquées aux fichiers de bases de données et implicitement au(x) sous système(s) disque(s) qui les hébergent. Le script suivant permet de réaliser cette tâche.

Celui-ci permet notamment de connaître le ratio de lecture ou d’écriture pour chaque fichier de base de données, la latence et la quantité moyenne de données pour chaque type d’opération.

Cela peut aider par exemple :

- à la répartition des fichiers de bases de données sur différents axes physiques en fonction de l’activité I/O
- à savoir si un sous système disque est performant (en se basant sur la latence)
- à préparer des tests de performances pour paramétrer un sous système disque en se basant sur la quantité moyenne de données par type d’opération (en conjonction avec SQLIO par exemple).

 

/***********************************************
* @Author = Mikedavem                          *
* @Description =                               *
* Statistiques IO sur les fichiers de bases de *
* données                                      *
* Il est possible de classer par  :            *
* – Quantité moyenne d’octets par lecture      *
* – Quantité moyenne d’octets par écriture     *
* – Latence moyenne de lecture                 *
* – Latence moyenne d’écriture                 *
* – Ratio lecture fichier / lecture totale     *
* – Raito écriture fichier / écriture totale   *
************************************************/

WITH io_file_stats
AS
(
    SELECT
        d.name AS database_name,
        f.name AS [file_name],
        f.physical_name,
        f.type_desc,
        vf.num_of_reads,
        vf.num_of_writes,
        CAST(vf.num_of_bytes_read * 1. / (vf.num_of_reads + 1) / 1024 AS DECIMAL(18,2)) AS avg_kbytes_read,
        CAST(vf.io_stall_read_ms * 1. / (vf.num_of_reads + 1) AS DECIMAL(18,2)) AS avg_stall_read_ms,
        CAST(vf.num_of_bytes_written * 1. / (vf.num_of_writes + 1) / 1024  AS DECIMAL(18,2)) AS avg_kbytes_written,
        CAST(vf.io_stall_write_ms * 1. / (vf.num_of_writes + 1) AS DECIMAL(18,2)) AS avg_stall_write_ms,
        CAST(vf.num_of_reads * 1. / SUM(num_of_reads + 1) OVER() * 100 AS DECIMAL(5,2)) AS percent_reads,
        CAST(vf.num_of_writes * 1. / SUM(num_of_writes + 1) OVER() *100 AS DECIMAL(5,2)) AS percent_writes
    FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vf
    INNER JOIN sys.databases AS d
    ON d.database_id = vf.database_id
    INNER JOIN sys.master_files AS f
    ON f.file_id = vf.file_id
     AND f.database_id = vf.database_id
),
io_file_stats_rank
AS
(
SELECT
    *,
    RANK() OVER (ORDER BY avg_kbytes_read DESC) AS rank_avg_kbytes_reads,
    RANK() OVER (ORDER BY avg_stall_read_ms DESC) AS rank_avg_stall_read_ms,
    RANK() OVER (ORDER BY avg_kbytes_written DESC) AS rank_avg_kbytes_written,
    RANK() OVER (ORDER BY avg_stall_write_ms DESC) AS rank_avg_stall_write_ms,
    RANK() OVER (ORDER BY percent_reads DESC) AS rank_percent_reads,
    RANK() OVER (ORDER BY percent_writes DESC) AS rank_percent_writes
FROM io_file_stats
)
SELECT
    database_name,
    [file_name],
    physical_name,
    type_desc,
    num_of_reads,
    num_of_writes,
    avg_kbytes_read,
    avg_stall_read_ms,
    avg_kbytes_written,
    avg_stall_write_ms,
    percent_reads,
    percent_writes
FROM io_file_stats_rank
– Choix du classement =
– rank_avg_kbytes_reads
– rank_avg_stall_read_ms
– rank_avg_kbytes_written
– rank_avg_stall_write_ms
– rank_percent_reads
– rank_percent_writes
ORDER BY rank_percent_reads;

Bon audit !!

David BARBARIN (Mikedavem)
Elève ingénieur CNAM Lyon
MVP SQL Server