IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Administration SQL Server Discussion :

Extended events : requêtes les plus longues; vos bonnes pratiques?


Sujet :

Administration SQL Server

  1. #1
    Membre chevronné
    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    avril 2013
    Messages
    1 630
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : High Tech - Produits et services télécom et Internet

    Informations forums :
    Inscription : avril 2013
    Messages : 1 630
    Points : 1 967
    Points
    1 967
    Par défaut Extended events : requêtes les plus longues; vos bonnes pratiques?
    Hello,

    Je travaille actuellement avec SSMS et j'utilise une session d'extended events pour tracer les requêtes les plus longues.
    J'avoue être un peu perdu entre le wizard, les templates, les global fields, les events, les actions, les predicates etc etc (et après on me dire que SQL Server est plus simple que Oracle!).

    Pour le moment j'arrive à afficher des infos en utilisant l'event sql_statement_completed.
    Nom : Sans titre 2.jpg
Affichages : 67
Taille : 42,3 Ko

    L'objectif est bien sur d'afficher les infos permettant de tuner un ordre SQL en disant par exemple : le SELECT retourne 100 lignes, il doit pour cela lire 10 000 blocs... il y aurait un pb d'accès aux données. Après je peux croiser les infos avec le plan d'exécution.
    Pour ceux qui connaissent, ce que je veux se rapproche de la commande AUTOTRACE d'Oracle.

    Donc, vous, en tant qu'experts SQL Server, qu'est-ce que vous vous utilisez comme Events et configuration?

    Merci pour les retours
    DBA Oracle
    Rédacteur du blog : dbaoraclesql.canalblog.com

  2. #2
    Expert éminent sénior
    Avatar de mikedavem
    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    août 2005
    Messages
    5 390
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 42
    Localisation : France, Ain (Rhône Alpes)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : Distribution

    Informations forums :
    Inscription : août 2005
    Messages : 5 390
    Points : 12 722
    Points
    12 722
    Par défaut
    Citation Envoyé par Ikebukuro Voir le message
    Hello,

    Je travaille actuellement avec SSMS et j'utilise une session d'extended events pour tracer les requêtes les plus longues.
    J'avoue être un peu perdu entre le wizard, les templates, les global fields, les events, les actions, les predicates etc etc (et après on me dire que SQL Server est plus simple que Oracle!).
    <Troll mode>Oui c'est même mieux que Oracle et moins cher </Troll mode>

    Citation Envoyé par Ikebukuro Voir le message
    L'objectif est bien sur d'afficher les infos permettant de tuner un ordre SQL en disant par exemple : le SELECT retourne 100 lignes, il doit pour cela lire 10 000 blocs... il y aurait un pb d'accès aux données. Après je peux croiser les infos avec le plan d'exécution.
    Pour ceux qui connaissent, ce que je veux se rapproche de la commande AUTOTRACE d'Oracle.
    Si tu veux te rapprocher de AUTOTRACE, je pense qu'il vaut mieux utiliser les options SET STASTISTICS IO / TIME sur SQL Server et qui te donneront les informations dont tu as besoin. Alors tu ne trouveras pas la notion de bloc (qui est le jargon Oracle) dans le résultat mais la notion de pages mais ca devrait aller je pense ^^

    ++

  3. #3
    Membre chevronné
    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    avril 2013
    Messages
    1 630
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : High Tech - Produits et services télécom et Internet

    Informations forums :
    Inscription : avril 2013
    Messages : 1 630
    Points : 1 967
    Points
    1 967
    Par défaut
    Je te remercie beaucoup.
    Sinon, je pense que tu utilises les extended events, as-tu des conseils pour le débutant que je suis pour identifier les requêtes lentes, quels événements choisir etc etc ?
    DBA Oracle
    Rédacteur du blog : dbaoraclesql.canalblog.com

  4. #4
    Rédacteur
    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    mai 2002
    Messages
    20 689
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 20 689
    Points : 49 027
    Points
    49 027
    Billets dans le blog
    1
    Par défaut
    Extended Events c'est un scalpel qui s'utilise pour cherche des problématiques complexes et fine là ou tout le reste à été insuffisant pour dégrossir le chose. C'est l'aiguille de pin dans la forêt.

    Dans oracle tu peut t'intéresser aux requêtes gourmandes mais c'est aussi s'attaquer à quelques arbres qui masque la forêt.... pas efficace.

    • La première des choses à voir sur un serveur MS SQL est de voir si toute les bonnes pratiques ont été appliquées. La plupart du temps cela débloque 50 à 90% des problèmes.
    • La seconde des choses à faire consiste a mener une campagne d'indexation : rajouter les index manquants et supprimer les index inutiles, redondants (doublons) ou inclus.
    • La troisième des choses est de voir quelles sont les objets lents (pas seulement les requêtes) : requêtes, procédures, déclencheurs et UDF.



    Si et seulement si ces 3 choses là n'ont pas résorbé le problème alors tu peut commencer à utiliser le profileur SQL et l'analyseur de performances. Si cela ne te suffit pas, tu peut alors utiliser les événements étendus.

    1 - les bonnes pratiques

    1.1 - Configuration OS

    Si VM, voir si
    1.1.1 - les cœurs ne sont pas flottant
    1.1.2 - la RAM n'est pas en "balooning"
    1.1.3 - le stockage est dédié

    Voir les bonnes pratiques VMWare :
    https://www.vmware.com/content/dam/d...ices-guide.pdf

    Au niveau Windows, vérifier si le mode économie d'énergie est actif. Si oui, le désactiver pour rouler au max de fréquence des CPU.

    1.2 - Configuration périmétrique

    Vérifiez qu'il n'y a pas d'antivirus.
    S'il y en a, isolez tous les répertoires de données des bases de production et système.
    Préférez l'antivirus MS qui n'est pas plus mauvais que les autres, mais plus léger et ne s'occupe pas des éléments MS et notamment de SQL Server.

    Vérifiez que SQL Server tourne sur une machine dédiée, ce qui signifie :
    aucune autre instance MS SQL Server
    aucun autre service applicatif d'aucun genre (autre SGBD, application tierce....)

    Désactivez le maximum de services Windows inutiles.

    1.3 - configuration d'instance SQL Server

    Vérifiez que votre instance SQL Server soit à jour des dernier SP pour les versions jusqu'à 2016 et CU pour les versions à partir de 2017
    Aidez vous de : https://sqlserverbuilds.blogspot.com/


    Vérifiez les paramètres suivants (sp_configure) :
    affinity I/O mask, affinity mask, affinity64 I/O mask, affinity64 mask à 0

    cost threshold for parallelism : à mettre entre 12 et 100 suivant importances des bases (12 petites bases, 24 moyennes bases, 50 grosses bases, 100 VLDB)
    max degree of parallelism : à positionner en fonction du nombre de cœurs si pas fait au niveau installation, avec le calcul suivant :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    Nombre de cœurs   MAXDOP
    ----------------- -------
        < 8            2
     8 à 12            3
    14 à 20            4
    22 à 32            5
    34 à 48            6
    50 à 64            7
    66 à 96            8
       > 96           10
    Pour une solution OLTP

    Pour de l'OLAP, 50% des cœurs.

    On compte les cœurs hyperthreadés (cœurs logiques)

    max server memory (MB) : à positionner en fonction de la quantité de RAM, si pas fait au niveau installation, avec le calcul suivant :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    DECLARE @RAM_MACHINE_MB INT = (SELECT physical_memory_kb / 1024 
                                   FROM sys.dm_os_sys_info);
    WITH
    T AS
    (
    SELECT @RAM_MACHINE_MB AS RAM_MACHINE_MB, 
           CASE WHEN @RAM_MACHINE_MB < 8192 
                   THEN 2048
                WHEN @RAM_MACHINE_MB < 16384
                   THEN 2048 + 1024 * (@RAM_MACHINE_MB - 8192) / 8192 
                WHEN @RAM_MACHINE_MB < 32768
                   THEN 3096 + 1024 * (@RAM_MACHINE_MB - 16384) / 16384
                WHEN @RAM_MACHINE_MB < 131072
                   THEN 4096 + 2048 * (@RAM_MACHINE_MB - 32768) / 98304
                WHEN @RAM_MACHINE_MB < 1048576
                   THEN 6144 + 2048 * (@RAM_MACHINE_MB - 131072) / 917504
                ELSE 8192
           END AS RAM_OS_MB
    )
    SELECT *, RAM_MACHINE_MB - RAM_OS_MB AS RAM_SQL_KB, 
           'EXEC sp_configure ''max server memory (MB)'', ' 
           + CAST(RAM_MACHINE_MB - RAM_OS_MB AS VARCHAR(32)) + ';' + CHAR(13) + CHAR(10)
           + 'RECONFIGURE;'
    FROM   T;
    optimize for ad hoc workloads à 1

    Vous pouvez aussi activer :
    backup checksum default à 1
    backup compression default à 1 si aucune base en chiffrement TDE

    traceflag :
    Si version antérieure à 2016 activez le (TF 2371)
    Si version égale à 2016 sp1 et antérieure à 2019, activez le TF 7412
    Si version 2014 SP1 et +, activez le TF 7471
    Si version 2014 Sp2 et inférieure à 2016, activez le TF 8079

    Vérifiez que vous êtes OK sur le cache en utilisant la requête suivante :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    SELECT instance_name AS NUMA_NODE, cntr_value AS PAGE_LIFE_EXPECTANSY
    FROM   sys.dm_os_performance_counters
    WHERE  object_name LIKE N'%Buffer Node%'
      AND  counter_name = N'Page life expectancy'
    Au minimum à 3 600, bien à 15 000, parfait à 30 000 secondes
    Vérifiez, si vous avez plusieurs noeuds NUMA qu'il n'y ait pas un fort déséquilibre (moins de 10%)

    Vérifiez l'état global de votre stockage disques à l'aide de la requête suivante :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    SELECT db.name AS "DATABASE_NAME", mf.name AS LOGICAL_FILE_NAME, 
           mf.type_desc, size / 128.0 AS SIZE_MB, is_percent_growth, 
           volume_mount_point, 100.0 * available_bytes / NULLIF(total_bytes, 0) AS DISK_PERCENT_FREE,
           io_stall_read_ms / NULLIF(num_of_reads, 0) AS READ_LATENCY_MS,
           io_stall_write_ms / NULLIF(num_of_writes,0) AS WRITE_LATENCY_MS,
           SUM(io_stall_read_ms) OVER (PARTITION BY mf.database_id) / 
              NULLIF(SUM(num_of_reads) OVER(PARTITION BY mf.database_id), 0) AS DB_READ_LATENCY_MS,
           SUM(io_stall_write_ms) OVER (PARTITION BY mf.database_id) / 
              NULLIF(SUM(num_of_writes) OVER(PARTITION BY mf.database_id), 0) AS DB_WRITE_LATENCY_MS,
           SUM(io_stall_read_ms) OVER (PARTITION BY vs.volume_mount_point) / 
              NULLIF(SUM(num_of_reads) OVER(PARTITION BY vs.volume_mount_point), 0) AS DRIVE_READ_LATENCY_MS,
           SUM(io_stall_write_ms) OVER (PARTITION BY vs.volume_mount_point) / 
              NULLIF(SUM(num_of_writes) OVER(PARTITION BY vs.volume_mount_point), 0) AS DRIVE_WRITE_LATENCY_MS
    FROM   sys.master_files AS mf
           JOIN sys.databases AS db ON mf.database_id = db.database_id
           CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id) AS vs
           CROSS APPLY sys.dm_io_virtual_file_stats(mf.database_id, mf.file_id) AS vfs
    Si vous avez des grossissement en pourcentage autre que sur les bases système, remédiez à cela.
    Si vous avez moins de 10% de libre sur vos disques, remédiez à cela
    Si vos latence disques sont supérieures à :
    • 15 ms en écriture
    • 8 ms en lecture


    Vous avez un problème de stockage. Remédiez-y.

    1.4 - Réglages pour tempdb

    Vérifiez bien qu'il y ait au moins 1 fichier par paire de cœurs, jusqu'à concurrence de 8 fichiers.

    Si version antérieures à 2019, activez les traceflag 1117 et 1118

    Dimensionnez correctement vos fichiers de la base de tempdb pour qu'il n'y ait aucune opération de croissance durant l'exploitation ordinaire.

    1.5 - Réglages par base

    Quelques un des réglages au niveau ALTER DATABASE SCOPED CONFIGUATION peuvent être appliqués en fonction de la nature de la base de données. Notamment ceux jouant sur l'optimisation et en particulier l'estimateur de cardinalité.

    2 - Campagne d'indexation

    Deux méthodes :
    2.1 - la méthode brute

    Consiste à créer entre 20 et 60% des index manquants (global au serveur) en prenant ceux ayant le plus de gains (pas ma méthode favorite car surindex les grosses tables au détriment des petites...). Requête pour se faire :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    SELECT ROW_NUMBER() OVER(ORDER BY migs.avg_total_user_cost * migs.avg_user_impact DESC) AS RN,
           100.0 * ROW_NUMBER() OVER(ORDER BY migs.avg_total_user_cost * migs.avg_user_impact DESC) 
           / COUNT(*) OVER() AS PERCENT_CREATED,
           N'CREATE INDEX X_' + REPLACE(CAST(NEWID() AS NVARCHAR(36)), N'-', N'_') + 
           N'_' + CONVERT(CHAR(8), GETDATE(), 112) + N' ON ' + statement +
           N' (' + COALESCE(equality_columns + N', ' + inequality_columns, equality_columns, inequality_columns) + N') ' + 
           CASE WHEN included_columns IS NULL THEN N'' 
                ELSE N' INCLUDE(' + included_columns + ') ' END + 
           N';' AS SQL_CREATE_INDEX,
           migs.avg_total_user_cost * migs.avg_user_impact AS INDICE
    FROM   sys.dm_db_missing_index_details AS mid
           JOIN sys.dm_db_missing_index_groups AS mig
              ON mid.index_handle = mig.index_handle
           JOIN sys.dm_db_missing_index_group_stats AS migs
              ON migs.group_handle = mig.index_group_handle
    ORDER BY RN


    2.2 - la méthode intélligente

    Plus intelligent : auditer chaque index à créer en évitant les inclusions et pseudo redondances à l'aide du processus suivant :
    http://mssqlserver.fr/aide-au-diagno...ms-sql-server/

    2.3 - Supprimer les index inutiles

    Utiliez la requête suivante (par base)
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
     
    SELECT s.name AS TABLE_SCHEMA, o.name AS TABLE_NAME, o.type_desc, i.name AS INDEX_NAME, ius.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
           JOIN sys.objects AS o ON ius.object_id = o.object_id
           JOIN sys.schemas AS s ON o.schema_id = s.schema_id
    WHERE  database_id = DB_ID()
      AND  ius.user_seeks = 0 
      AND  ius.user_lookups = 0 
      AND  ius.user_scans = 0
      AND  i.is_hypothetical = 0
      AND  i.is_primary_key = 0
      AND  i.is_unique = 0
      AND  i.is_unique_constraint = 0
    ORDER BY ius.user_updates;
    2.4 - supprimez les index redondants ou inclus

    Aidez-vous de la requête suivante pour choisir les index à supprimer (par base) :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    82
    83
    84
    85
    86
    87
    88
    89
    90
    91
    92
    93
    94
    95
    96
    97
    WITH 
    TK AS 
    (
    -- clés
    SELECT o.object_id, i.index_id, i.type_desc, i.filter_definition,
           STUFF((SELECT ', ' + c.name + CASE WHEN ic.is_descending_key = 1 THEN ' DESC' ELSE '' END
                  FROM   sys.index_columns AS ic  
                         INNER JOIN sys.columns AS c  
                            ON ic.object_id = c.object_id  
                            AND ic.column_id = c.column_id 
                  WHERE  ic.object_id = i.object_id AND ic.index_id = i.index_id
                    AND  ic.key_ordinal > 0
                    AND  ic.is_included_column = 0
                  ORDER BY ic.key_ordinal
                  FOR XML PATH('')), 1, 2, '') AS KEY_LIST,
           STUFF((SELECT ', ~' + CAST(ic.column_id AS VARCHAR(32)) + CASE WHEN ic.is_descending_key = 1 THEN ' DESC' ELSE '' END + '~'
                  FROM   sys.index_columns AS ic  
                  WHERE  ic.object_id = i.object_id AND ic.index_id = i.index_id
                    AND  ic.key_ordinal > 0
                    AND  ic.is_included_column = 0
                  ORDER BY ic.key_ordinal
                  FOR XML PATH('')), 1, 2, '') AS KEY_ID_LIST
    FROM   sys.indexes AS i
           JOIN sys.objects AS o ON i.object_id = o.object_id
    WHERE  o.is_ms_shipped = 0 AND index_id > 0
    ),
    TI AS
    (
    -- colonnes incluses
    SELECT o.object_id, i.index_id,
           STUFF((SELECT ', ' + c.name 
                  FROM   sys.index_columns AS ic  
                         INNER JOIN sys.columns AS c  
                            ON ic.object_id = c.object_id  
                            AND ic.column_id = c.column_id 
                  WHERE  ic.object_id = i.object_id AND ic.index_id = i.index_id
                    AND  ic.key_ordinal = 0
                    AND  ic.is_included_column = 1
                  ORDER BY c.name
                  FOR XML PATH('')), 1, 2, '') AS INC_LIST
    FROM   sys.indexes AS i
           JOIN sys.objects AS o ON i.object_id = o.object_id
    WHERE  o.is_ms_shipped = 0 AND index_id > 0
      AND  EXISTS(SELECT * 
                  FROM   sys.index_columns AS ic  
                  WHERE  ic.object_id = i.object_id AND ic.index_id = i.index_id
                    AND  ic.is_included_column = 1)
    ),
    TS AS
    (
    -- synthèses
    SELECT TK.object_id, TK.index_id, TK.type_desc, TK.filter_definition, TK.KEY_LIST, TK.KEY_ID_LIST, TI.INC_LIST,
           1 + LEN(KEY_ID_LIST) - LEN(REPLACE(KEY_ID_LIST, ',', '')) AS COLS
    FROM   TK
           LEFT OUTER JOIN TI ON TK.object_id = TI.object_id AND TI.index_id = TK.index_id
    ),
    TX AS
    (
    -- comparaisons
    SELECT T2.object_id, T2.index_id,  
                  T3.index_id AS index_id_anomalie,  
                  T2.KEY_LIST AS CLEF_INDEX,  
                  T3.KEY_LIST AS CLEF_INDEX_ANORMAL,
                  T2.INC_LIST, T3.INC_LIST AS COLONNES_INCLUSES_ANORMAL,  
                  CASE  
                     WHEN T2.KEY_ID_LIST = T3.KEY_ID_LIST  
                       THEN 'DOUBLONS'  
                     WHEN T2.KEY_ID_LIST LIKE T3.KEY_ID_LIST +'%'  
                       THEN 'INCLUS'  
                  END AS ANOMALIE,  
                  ABS(T2.COLS - T3.COLS) AS DISTANCE  
           FROM   TS AS T2  
                  INNER JOIN TS AS T3  
                 ON T2.object_id = T3.object_id  
                    AND T2.index_id <> T3.index_id  
                    AND T2.KEY_ID_LIST LIKE T3.KEY_ID_LIST +'%'
    )
    -- résultat final
    SELECT TX.*,  
           s.name +'.' + o.name AS NOM_TABLE,  
           i1.name AS NOM_INDEX,  
           i2.name AS NOM_INDEX_ANORMAL
           , i1.filter_definition AS FILTRE_INDEX
           , i2.filter_definition AS FILTRE_INDEX_ANORMAL
    FROM   TX  
           INNER JOIN sys.objects AS o  
                 ON TX.object_id = o.object_id  
                 INNER JOIN sys.schemas AS s  
                       ON o.schema_id = s.schema_id  
           INNER JOIN sys.indexes AS i1  
                 ON TX.object_id = i1.object_id  
                    AND TX.index_id = i1.index_id  
           INNER JOIN sys.indexes AS i2  
                 ON TX.object_id = i2.object_id  
                    AND TX.index_id_anomalie = i2.index_id  
    WHERE  o."type" IN ('U', 'V')  
    ORDER  BY NOM_TABLE, NOM_INDEX;

    2.5 - supprimez les statistiques inutiles

    Utilisez la requête suivante (par base)

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    WITH T0 AS
    (
    SELECT s.object_id, s.name AS STAT_NAME, i.name AS INDEX_NAME,
           CASE WHEN i.is_primary_key = 1 THEN 'PRIMAY KEY'
                WHEN i.is_unique_constraint = 1 THEN 'UNIQUE'
           END AS CONSTRAINT_TYPE,
           i.has_filter AS HAS_FILTER,
           (SELECT TOP 1 c.name
            FROM   sys.stats_columns AS sc
                   JOIN sys.columns AS c
                        ON sc.object_id = c.object_id
                        AND sc.column_id = c.column_id
            WHERE  s.object_id = sc.object_id  
              AND  s.stats_id = sc.stats_id
            ORDER BY sc.stats_column_id) AS STAT_COLUMN,
           STUFF((SELECT ', ' + c.name
                  FROM   sys.stats_columns AS sc
                         JOIN sys.columns AS c
                              ON sc.object_id = c.object_id
                              AND sc.column_id = c.column_id
                  WHERE  s.object_id = sc.object_id  
                    AND  s.stats_id = sc.stats_id
                  ORDER BY sc.stats_column_id
                  FOR XML PATH('')), 1, 1, '') AS KEY_COLS  
    FROM   sys.stats AS s
           LEFT OUTER JOIN sys.indexes AS i
                ON s.object_id = i.object_id
                AND s.name = i.name
    )
    SELECT A.*, B.STAT_NAME AS STAT_NAME2, B.INDEX_NAME AS INDEX_NAME2,
           B.CONSTRAINT_TYPE AS CONSTRAINT_TYPE2, B.HAS_FILTER AS HAS_FILTER2,
           B.KEY_COLS AS KEY_COLS2,
           N'DROP STATISTICS [' + s.name + N'].[' + o.name + N'].[' +
                             B.STAT_NAME + N'];' AS DROP_STAT
    FROM   T0 AS A
           JOIN T0 AS B
                ON  A.STAT_COLUMN = B.STAT_COLUMN
                AND A.STAT_NAME > B.STAT_NAME
                AND A.object_id = B.object_id
           JOIN sys.objects AS o
                ON A.object_id = o.object_id
           JOIN sys.schemas AS s
                ON o.schema_id = s.schema_id                
    WHERE  B.INDEX_NAME IS NULL
    AND    o."type" IN ('V', 'U');

    ATTENTION !

    Pour tous ces diagnostics, soyez sûr que l'instance n'a pas redémarrée depuis au moins 31 jours, à l'aide de la requête :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    DECLARE @SQL_START DATETIME = (SELECT sqlserver_start_time 
                                   FROM   sys.dm_os_sys_info);
    DECLARE @SQL_START_STR CHAR(21) = CONVERT(CHAR(19), @SQL_START, 121);
    IF @SQL_START > DATEADD(day, -31, GETDATE())
    BEGIN
       RAISERROR ('The server restarted on %s which is insufficient for a good diagnosis. Try again in a few days', 
                  16, 1, @SQL_START_STR);
       RETURN;
    END;

    3 - objets lents

    Après toutes ces mises en oeuvre et passé un délai de 31 jours, utilisez les requêtes suivantes qui extraient les 25 objets les plus pourris au niveau perf dans chacune des 4 catégories suivantes :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    DECLARE @TOP INT = 25;
    WITH 
    Q AS
    (
    SELECT TOP(@TOP) 'QUERY' AS CATEGORY, 
           SUBSTRING(st.text, 
                            1 + s.statement_start_offset/2,  
                           ((CASE s.statement_end_offset   
                                WHEN -1 THEN DATALENGTH(st.text)  
                                ELSE s.statement_end_offset 
                             END  - s.statement_start_offset)/2) + 1) AS SQL_STATEMENT,
           s.execution_count, s.total_worker_time, s.max_elapsed_time, s.min_elapsed_time,
           CAST(ROUND(1.0 * s.total_worker_time / s.execution_count, 3) AS DECIMAL(32, 3)) AS AVG_EXEC_TIME
    FROM   sys.dm_exec_query_stats AS s
           CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
           CROSS APPLY sys.dm_exec_query_plan(plan_handle)
    ORDER BY total_worker_time DESC
    ),
    P AS
    (
    SELECT TOP(@TOP) 'PROCEDURE' AS CATEGORY, 
           st.text AS SQL_STATEMENT,
           s.execution_count, s.total_worker_time, s.max_elapsed_time, s.min_elapsed_time,
           CAST(ROUND(1.0 * s.total_worker_time / s.execution_count, 3) AS DECIMAL(32, 3)) AS AVG_EXEC_TIME
    FROM   sys.dm_exec_procedure_stats AS s
           CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
           CROSS APPLY sys.dm_exec_query_plan(plan_handle)
    ORDER BY total_worker_time DESC
    ),
    T AS
    (
    SELECT TOP(@TOP) 'TRIGGER' AS CATEGORY, 
           st.text AS SQL_STATEMENT,
           s.execution_count, s.total_worker_time, s.max_elapsed_time, s.min_elapsed_time,
           CAST(ROUND(1.0 * s.total_worker_time / s.execution_count, 3) AS DECIMAL(32, 3)) AS AVG_EXEC_TIME
    FROM   sys.dm_exec_trigger_stats AS s
           CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
           CROSS APPLY sys.dm_exec_query_plan(plan_handle)
    ORDER BY total_worker_time DESC
    ),
    F AS
    (SELECT TOP(@TOP) 'PROCEDURE' AS CATEGORY, 
           st.text AS SQL_STATEMENT,
           s.execution_count, s.total_worker_time, s.max_elapsed_time, s.min_elapsed_time,
           CAST(ROUND(1.0 * s.total_worker_time / s.execution_count, 3) AS DECIMAL(32, 3)) AS AVG_EXEC_TIME
    FROM   sys.dm_exec_function_stats AS s
           CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
           CROSS APPLY sys.dm_exec_query_plan(plan_handle)
    ORDER BY total_worker_time DESC
    )
    SELECT *
    FROM   Q 
    UNION ALL
    SELECT *
    FROM   P 
    UNION ALL
    SELECT *
    FROM   T 
    UNION ALL
    SELECT *
    FROM   F
    ORDER BY total_worker_time
    Enfin, s'il vous reste quelque chose à moudre, utilisez le profiler puis les extended events !

    Au fait, Oracle est-il en mesure de daignosticquer les index manquants ? De le créer automatiquement (en prod dans la version SQL Azure) ? (bientôt disponible dans la version on Premise)... !

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

  5. #5
    Modérateur

    Homme Profil pro
    Consultant Teradata
    Inscrit en
    septembre 2008
    Messages
    8 115
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 43
    Localisation : France, Val de Marne (Île de France)

    Informations professionnelles :
    Activité : Consultant Teradata

    Informations forums :
    Inscription : septembre 2008
    Messages : 8 115
    Points : 16 351
    Points
    16 351
    Par défaut
    Citation Envoyé par SQLpro Voir le message
    Au fait, Oracle est-il en mesure de daignosticquer les index manquants ? De le créer automatiquement (en prod dans la version SQL Azure) ?
    Oui, ils ont implémenté la fonctionnalité Automatic Indexing en 19c, qui crée les index, les teste, les implémente ou les drop en fonction du résultat.
    Un blog en anglais sur le sujet (un peu barrée par contre tout l'intro) :
    https://blogs.oracle.com/oraclemagaz...omous-indexing
    Note que je n'ai pas personnellement essayé la fonctionnalité.

  6. #6
    Membre chevronné
    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    avril 2013
    Messages
    1 630
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : High Tech - Produits et services télécom et Internet

    Informations forums :
    Inscription : avril 2013
    Messages : 1 630
    Points : 1 967
    Points
    1 967
    Par défaut
    Merci pour ce gros gros retour, j'ai de quoi lire

    Ah, pour info, Oracle crée automatiquement les histogrammes, les statistiques étendues sur des colonnes qui ont un lien entre elles et, depuis la 18 ou 19, peut créer aussi de façon autonome les index et les vues matérialisées
    Pas mal, non?
    DBA Oracle
    Rédacteur du blog : dbaoraclesql.canalblog.com

  7. #7
    Expert éminent sénior
    Avatar de mikedavem
    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    août 2005
    Messages
    5 390
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 42
    Localisation : France, Ain (Rhône Alpes)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : Distribution

    Informations forums :
    Inscription : août 2005
    Messages : 5 390
    Points : 12 722
    Points
    12 722
    Par défaut
    Citation Envoyé par Ikebukuro Voir le message
    as-tu des conseils pour le débutant que je suis pour identifier les requêtes lentes, quels événements choisir etc etc ?
    Je serai en revanche plus concis dans la réponse par rapport à SQLPro

    Les événements sont à choisir en fonction de l'activité que tu as mais de manière générale :

    - module_end
    - rpc_completed
    - sql_statement_completed
    - sql_batch_completed

    ++

  8. #8
    Rédacteur
    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    mai 2002
    Messages
    20 689
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 20 689
    Points : 49 027
    Points
    49 027
    Billets dans le blog
    1
    Par défaut
    Citation Envoyé par Waldar Voir le message
    Oui, ils ont implémenté la fonctionnalité Automatic Indexing en 19c, qui crée les index, les teste, les implémente ou les drop en fonction du résultat.
    Un blog en anglais sur le sujet (un peu barrée par contre tout l'intro) :
    https://blogs.oracle.com/oraclemagaz...omous-indexing
    Note que je n'ai pas personnellement essayé la fonctionnalité.
    D'après ce que je lit c'est plutôt l'équivament du DTA de SQL Server qui existe depuis 2008... avec quelques améliorations par rapport à MS...

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

Discussions similaires

  1. top 10 des requêtes les plus longues
    Par cseguino dans le forum Administration
    Réponses: 1
    Dernier message: 10/02/2010, 09h46
  2. Requêtes les plus consommatrices
    Par orafrance dans le forum Administration
    Réponses: 7
    Dernier message: 25/09/2009, 09h22
  3. Requête les plus sollicitées
    Par zut94 dans le forum Développement
    Réponses: 3
    Dernier message: 23/03/2008, 15h42
  4. Requête les plus sollicitées
    Par zut94 dans le forum MS SQL Server
    Réponses: 3
    Dernier message: 23/03/2008, 15h42
  5. Savoir quelle sont les requêtes les plus utilisées ?
    Par tchoumak dans le forum Requêtes
    Réponses: 1
    Dernier message: 29/06/2006, 16h45

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo