Agrégation des statistiques d’IO d’une requête

Laisser une réponse

Si le paramétrage SET STATISTICS IO ON de Microsoft SQL Server permet de savoir, table par table les statistiques d’entrées/sorties (IO pour Input/Output) des requêtes, il n’est pas toujours facile de s’y repérer lorsque la requête est complexe et fait appel à de multiples tables. Voici une procédure qui en effectue la synthèse.

Rappelons que les statistiques IO sont le nombre de pages lues par le moteur SQL pour exécuter une requête. Le paramètre de session STATISTICS IO peut être mis à ON ou OFF. Par défaut il est à OFF. Mis sur ON, il permet, dans SQL Server Management Studio (SSMS), de voir le nombre de pages accédées pour une requête ou un lot de requête. C’est une série de message d’une ligne commençant par « Table… » qui suit immédiatement le message indiquant le nombre de lignes affectées par la requête.

SQL Server onglet message STATISTICS IO

SQL Server onglet message STATISTICS IO

Néanmoins il est assez difficile de lire tout cela et d’en déduire une tendance globale.
Ne serait-ce pas plus intéressante de les présenter de cette manière :

SQL Server STATISTICS IO aggregate

Statistiques IO SQL Server avec totaux et agrégation

La petite procédure que voici permet justement d’effectuer cette tâche. Il suffit de lui passer le texte complet des message d’IO et elle vous retournera tout cela, formaté, totalisé et agrégé !

USE msdb;
GO

CREATE SCHEMA S_DBA;
GO

CREATE PROCEDURE S_DBA.P_SPLIT_STATS_IO @DATA NVARCHAR(MAX)
AS
SET NOCOUNT ON;
DECLARE @LINE NVARCHAR(MAX), @SQL NVARCHAR(MAX) = N'';
DECLARE @L TABLE (LINE NVARCHAR(800));
DECLARE @T TABLE (TABLE_NAME             sysname,
                  SCAN_COUNT             BIGINT,
                  LOGICAL_READS          BIGINT,
                  PHYSICAL_READS         BIGINT,
                  READ_AHEAD_READS       BIGINT,
                  LOB_LOGICAL_READS      BIGINT,
                  LOB_PHYSICAL_READS     BIGINT,
                  LOB_READ_AHEAD_READS   BIGINT);

WHILE CHARINDEX(NCHAR(13) + NCHAR(10), @DATA) > 0
BEGIN
   SET @LINE = LEFT(@DATA, CHARINDEX(NCHAR(13) + NCHAR(10), @DATA)  - 2);
   INSERT INTO @L SELECT @LINE;
   SET @DATA = RIGHT(@DATA, LEN(@DATA) - CHARINDEX(NCHAR(13) + NCHAR(10), @DATA) - 1);
END;
INSERT INTO @L SELECT @DATA;
DELETE @L WHERE LINE = N'' OR LINE NOT LIKE N'%Table %' COLLATE French_BIN;

IF NOT EXISTS(SELECT *
              FROM   @L
              WHERE  LINE NOT LIKE '%Nombre d''analyses%')
--> français
BEGIN
   UPDATE @L SET LINE = REPLACE(LINE, 'Table ', '');
   UPDATE @L SET LINE = REPLACE(LINE, '. Nombre d''analyses ', ',');
   UPDATE @L SET LINE = REPLACE(LINE, ' lectures logiques ', '');
   UPDATE @L SET LINE = REPLACE(LINE, ' lectures physiques ', '');
   UPDATE @L SET LINE = REPLACE(LINE, ' lectures anticipées ', '');
   UPDATE @L SET LINE = REPLACE(LINE, 'de données d''objets volumineux ', '');
   UPDATE @L SET LINE = REPLACE(LINE, '.', '');
END;

IF NOT EXISTS(SELECT *
              FROM   @L
              WHERE  LINE NOT LIKE '%Scan count%')
--> anglais
BEGIN
   UPDATE @L SET LINE = REPLACE(LINE, 'Table ', '');
   UPDATE @L SET LINE = REPLACE(LINE, '. Scan count ', ',');
   UPDATE @L SET LINE = REPLACE(LINE, ' logical reads ', '');
   UPDATE @L SET LINE = REPLACE(LINE, ' physical reads ', '');
   UPDATE @L SET LINE = REPLACE(LINE, ' read-ahead reads ', '');
   UPDATE @L SET LINE = REPLACE(LINE, 'lob', '');
   UPDATE @L SET LINE = REPLACE(LINE, '.', '');
END;


SELECT @SQL = @SQL + 'SELECT ' + LINE + ' UNION ALL ' FROM @L;
SET @SQL = LEFT(@SQL, LEN(@SQL) - 10)

INSERT INTO @T
EXEC (@SQL);

WITH
T AS
(
SELECT *
FROM   @T
UNION ALL
SELECT '°°° TOTAL °°°', SUM(SCAN_COUNT),
       SUM(LOGICAL_READS), SUM(PHYSICAL_READS), SUM(READ_AHEAD_READS),
       SUM(LOB_LOGICAL_READS),SUM(LOB_PHYSICAL_READS),SUM(LOB_READ_AHEAD_READS)
FROM   @T
)
SELECT *
FROM T
ORDER BY CASE WHEN TABLE_NAME = '°°° TOTAL °°°' THEN 1 ELSE 0 END,
         LOGICAL_READS DESC;  

SELECT SUM(SCAN_COUNT) AS TOTAL_SCANS,
       SUM(LOGICAL_READS + LOB_LOGICAL_READS) AS TOTAL_LOGICAL_READS,
       SUM(PHYSICAL_READS + READ_AHEAD_READS +
           LOB_PHYSICAL_READS + LOB_READ_AHEAD_READS) AS TOTAL_PHYSICAL_READS
FROM   @T;

GO

Exemple d’utilisation :

EXECUTE msdb.S_DBA.P_SPLIT_STATS_IO 'Table ''PARAM_EAC''. Nombre d''analyses 13, lectures logiques 26, lectures physiques 0, lectures anticipées 0, lectures logiques de données d''objets volumineux 0, lectures physiques de données d''objets volumineux 0, lectures anticipées de données d''objets volumineux 0.
Table ''Worktable''. Nombre d''analyses 0, lectures logiques 0, lectures physiques 0, lectures anticipées 0, lectures logiques de données d''objets volumineux 0, lectures physiques de données d''objets volumineux 0, lectures anticipées de données d''objets volumineux 0.
Table ''VAL_PARAM_EAC''. Nombre d''analyses 1, lectures logiques 59, lectures physiques 0, lectures anticipées 0, lectures logiques de données d''objets volumineux 0, lectures physiques de données d''objets volumineux 0, lectures anticipées de données d''objets volumineux 0.
Table ''ESP''. Nombre d''analyses 49, lectures logiques 31, lectures physiques 0, lectures anticipées 0, lectures logiques de données d''objets volumineux 0, lectures physiques de données d''objets volumineux 0, lectures anticipées de données d''objets volumineux 0.
Table ''TL_EXP_COOP''. Nombre d''analyses 49, lectures logiques 178, lectures physiques 0, lectures anticipées 0, lectures logiques de données d''objets volumineux 0, lectures physiques de données d''objets volumineux 0, lectures anticipées de données d''objets volumineux 0.
Table ''UC''. Nombre d''analyses 49, lectures logiques 5633, lectures physiques 0, lectures anticipées 0, lectures logiques de données d''objets volumineux 0, lectures physiques de données d''objets volumineux 0, lectures anticipées de données d''objets volumineux 0.
Table ''TL_PP_UC''. Nombre d''analyses 49, lectures logiques 1254, lectures physiques 0, lectures anticipées 0, lectures logiques de données d''objets volumineux 0, lectures physiques de données d''objets volumineux 0, lectures anticipées de données d''objets volumineux 0.
Table ''PG''. Nombre d''analyses 49, lectures logiques 85289, lectures physiques 0, lectures anticipées 0, lectures logiques de données d''objets volumineux 0, lectures physiques de données d''objets volumineux 0, lectures anticipées de données d''objets volumineux 0.
Table ''Worktable''. Nombre d''analyses 0, lectures logiques 0, lectures physiques 0, lectures anticipées 0, lectures logiques de données d''objets volumineux 0, lectures physiques de données d''objets volumineux 0, lectures anticipées de données d''objets volumineux 0.
Table ''PG''. Nombre d''analyses 1, lectures logiques 437, lectures physiques 0, lectures anticipées 0, lectures logiques de données d''objets volumineux 0, lectures physiques de données d''objets volumineux 0, lectures anticipées de données d''objets volumineux 0.
Table ''PP''. Nombre d''analyses 49, lectures logiques 7271, lectures physiques 0, lectures anticipées 0, lectures logiques de données d''objets volumineux 0, lectures physiques de données d''objets volumineux 0, lectures anticipées de données d''objets volumineux 0.
Table ''EXP''. Nombre d''analyses 49, lectures logiques 1996, lectures physiques 0, lectures anticipées 0, lectures logiques de données d''objets volumineux 0, lectures physiques de données d''objets volumineux 0, lectures anticipées de données d''objets volumineux 0.
Table ''VAR''. Nombre d''analyses 49, lectures logiques 866, lectures physiques 0, lectures anticipées 0, lectures logiques de données d''objets volumineux 0, lectures physiques de données d''objets volumineux 0, lectures anticipées de données d''objets volumineux 0.'

Le résultat :

Les statistiques d'IO d'une requête, sus forme tabulaire, totalisés et agrégés

Les statistiques d’IO d’une requête, sous forme tabulaire, totalisés et agrégés

QUELQUES EXPLICATIONS SUR LES MÉTRIQUES

  • analyses (scan count) : nombre de fois ou l’exécuteur de requête a du pénétrer dans un index de la table ou dans la table (en cas de parallélisme, autant de fois que de threads…)
  • lectures logiques (logical reads) : nombre de pages lues en RAM
  • lectures physiques (physical reads) : nombre de pages lues depuis le disque (avec transfert en RAM) en mode aléatoire.
  • lectures anticipées (read-ahead physical read) : nombre de pages lues depuis le disque (avec transfert en RAM) en mode continu.
  • …d’objets volumineux (lob …) : nombre de pages … pour les « Large OBjects ».

    • NOTA : cet outil fonctionne que votre serveur soit en anglais ou français.

    LE CODE * LE CODE * LE CODE * LE CODE * LE CODE * LE CODE * LE CODE * LE CODE * LE CODE
    Le code !

    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

    Développez et administrez pour la performance avec SQL Server 2014

    Développez et administrez pour la performance avec SQL Server 2014

    Laisser un commentaire