SQLProfiler : Analyse avancée de traces

La création d’une trace profiler est un passage quasi obligatoire lorsqu’il s’agit d’auditer les performances et les ressources monopolisées des requêtes, lots de requêtes ou des procédures stockées qui s’exécutent sur le serveur de bases de données lors d’un audit. Bien qu’il existe les DMV depuis la version 2005, celles-ci ne peuvent être réellement utilisées qu’après une période significative de fonctionnement du serveur. Par conséquent il est plus intéressant de les utiliser dans un contexte de production que dans celui d’un audit ponctuel.

De plus, il peut exister plusieurs exécutions d’une même requête ou procédure dans une trace profiler mais avec des paramètres différents. Une question peut alors se poser : comment connaître les durées et consommations globales des différents modèles de requêtes (indépendamment de la valeur des paramètres utilisés) et pouvoir ainsi mettre l’accent sur l’optimisation de certains modèles de requêtes ?

Lors de mon dernier audit, j’ai également dû répondre à la problématique suivante : le serveur de bases de données comportait plusieurs instances SQL Server. Dans un tel cas, comment connaitre le ratio entre la consommation d’une requête exécutée sur une instance et celle de l’ensemble des instances présentes sur ce même serveur ? Cela peut être utile pour cibler les requêtes ou procédures les plus consommatrices à l’échelle du serveur et pour lesquelles il est utile de revoir la conception.

Le script suivant permet de répondre aux deux problématiques décrites ci-dessus.

Ce script est divisé en plusieurs parties :

- Création d’une fonction SQL qui permettra de concevoir les modèles de requêtes en fonction des requêtes exécutés dans la trace profiler.
- Création d’une table temporaire de travail pour effectuer les analyses nécessaires
- Import des données du ou des fichiers de trace profiler
- Analyse des différentes consommations de requêtes

Script :

/****************************************************************
* @Auteur = BARBARIN DAVID (Mikedavem)                         *
* @Description =                                               *
* Analyse des fichiers de trace                                *
* Eléments du script :                                         *
* – Fonction dbo.SQLSig : Génération des patterns de requêtes *
* – #Workload : Table de travail pour les analyses de requêtes *
****************************************************************/

USE tempdb;
GO

/******************************************************************

* Fonction de génération des modèles de requêtes                 *

* Paramètres d’entrée :                                          *

* – @p1 : Requête ou procédure                                   *

******************************************************************/

IF EXISTS (SELECT * FROM sys.sql_modules

           WHERE object_id = OBJECT_ID(‘dbo.SQLSig’))

    DROP FUNCTION dbo.SQLSig;

GO

– Création de la fonction de traitement des patterns

CREATE FUNCTION dbo.SQLSig

(@p1 NVARCHAR(MAX))

RETURNS NVARCHAR(MAX)

WITH SCHEMABINDING

AS

– Fonction réalisée par la Team SQL Server Customer Advisory

– Auteur = Stuart Ozer

BEGIN

DECLARE @pos INT;

DECLARE @mode CHAR(10);

DECLARE @maxlength INT;

DECLARE @p2 NVARCHAR(MAX);

DECLARE @p2i NVARCHAR(MAX);

DECLARE @currchar CHAR(1);

DECLARE @nextchar CHAR(1);

DECLARE @p2len INT;

SET @maxlength = LEN(RTRIM(SUBSTRING(@p1, 1, 4000)));

SET @pos = 1;

SET @p2i = »;

SET @p2 = »;

SET @p2len = 0;

SET @currchar = »;

SET @nextchar = »;

SET @mode = ‘command';

– Modification David BARBARIN (pour prendre en compte sp_executesql)

IF (PATINDEX(‘%sp_executesql%’, @p1)) > 0

BEGIN

    SET @pos = PATINDEX(‘% »,@%’,@p1) + 2;

    SET @p2i = SUBSTRING(@p1, 1, PATINDEX(‘% »,@%’,@p1) + 1);

    SET @p2 = SUBSTRING(@p1, PATINDEX(‘% »,@%’,@p1) + 2, LEN(@p1));

END

WHILE (@pos <= @maxlength)

BEGIN

SET @currchar = SUBSTRING(@p1, @pos, 1);

SET @nextchar = SUBSTRING(@p1, @pos + 1, 1);

IF @mode = ‘command’

BEGIN

   SET @p2 = LEFT(@p2, @p2len) + @currchar;

   SET @p2len = @p2len + 1;

   IF @currchar IN (‘,’,'(‘,’ ‘,’=’,'<‘,’>’,’!’)

       AND @nextchar BETWEEN ‘0’ AND ‘9’

   BEGIN

    SET @mode = ‘number';

    SET @p2 = LEFT(@p2, @p2len) + ‘#';

    SET @p2len = @p2len + 1;

   END

   IF @currchar = » »

   BEGIN

    SET @mode = ‘literal';

    SET @p2 = LEFT(@p2, @p2len) + ‘# »';

    SET @p2len = @p2len + 2;

   END

END

ELSE IF @mode = ‘number’ AND @nextchar IN (‘,’,’)’,’ ‘,’=’,'<‘,’>’,’!’)

   SET @mode = ‘command';

ELSE IF @mode = ‘literal’ AND @currchar = » »

   SET @mode = ‘command’

SET @pos = @pos + 1;

END

RETURN @p2i + @p2;

END;

GO

/******************************************************************
* Création de la table de travail pour les analyses              *
******************************************************************/
IF EXISTS(SELECT * FROM tempdb.sys.objects
          WHERE object_id = OBJECT_ID(‘tempdb.dbo.#workload’))
    DROP TABLE #workload;

CREATE TABLE #workload
(
ServerName VARCHAR(50),
TextData VARCHAR(MAX),
DatabaseName VARCHAR(100),
ApplicationName VARCHAR(100),
Duration INT,
Reads INT,
Writes INT,
CPU INT,
Template AS dbo.SQLSig(TextData) PERSISTED,
CheckSum_Template AS CHECKSUM(dbo.SQLSig(TextData)) PERSISTED
);
GO

– Création index cluster sur la table de travail
CREATE CLUSTERED INDEX idx_clust_workload
ON #workload
(
CheckSum_Template
);
GO

/******************************************************************
* Import des données des fichiers de trace                       *
* Paramètres d’entrée :                                          *
* – @path_trc : Chemin du fichier de trace à importer            *
******************************************************************/
DECLARE @path_trc VARCHAR(100);
SET @path_trc = ‘path_fichier_trace';

INSERT INTO #workload (ServerName, TextData, DatabaseName, ApplicationName, Duration, Reads, Writes, CPU)
SELECT ServerName, TextData, DatabaseName, ApplicationName, Duration, Reads, Writes, CPU
FROM fn_trace_gettable(@path_trc, NULL);
GO

/******************************************************************
* Requête d’analyse par pattern des données du fichier de trace *
* Paramètres de tri : @classement                                *
* = reads    = Classement IO lectures                            *
* = writes   = Classement IO écritures                           *
* = CPU      = Classement par consommation CPU                   *
* = Duration = Classement par durée d’exécution                  *
******************************************************************/

DECLARE @classement VARCHAR(10)
SET @classement = ‘duration'; — Type de classement : duration, cpu, reads or writes

WITH total_workload_by_server_checksum_template
AS
(
    SELECT
        CheckSum_Template,
        ServerName,
        COUNT(*) AS nb_executions,
        SUM(CAST(duration AS BIGINT)) AS total_duration_by_template_server,
        SUM(CAST(CPU AS BIGINT)) AS total_cpu_by_template_server,
        SUM(CAST(Reads AS BIGINT)) AS total_reads_by_template_server,
        SUM(CAST(Writes AS BIGINT)) AS total_writes_by_template_server
    FROM #workload
    GROUP BY ServerName, CheckSum_Template
),
total_workload_total_servers
AS
(
     SELECT
        W.*,
        W.total_duration_by_template_server * 100.0 / S.duration_total_server AS percent_duration_template_server,
        W.total_cpu_by_template_server * 100.0 / S.total_cpu_total_server AS percent_cpu_template_server,
        W.total_reads_by_template_server * 100.0 / S.total_reads_server AS percent_reads_template_server,
        W.total_writes_by_template_server * 100.0 / S.total_writes_server AS percent_writes_template_server,
        W.total_duration_by_template_server * 100.0 / T.duration_total AS percent_duration_total,
        W.total_cpu_by_template_server * 100.0 / T.cpu_total AS percent_cpu_total,
        W.total_reads_by_template_server * 100.0 / T.reads_total AS percent_reads_total,
        W.total_writes_by_template_server * 100.0 / T.writes_total AS percent_writes_total
     FROM total_workload_by_server_checksum_template AS W
     INNER JOIN
     (
        SELECT
            ServerName,
            SUM(CAST(duration AS BIGINT)) AS duration_total_server,
            SUM(CAST(CPU AS BIGINT)) AS total_cpu_total_server,
            SUM(CAST(Reads AS BIGINT)) AS total_reads_server,
            SUM(CAST(Writes AS BIGINT)) AS total_writes_server
        FROM #workload
        GROUP BY ServerName
     ) AS S
     ON S.ServerName = W.ServerName
     CROSS JOIN
     (
        SELECT
            SUM(CAST(duration AS BIGINT)) AS duration_total,
            SUM(CAST(CPU AS BIGINT)) AS cpu_total,
            SUM(CAST(Reads AS BIGINT)) AS reads_total,
            SUM(CAST(Writes AS BIGINT)) AS writes_total
        FROM #workload
     ) AS T
),
rank_template_workload_by_type
AS
(
SELECT
    *,
    RANK() OVER(ORDER BY percent_duration_total DESC) AS rank_duration,
    RANK() OVER(ORDER BY percent_cpu_total DESC) AS rank_cpu,
    RANK() OVER(ORDER BY percent_reads_total DESC) AS rank_reads,
    RANK() OVER(ORDER BY percent_writes_total DESC) AS rank_writes,
    RANK() OVER(ORDER BY percent_duration_template_server DESC) AS rank_duration_server,
    RANK() OVER(ORDER BY percent_cpu_template_server DESC) AS rank_cpu_server,
    RANK() OVER(ORDER BY percent_reads_template_server DESC) AS rank_reads_server,
    RANK() OVER(ORDER BY percent_writes_template_server DESC) AS rank_writes_server
FROM total_workload_total_servers
)
SELECT
W.ServerName,
W.Template,
R.nb_executions,
R.percent_duration_total,
R.percent_duration_template_server,
R.percent_cpu_total,
R.percent_cpu_template_server,
R.percent_reads_total,
R.percent_reads_template_server,
R.percent_writes_total,
R.percent_writes_template_server,
R.total_duration_by_template_server AS total_Duration,
R.total_cpu_by_template_server AS total_CPU,
R.total_reads_by_template_server AS total_Reads,
R.total_writes_by_template_server AS total_Writes,
W.TextData,
W.Duration,
W.CPU,
W.Reads,
W.Writes
FROM #workload AS W
INNER JOIN rank_template_workload_by_type AS R
ON W.CheckSum_Template = R.CheckSum_Template
AND W.ServerName = R.ServerName
–Récupération des requêtes les plus consommatrices (>= 5%)
WHERE (@classement = ‘duration’ AND R.percent_duration_total >= 5)
OR (@classement = ‘cpu’ AND R.percent_cpu_total >=5)
OR (@classement = ‘reads’ AND R.percent_reads_total >= 5)
   OR (@classement = ‘writes’ AND R.percent_writes_total >= 5)
ORDER BY CASE @classement
          WHEN ‘duration’ THEN R.rank_duration
          WHEN ‘cpu’ THEN R.rank_cpu
          WHEN ‘reads’ THEN R.rank_reads
          WHEN ‘writes’ THEN R.rank_writes
         END,
         CASE @classement
          WHEN ‘duration’ THEN R.rank_duration_server
          WHEN ‘cpu’ THEN R.rank_cpu_server
          WHEN ‘reads’ THEN R.rank_reads_server
          WHEN ‘writes’ THEN R.rank_writes_server
         END,
         CASE @classement
          WHEN ‘duration’ THEN W.Duration
          WHEN ‘cpu’ THEN W.CPU
          WHEN ‘reads’ THEN W.Reads
          WHEN ‘writes’ THEN W.writes
         END DESC;

Remarque :

La fonction de traitement des requêtes pour générer les modèles associés peut poser un problème si le fichier de trace à important est trop important. Par habitude, je préfère importer plusieurs fichiers de trace à volumétrie moyenne qu’un seul gros fichier à la fois.

Bon audit de trace !

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

Identifiez-vous

Créer un compte

David Barbarin

MVP DataPlatform – MCM SQL Server

SQLProfiler : Analyse avancée de traces

Laisser un commentaire Annuler la réponse.

Identifiant
Mot de passe

Mot de passe oublié ?