Estimation des IO (ES) en lecture et écriture table par table d’une base SQL

Laisser une réponse

Les deux requêtes que je vous présente permettent d’estimer le nombres de pages lues et écrites table par table tenant compte de tous les index des tables. Attention : il ne s’agit pas d’une mesure exacte. Une telle mesure est impossible mais bien d’une estimation pour connaître les tables les plus écrites et celles les plus lues de manières relatives les unes aux autres.

Elle se base sur le nombre d’opération unitaire d’accès effectué sur chaque table et index. En gros, il y a trois types d’accès :
1) un accès par balayage de la table ou de l’index (scan) appelé « analyse » dans les plans de requête en français;
2) un accès pour recherche dichotomique dans l’index (seek) appelé « recherche » dans les plans de requête en français;
3) un accès multi recherche dans l’index (lookup) appelé « recherche de clés » dans les plans de requête en français;

Nous avons compter qu’un SCAN balayait toutes les pages de l’index ou de la table, ce qui est faux dans certains cas particulier (par exemple recherche TOP n)
Nous avons compter qu’un SEEK parcourait un nombre de page équivalent à la profondeur de l’arbre ce qui n’est pas vrai pour les recherches de type « range » (par exemple lors d’un BETWEEN)
Nous avons compter qu’un LOOKUP était l’équivalent de 10 seeks. Mais cela peut être plus ou moins et dans certains cas, ce n’est même pas réellement l’équivalent d’un seek.

Ces approximations nous permettent quand même une bonne évaluation de la surface d’attaque des données des tables en production, dans un but comparatif, voir les tables les plus accédées en lecture ou en écriture.

La requête pour les lectures :

WITH
idx_details AS
(
SELECT ius.object_id, ius.index_id, user_seeks, user_lookups, user_scans,
       COALESCE(INDEXPROPERTY(i.object_id, i.name, 'IndexDepth '), 0)
          AS index_depth,
       (SELECT SUM(used_page_count) AS pages
        FROM   sys.dm_db_partition_stats AS s
        WHERE  s.object_id = ius.object_id
          AND  s.index_id  = ius.index_id) AS page_count
FROM   sys.dm_db_index_usage_stats AS ius
       JOIN sys.indexes AS i
          ON  ius.object_id = i.object_id
          AND ius.index_id  = i.index_id
WHERE  database_id = DB_ID()
AND    ius.index_id  0 OR user_lookups > 0 OR user_scans > 0)  
),
TOPT AS
(
SELECT s.name AS TABLE_SCHEMA, o.name AS TABLE_NAME,
       SUM(user_seeks + 10 * user_lookups * index_depth) +
       SUM(user_scans * page_count) AS IO_READS_ESTIMATE,
       100.0 * (SUM(user_seeks + 10 * user_lookups * index_depth) +
       SUM(user_scans * page_count)) /
       SUM(NULLIF(SUM(user_seeks + 10 * user_lookups * index_depth) +
       SUM(user_scans * page_count), 0)) OVER()  AS PERCENT_READS_ESTIMATE
FROM   idx_details AS id
       JOIN sys.objects AS o
            ON id.object_id = o.object_id
       JOIN sys.schemas AS s
            ON s.schema_id = o.schema_id
GROUP  BY s.name, o.name
HAVING SUM(user_seeks + 10 * user_lookups * index_depth) +
       SUM(user_scans * page_count) > 0
)
SELECT TABLE_SCHEMA, TABLE_NAME, IO_READS_ESTIMATE,
       CAST(PERCENT_READS_ESTIMATE AS DECIMAL(5, 2)) AS PERCENT_READS_ESTIMATE,
       CAST(SUM(PERCENT_READS_ESTIMATE)
          OVER(ORDER BY PERCENT_READS_ESTIMATE DESC) AS DECIMAL(5, 2))
          AS CUMUL_PERCENT,
       CAST((SELECT sqlserver_start_time
             FROM   sys.dm_os_sys_info) AS datetime2(0)) AS SINCE
FROM   TOPT
ORDER  BY IO_READS_ESTIMATE DESC;

La requête pour les écritures :

WITH
idx_details AS
(
SELECT ius.object_id, SUM(user_updates * 100) AS user_updates
FROM   sys.dm_db_index_usage_stats AS ius
       JOIN sys.indexes AS i
          ON ius.object_id = i.object_id AND ius.index_id = i.index_id
WHERE  database_id = DB_ID()
AND    ius.index_id  0
GROUP  BY ius.object_id
),
TOPT AS
(
SELECT s.name AS TABLE_SCHEMA, o.name AS TABLE_NAME,
       user_updates AS IO_WRITES_ESTIMATE,
       100.0 * user_updates / SUM(user_updates) OVER()
          AS PERCENT_WRITES_ESTIMATE
FROM   idx_details AS id
       JOIN sys.objects AS o
            ON id.object_id = o.object_id
       JOIN sys.schemas AS s
            ON s.schema_id = o.schema_id
GROUP  BY s.name, o.name, user_updates
)
SELECT TABLE_SCHEMA, TABLE_NAME, IO_WRITES_ESTIMATE,
       CAST(PERCENT_WRITES_ESTIMATE AS DECIMAL(5, 2))
          AS PERCENT_WRITES_ESTIMATE,
       CAST(SUM(PERCENT_WRITES_ESTIMATE)
          OVER(ORDER BY PERCENT_WRITES_ESTIMATE DESC) AS DECIMAL(5, 2))
          AS CUMUL_PERCENT,
       CAST((SELECT sqlserver_start_time
             FROM   sys.dm_os_sys_info) AS datetime2(0)) AS SINCE
FROM   TOPT
ORDER  BY PERCENT_WRITES_ESTIMATE DESC;

Un exemple de résultat pour la lecture :

TABLE_SCHEMA   TABLE_NAME                  IO_READS_ESTIMATE    PERCENT_READS_ESTIMATE  CUMUL_PERCENT   SINCE
-------------- --------------------------- -------------------- ----------------------- --------------- ---------------------------
S_GEO          COMMUNE                     748792               55.59                   55.59           2018-01-16 16:17:28
S_RTE          TRONCON_ROUTE               520044               38.61                   94.19           2018-01-16 16:17:28
S_ADR          CODE_INSEE_CODE_POSTAL      42147                3.13                    97.32           2018-01-16 16:17:28
S_GEO          DEPARTEMENT                 36057                2.68                    100.00          2018-01-16 16:17:28
Frédéric Brouard, alias SQLpro, ARCHITECTE DE DONNÉES
Expert  S.G.B.D  relationnelles   et   langage  S.Q.L
Moste  Valuable  Professionnal  Microsoft  SQL Server
Société SQLspot  :  modélisation, conseil, formation,
optimisation,  audit,  tuning,  administration  SGBDR
Enseignant: CNAM PACA, ISEN Toulon, CESI Aix en Prov.

L’entreprise SQL Spot
Le site web sur le SQL et les SGBDR

MVP Microsoft SQL Server

Laisser un commentaire