• Article

Activité des requêtes, type de collecteur

Le type de collecteur Activité des requêtes est un type de collecteur personnalisé utilisé par l'un des jeux d'éléments de collecte de données prédéfinis, Statistiques sur les requêtes.

Ce type de collecteur est utilisé pour collecter, sur des requêtes répondant à des critères prédéfinis, des statistiques et des informations sur leur activité, ainsi que le plan et le texte de requête. Si vous créez votre propre jeu d'éléments de collecte à l'aide de ce type de collecteur, les informations collectées seront les mêmes qu'avec Statistiques sur les requêtes. Nous vous recommandons par conséquent d'utiliser le jeu d'éléments de collecte Statistiques sur les requêtes prédéfini.

Architecture et traitement

Le type de collecteur Activité des requêtes est constitué des éléments suivants :

  • schéma des paramètres d'entrée (interne) ;

  • package SSIS pour recueillir les données (QueryActivityCollect.dtsx) ;

  • package SSIS pour télécharger les données (QueryActivityUpload.dtsx).

Ce type de collecteur utilise en outre des tâches ou des transformations personnalisées pour analyser et sélectionner des données en vue de leur stockage dans l'entrepôt de données de gestion.

Le type de collecteur Activité des requêtes effectue les opérations décrites ci-dessous.

  • Il collecte des échantillons de dm_exec_requests, dm_exec_sessions et une sélection d'autres vues de gestion dynamique associées. Cette collecte de données est implémentée sous forme de requête jointe unique. La collecte de données se produit selon la fréquence spécifiée pour l'élément de collecte.

  • Il collecte les captures instantanées de la vue de gestion dynamique dm_exec_query_stats selon une fréquence identique à la fréquence de téléchargement du jeu d'éléments de collecte. Par défaut, la fréquence de téléchargement du jeu d'éléments de collecte Statistiques sur les requêtes est de 15 minutes.

Phase de collecte

Le tableau suivant montre la requête utilisée pendant la phase de collecte. Cette requête est définie dans le package SSIS QueryActivityCollect.dtsx.

Fréquence de collecte

10 secondes

Requête

 
SET NOCOUNT ON
-- Get the collection time as UTC time
DECLARE @collection_time datetime
SET @collection_time = GETDATE()
SELECT
CONVERT(int, ROW_NUMBER() OVER (ORDER BY sess.session_id, ISNULL (req.request_id, -1), ISNULL (tasks.exec_context_id, -1)) ) AS row_id,
-- IDs and Blocking IDs
sess.session_id, 
ISNULL (req.request_id, -1) AS request_id, 
ISNULL (tasks.exec_context_id, -1) AS exec_context_id, 
ISNULL (req.blocking_session_id, 0) AS blocking_session_id,
CONVERT (bit, CASE 
                WHEN EXISTS (SELECT TOP 1 session_id FROM sys.dm_exec_requests bl WHERE bl.blocking_session_id = req.session_id) THEN 1
                ELSE 0
              END) AS is_blocking,
ISNULL (waits.blocking_exec_context_id, 0) AS blocking_exec_context_id, 
tasks.scheduler_id, 
DB_NAME(req.database_id) as database_name, 
req.[user_id], 
-- State information
LEFT (tasks.task_state, 10) AS task_state, 
LEFT (req.status, 15) AS request_status, 
LEFT (sess.status, 15) AS session_status,
req.executing_managed_code, 
-- Session information
sess.login_time, 
sess.is_user_process, 
LEFT (ISNULL (sess.[host_name], ''), 20) AS [host_name], 
LEFT (ISNULL (sess.[program_name], ''), 50) AS [program_name], 
LEFT (ISNULL (sess.login_name, ''), 30) AS login_name, 
-- Waits information
LEFT (ISNULL (req.wait_type, ''), 45) AS wait_type, 
LEFT (ISNULL (req.last_wait_type, ''), 45) AS last_wait_type, 
ISNULL (waits.wait_duration_ms, 0) AS wait_duration_ms, 
LEFT (ISNULL (req.wait_resource, ''), 50) AS wait_resource, 
LEFT (ISNULL (waits.resource_description, ''), 140) AS resource_description,
-- Transaction information
req.transaction_id, 
ISNULL(req.open_transaction_count, 0) AS open_transaction_count,
COALESCE(req.transaction_isolation_level, sess.transaction_isolation_level) AS transaction_isolation_level,
-- Request stats
req.cpu_time AS request_cpu_time, 
req.logical_reads AS request_logical_reads, 
req.reads AS request_reads, 
req.writes AS request_writes, 
req.total_elapsed_time AS request_total_elapsed_time, 
req.start_time AS request_start_time, 
-- Session stats
sess.memory_usage, 
sess.cpu_time AS session_cpu_time, 
sess.reads AS session_reads, 
sess.writes AS session_writes, 
sess.logical_reads AS session_logical_reads, 
sess.total_scheduled_time AS session_total_scheduled_time, 
sess.total_elapsed_time AS session_total_elapsed_time, 
sess.last_request_start_time, 
sess.last_request_end_time, 
req.open_resultset_count AS open_resultsets, 
sess.row_count AS session_row_count, 
sess.prev_error, 
tasks.pending_io_count, 
-- Text/Plan handles
ISNULL (req.command, 'AWAITING COMMAND') AS command,  
req.plan_handle, 
req.sql_handle, 
req.statement_start_offset, 
req.statement_end_offset,
@collection_time AS collection_time
FROM sys.dm_exec_sessions sess 
LEFT OUTER MERGE JOIN sys.dm_exec_requests req  ON sess.session_id = req.session_id
LEFT OUTER MERGE JOIN sys.dm_os_tasks tasks ON tasks.session_id = sess.session_id AND tasks.request_id = req.request_id AND tasks.task_address = req.task_address
LEFT OUTER MERGE JOIN sys.dm_os_waiting_tasks waits ON waits.session_id = sess.session_id AND waits.waiting_task_address = req.task_address
WHERE 
    sess.session_id <> @@SPID
    AND
    (
        (req.session_id IS NOT NULL AND (sess.is_user_process = 1 OR req.status COLLATE Latin1_General_BIN NOT IN ('background', 'sleeping')))-- active request
            OR 
        (sess.session_id IN (SELECT DISTINCT blocking_session_id FROM sys.dm_exec_requests WHERE blocking_session_id != 0))            -- not active, but head blocker
    )
OPTION (FORCE ORDER)

Phase de téléchargement

Pendant la phase de téléchargement, les données collectées sont analysées afin d'identifier celles qui seront enregistrées dans l'entrepôt de données de gestion. Cette analyse détermine un jeu de statistiques sur les requêtes, de plans de requête et de texte de requête qui doit être enregistré.

Un élément clé de ce processus est l'algorithme qui sélectionne les requêtes et les plans de requête à enregistrer dans l'entrepôt de données. Cet algorithme fonctionne comme suit :

  1. Il collecte une capture instantanée de sys.dm_exec_query_stats. Cette capture instantanée est recueillie selon une fréquence identique à la fréquence de téléchargement du jeu d'éléments de collecte. (Par défaut, celle-ci est de 15 minutes.)

  2. Il récupère la capture instantanée la plus récente (de 15 minutes plus tôt) pour la comparer à la nouvelle. La capture instantanée la plus récente est mise en cache localement et n'a pas à être récupérée à partir de l'entrepôt de données de gestion.

  3. Il sélectionne les trois requêtes principales de chaque capture instantanée sur la base des mesures suivantes :

    • Temps écoulé

    • Temps de travail

    • Lectures logiques

    • Écritures logiques

    • Lectures physiques

    • Nombre d'exécutions

    Ce processus fournit 6 x 3 sql_handles et plan_handles.

  4. Il identifie les sql_handles et plan_handles uniques.

  5. Il recoupe ce résultat avec les sql_handles et plan_handles stockés dans l'entrepôt de données.

    Pour les nouveaux sql_handles et plan_handles, il obtient le plan et le texte à partir du serveur. Si le plan ou le texte est introuvable (il peut avoir déjà été supprimé du cache local), il stocke les handles dans l'entrepôt de données de gestion.

  6. Pour chaque texte de sql_handle recueilli, il normalise le texte (par exemple, il supprime les paramètres et les littéraux) et calcule la valeur de hachage unique pour le texte normalisé. Il stocke le texte normalisé, la valeur de hachage et le mappage au sql_handle d'origine dans l'entrepôt de données de gestion.

Le tableau suivant montre la requête utilisée pour obtenir les captures instantanées et analyser les données, puis les télécharger vers l'entrepôt de données de gestion. Cette requête est définie dans le package SSIS QueryActivityUpload.dtsx.

Requête

 
SET NOCOUNT ON
DECLARE @p1 datetime
SET @p1 = GETDATE()
SELECT 
    [sql_handle],
    statement_start_offset,
    statement_end_offset,
    -- Use ISNULL here and in other columns to handle in-progress queries that are not yet in sys.dm_exec_query_stats.  
    -- These values only come from sys.dm_exec_query_stats. If the plan does not show up in sys.dm_exec_query_stats 
    -- (first execution of a still-in-progress query, visible in sys.dm_exec_requests), these values will be NULL. 
    MAX (plan_generation_num) AS plan_generation_num,
    plan_handle,
    MIN (creation_time) AS creation_time, 
    MAX (last_execution_time) AS last_execution_time,
    SUM (execution_count) AS execution_count,
    SUM (total_worker_time) AS total_worker_time,
    MIN (min_worker_time) AS min_worker_time,           -- NULLable
    MAX (max_worker_time) AS max_worker_time,
    SUM (total_physical_reads) AS total_physical_reads,
    MIN (min_physical_reads) AS min_physical_reads,     -- NULLable
    MAX (max_physical_reads) AS max_physical_reads,
    SUM (total_logical_writes) AS total_logical_writes,
    MIN (min_logical_writes) AS min_logical_writes,     -- NULLable
    MAX (max_logical_writes) AS max_logical_writes,
    SUM (total_logical_reads) AS total_logical_reads,
    MIN (min_logical_reads) AS min_logical_reads,       -- NULLable
    MAX (max_logical_reads) AS max_logical_reads,
    SUM (total_clr_time) AS total_clr_time,
    MIN (min_clr_time) AS min_clr_time,                 -- NULLable
    MAX (max_clr_time) AS max_clr_time,
    SUM (total_elapsed_time) AS total_elapsed_time,
    MIN (min_elapsed_time) AS min_elapsed_time,         -- NULLable
    MAX (max_elapsed_time) AS max_elapsed_time,
    @p1 AS collection_time
FROM
(
    SELECT  
        [sql_handle],
        statement_start_offset,
        statement_end_offset,
        plan_generation_num,
        plan_handle,
        creation_time,
        last_execution_time,
        execution_count,
        total_worker_time,
        min_worker_time,
        max_worker_time,
        total_physical_reads,
        min_physical_reads,
        max_physical_reads,
        total_logical_writes,
        min_logical_writes,
        max_logical_writes,
        total_logical_reads,
        min_logical_reads,
        max_logical_reads,
        total_clr_time,
        min_clr_time,
        max_clr_time,
        total_elapsed_time,
        min_elapsed_time,
        max_elapsed_time 
    FROM sys.dm_exec_query_stats AS q
    UNION ALL 
    SELECT 
        r.[sql_handle],
        r.statement_start_offset,
        r.statement_end_offset,
        ISNULL (qs.plan_generation_num, 0) AS plan_generation_num,
        r.plan_handle,
        ISNULL (qs.creation_time, r.start_time) AS creation_time,
        r.start_time AS last_execution_time,
        1 AS execution_count,
        r.cpu_time AS total_worker_time,
        qs.min_worker_time,     -- min should not be influenced by in-progress queries
        r.cpu_time AS max_worker_time,
        r.reads AS total_physical_reads,
        qs.min_physical_reads,  -- min should not be influenced by in-progress queries
        r.reads AS max_physical_reads,
        r.writes AS total_logical_writes,
        qs.min_logical_writes,  -- min should not be influenced by in-progress queries
        r.writes AS max_logical_writes,
        r.logical_reads AS total_logical_reads,
        qs.min_logical_reads,   -- min should not be influenced by in-progress queries
        r.logical_reads AS max_logical_reads,
        qs.total_clr_time,      -- CLR time is not available in dm_exec_requests
        qs.min_clr_time,        -- CLR time is not available in dm_exec_requests
        qs.max_clr_time,        -- CLR time is not available in dm_exec_requests
        r.total_elapsed_time AS total_elapsed_time,
        qs.min_elapsed_time,    -- min should not be influenced by in-progress queries
        r.total_elapsed_time AS max_elapsed_time
    FROM sys.dm_exec_requests AS r 
    LEFT OUTER JOIN sys.dm_exec_query_stats AS qs ON r.plan_handle = qs.plan_handle AND r.statement_start_offset = qs.statement_start_offset 
        AND r.statement_end_offset = qs.statement_end_offset 
    WHERE r.sql_handle IS NOT NULL 
) AS query_stats 
OUTER APPLY sys.dm_exec_sql_text (sql_handle) AS sql
GROUP BY [sql_handle], plan_handle, statement_start_offset, statement_end_offset 
ORDER BY [sql_handle], plan_handle, statement_start_offset, statement_end_offset

Sortie de la requête

snapshots.query_stats, snapshots.notable_query_text et snapshots.notable_query_plan

Historique des modifications

Mise à jour du contenu

Ajout des sections « Phase de collecte » et « Phase de téléchargement ».

Remplacement des critères concernant les données qui seront enregistrées dans l'entrepôt de données de gestion pendant la phase de téléchargement par la requête définie dans le package SSIS QueryActivityUpload.dtsx.