Artículo
03/30/2012

Tipo de recopilador Actividad de consulta

El tipo de recopilador de Actividad de consultas es un tipo de recopilador personalizado usado por el conjunto de recopilación Estadísticas de consultas, uno de los conjuntos de recopilación de datos del sistema predefinidos.

Este tipo de recopilador se utiliza para recopilar estadísticas de consultas e información de la actividad de consultas junto con el plan de consulta y el texto de las consultas que cumplen los criterios predefinidos. Si crea un conjunto de recopilación propio mediante este tipo de recopilador, recopilará la misma información que el conjunto de recopilación Estadísticas de consultas. Por tanto, se recomienda que utilice el conjunto de recopilación Estadísticas de consultas predefinido.

Arquitectura y procesamiento

El tipo de recopilador de Actividad de consultas está compuesto de los elementos siguientes:

Un esquema de parámetros de entrada (interno)
Un paquete SSIS para recopilar los datos (QueryActivityCollect.dtsx)
Un paquete SSIS para cargar los datos (QueryActivityUpload.dtsx)

Además, este tipo de recopilador utiliza transformaciones o tareas personalizadas para analizar y seleccionar los datos que se almacenarán en el almacén de administración de datos.

El tipo de recopilador de Actividad de consultas realiza las operaciones siguientes:

Recopila ejemplos de dm_exec_requests, dm_exec_sessions y otras vistas de administración dinámica relacionadas seleccionadas. Se implementa como una sola consulta singleton. La recopilación de datos tiene lugar con la frecuencia especificada para el elemento de recopilación.
Recopila instantáneas de la vista de administración dinámica dm_exec_query_stats con una frecuencia igual a la de la carga del conjunto de recopilación. De forma predeterminada, el conjunto de recopilación Estadísticas de consultas sigue una frecuencia de carga de 15 minutos.

Fase de recopilación

En la siguiente tabla se muestra la consulta que se utiliza durante la fase de recopilación. Esta consulta se define en el paquete SSIS QueryActivityCollect.dtsx.

Frecuencia de recopilación

10 segundos

Consulta

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)

Fase de carga

Durante la fase de carga, los datos recopilados se analizan para determinar qué datos se guardarán en el almacén de administración de datos. Este análisis determina un conjunto de estadísticas de consulta, planes de consulta y texto de consulta que hay que guardar.

Un elemento clave es un algoritmo que selecciona las consultas y los planes de consulta que se deben guardar en el almacenamiento de datos. Este algoritmo realiza las operaciones siguientes:

Recopila una instantánea de sys.dm_exec_query_stats. Esta instantánea se recopila con una frecuencia igual a la de la carga del conjunto de recopilación. (De forma predeterminada, es de 15 minutos.)
Recupera la instantánea más reciente (de hace 15 minutos) para compararla con la nueva instantánea. La instantánea más reciente está almacenada localmente en memoria caché y no es necesario recuperarla del almacén de administración de datos.
Selecciona las tres consultas principales de cada instantánea mediante las mediciones siguientes:
- Tiempo transcurrido
- Tiempo de trabajo
- Lecturas lógicas
- Escrituras lógicas
- Lecturas físicas
- Total de ejecuciones
Este proceso proporciona 6 x 3 sql_handles y plan_handles.
Identifica los sql_handles y plan_handles únicos.
Calcula la intersección de este resultado con los valores de sql_handles y plan_handles que están almacenados en el almacenamiento de datos.

Para los nuevos valores de sql_handles y plan_handles, obtiene el plan y el texto del servidor. Si no se puede encontrar el plan o el texto (es posible que se hayan quitado de la memoria caché local), almacena los identificadores en el almacén de administración de datos.
Para cada texto sql_handle que se recopila, normaliza el texto (por ejemplo, quita los parámetros y los literales) y calcula el valor hash único para el texto normalizado. Almacena el texto normalizado, el valor hash y la asignación al sql_handle original en el almacén de administración de datos.

En la tabla siguiente se muestra la consulta que se usa para obtener las instantáneas así como para analizar y a continuación cargar los datos en el almacén de administración de datos. Esta consulta se define en el paquete SSIS QueryActivityUpload.dtsx.

Consulta

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

Resultado de la consulta

snapshots.query_stats, snapshots.notable_query_text y snapshots.notable_query_plan