Udostępnij za pośrednictwem

  • Artykuł

Query Activity Collector Type

typ modułu zbierającego dane dane działania kwerendy jest to typ niestandardowy moduł zbierający używane przez kwerendy statystyki zestaw kolekcja, jedną z wstępnie zdefiniowanych systemu danych zestaw kolekcja s.

Ten typ modułu zbierającego dane dane jest używany do gromadzenia statystyki kwerendy i zbadać informacji o aktywności wraz z planu kwerend i tekst kwerendy dla kwerend, które spełniają kryteria wstępnie zdefiniowane.Jeśli utworzysz własny zestaw kolekcji przy użyciu tego typu modułu zbierającego, będzie zbierać te same informacje, jak statystyki Query zestaw kolekcji.Dlatego zaleca się używanie wstępnie zdefiniowanych statystyki Query zestaw kolekcji.

Architektura i przetwarzania

Działanie kwerendy typ modułu zbierającego dane składa się z następujących elementów:

  • Parametry wejściowe schematu (wewnętrzne)

  • An SSIS pakiet for collecting data (QueryActivityCollect.dtsx)

  • An SSIS pakiet for uploading data (QueryActivityUpload.dtsx)

Ponadto tego typ modułu zbierającego dane wykorzystuje niestandardowe zadania lub przekształca do analizowania i wybieranie danych do przechowywania w magazynie danych zarządzania.

Typ działania kwerendy zbierającego wykonuje następujące operacje:

  • Zbieranie próbek dm_exec_requests dm_exec_sessions, a zaznaczone inne widoki powiązane dynamicznego zarządzania.To jest zaimplementowany jako jedną kwerendę sprzężone.Zbieranie danych odbywa się z częstotliwością określoną dla zapasu kolekcja.

  • To gromadzi migawek dynamiczny widok zarządzania dm_exec_query_stats z częstotliwością równa zestaw kolekcja przekazać częstotliwości.Domyślnie zestaw kolekcja Query statystyka ma częstotliwość wysyłania 15 minut.

Fazy zbierania

W poniższej tabela przedstawiono kwerendę, która jest używana w czasie kolekcja fazy.Ta kwerenda jest zdefiniowany w QueryActivityCollect.dtsx SSIS pakiet.

Częstotliwość kolekcja

10 sekund

Query

 
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)

Przekaż fazy

Podczas fazy przekazywania zebrane dane jest analizować je w celu ustalenia, jakie dane zostaną zapisane w magazyn danych zarządzania.Taka analiza określa zestaw kwerendy statystyki, planów kwerend i tekst kwerendy, która musi zostać zapisany.

Kluczowym elementem jest algorytm, który wybiera, które kwerendy i planów kwerend, aby zapisać w magazyn danych.Ten algorytm działa w następujący sposób:

  1. Pobiera migawkę sys.dm_exec_query_stats.To migawka zbierane są przy częstotliwości równa częstotliwość przekazywania kolekcja zestaw.(Domyślnie jest to 15 minut).

  2. Pobiera najbardziej aktualne migawkę (od 15 minut wcześniej) dla porównania z nowym migawka.Najnowsze migawka są buforowane lokalnie, a nie musi być pobrana z magazyn danych zarządzania.

  3. Wybiera górny trzech kwerend z każdego migawka za pomocą następujących metryk:

    • Czas

    • Pracownik czas

    • Odczyty logicznych

    • Zapisywanie logiczne

    • Operacje odczytu fizycznego

    • Licznik wykonanie

    Ten proces zapewnia sql_handles i plan_handles 6 x 3.

  4. Identyfikuje unikatowy sql_handles i plan_handles.

  5. Ten wynik z sql_handles i plan_handles, które są przechowywane w przecina magazyn danych.

    Dla nowych sql_handles i plan_handles uzyskuje plan i tekst z serwera.Jeśli nie można odnaleźć planu lub tekst (go zostały już mógł być usunięte z lokalnej pamięci podręcznej), przechowuje uchwyty w magazyn danych zarządzania.

  6. Dla każdego sql_handle tekst zebrane, normalizuje tekstu (na przykład usuwa parametrów, jak i literałów) i oblicza wartość mieszania unikatowy znormalizowana tekstu.Przechowuje znormalizowana tekst, wartości mieszania i mapowanie do oryginalnego sql_handle w magazyn danych zarządzania.

W poniższej tabela przedstawiono kwerendę, która jest używana do uzyskania migawki oraz do analizowania, a następnie przekazać dane do magazyn danych zarządzania.Ta kwerenda jest zdefiniowany w QueryActivityUpload.dtsx SSIS pakiet.

Query

 
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

Dane wyjściowe kwerendy

snapshots.query_stats, snapshots.notable_query_text, and snapshots.notable_query_plan

Historia zmian

Microsoft Learning

Dodano sekcje "Fazy zbierania" i "Przekaż fazy".

Zastępuje kryteriów, w odniesieniu do którego dane zostaną zapisane w magazynie danych zarządzania podczas fazy przekazywania z kwerendy, która jest zdefiniowana w QueryActivityUpload.dtsx SSIS pakiet.