Статья
09/28/2010

Сборщик типа «Активность запросов»

«Активность запросов» — это пользовательский тип сборщика, используемый в одном из стандартных наборов сбора системных данных — «Статистика запросов».

Этот тип сборщика используется для сбора сведений о статистике и активности запросов, а также плана и текста запросов, соответствующих стандартным критериям. Если с помощью этого типа сборщика создан собственный набор сбора, он будет собирать те же данные, что и набор сбора «Статистика запросов». Поэтому рекомендуется использовать стандартный набор сбора «Статистика запросов».

Архитектура и обработка

Тип сборщика «Активность запросов» состоит из следующих элементов.

Схема входных параметров (внутренняя)
Пакет служб SSIS для сбора данных (QueryActivityCollect.dtsx)
Пакет служб SSIS для передачи данных (QueryActivityUpload.dtsx)

Кроме того, этот тип сборщика использует пользовательские задачи или преобразования для анализа и выбора данных, направляемых в хранилище данных управления.

Тип сборщика «Активность запросов» выполняет следующие операции.

Собирает образцы dm_exec_requests, dm_exec_sessions и других выбранных связанных динамических административных представлений. Это реализовано в едином запросе. Сбор данных производится с частотой, указанной для элемента сбора.
Собирает моментальные снимки динамического административного представления dm_exec_query_stats с частотой, равной частоте передачи набора сбора. По умолчанию частота передачи набора сбора «Статистика запросов» составляет 15 минут.

Стадия сбора

В следующей таблице изображен запрос, используемый на стадии сбора. Этот запрос определяется в пакете QueryActivityCollect.dtsx служб SSIS.

Частота сбора

10 секунд

Запрос

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)

Стадия передачи

На этапе передачи собранные данные анализируются, чтобы определить данные, которые будут сохранены в хранилище данных управления. Этот анализ определяет набор статистики запросов, планов запросов и их текстов, которые необходимо сохранить.

Ключевым элементом является алгоритм, который выбирает запросы и планы запросов для сохранения в хранилище данных. Ниже перечислены этапы работы этого алгоритма.

Собирает моментальный снимок представления sys.dm_exec_query_stats. Сбор этого моментального снимка выполняется с частотой, равной частоте передачи набора сбора. (По умолчанию это значение равно 15 минутам.)
Извлекает последний моментальный снимок (полученный 15 минутами ранее) для сравнения с новым моментальным снимком. Последний моментальный снимок помещается в локальный кэш и его не обязательно извлекать из хранилища управляющих данных.
Выбирает три верхних запроса из каждого моментального снимка по следующим метрикам.
- Истекшее время
- Рабочее время
- Число логических операций чтения
- Число логических операций записи
- Число физических операций записи
- Число выполнений
При этом получается 6 x 3 дескрипторов sql_handle и plan_handle.
Определяет уникальные дескрипторы sql_handle и plan_handle.
Выполняет операцию пересечения этого результата со значениями sql_handle и plan_handle, которые хранятся в хранилище данных.

Получает с сервера план и текст, соответствующие новым дескрипторам sql_handle и plan_handle. Если план или текст не удается найти (они могут быть уже удалены из локального кэша), сохраните дескрипторы в хранилище данных управления.
Для каждого собранного текста sql_handle нормализует текст (например, удаляет параметры и литералы) и вычисляет уникальное хэш-значение для нормализованного текста. Сохраняет нормализованный текст, хэш-значение и сопоставление с исходным дескриптором sql_handle в хранилище данных управления.

В следующей таблице показан запрос, используемый для получения моментальных снимков и их анализа, а также для последующей передачи данных в хранилище данных управления. Этот запрос определяется в пакете QueryActivityUpload.dtsx служб SSIS.

Запрос

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

Вывод запроса

snapshots.query_stats, snapshots.notable_query_text и snapshots.notable_query_plan

Журнал изменений

Обновления
Добавлены разделы «Стадия сбора» и «Стадия передачи».
Заменены критериев, в соответствии с которыми данные будут сохраняться в хранилище данных управления на стадии передачи с запросом, определенным в пакете QueryActivityUpload.dtsx служб SSIS.