Udostępnij za pośrednictwem

  • Artykuł

Typ zbierających działania kwerendy

Działanie kwerendy typ modułu zbierającego jest niestandardowy typ modułu zbierającego używany przez zestaw zbierania statystyk kwerendy, jedną z wstępnie zdefiniowanych systemu zbierania danych ustawia.

To typ modułu zbierającego do zbierania statystyk kwerendy i kwerendami działalności wraz z planu kwerend i tekst kwerendy dla kwerend, które spełniają kryteria wstępnie zdefiniowanych.Po utworzeniu własnego zbioru ustawiona za pomocą tej typ modułu zbierającego, gromadzone takie same informacje jak zestaw zbierania statystyk kwerendy.Dlatego zaleca się używać wstępnie zdefiniowanych statystyki kwerendy zestaw kolekcji.

Architektura i przetwarzania

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

  • Schemat parametry wejściowe (wewnętrzne)

  • SSIS Pakiet zbierania danych (QueryActivityCollect.dtsx)

  • SSIS Pakiet dla przesyłania danych (QueryActivityUpload.dtsx)

Ponadto wykorzystuje niestandardowe zadania tego typu zbierających lub transformacje do analizowania i wybieranie danych do przechowywania w magazyn danych zarządzania.

Działanie kwerendy typ modułu zbierającego wykonuje następujące operacje:

  • Próbki są zbierane przez dm_exec_requests, dm_exec_sessionsoraz wybrane inne widoki pokrewne dynamicznego zarządzania.To jest zaimplementowany jako kwerendę sprzężonych.Zbieranie danych stanie się z częstotliwością określoną dla element kolekcja.

  • Zbiera migawek dm_exec_query_stats dynamiczny widok zarządzania, z częstotliwością równa zestaw kolekcja przekazywanie częstotliwości.Domyślnie, Statystyka kwerendy zestaw kolekcji została częstotliwość przekazywania 15 minut.

Fazy zbierania

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

Częstotliwość pobierania

10 sekund

Zapytanie

 
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 zebranych danych jest analizowane w celu ustalenia, jakie dane zostaną zapisane w magazyn danych zarządzania.Analiza ta określa zestaw statystyki kwerendy, planów kwerend i tekst kwerendy, która musi zostać zapisany.

A klucz element jest algorytm wybiera, które kwerend i planów kwerend, aby zapisać w magazynie danych.Ten algorytm działa w następujący sposób:

  1. Zbiera migawka z sys.dm_exec_query_stats.Migawka są zbierane równa częstotliwością zestaw kolekcja przekazywanie częstotliwości.(Domyślnie jest to 15 minut).

  2. Pobiera ostatnio migawka (z 15 minut wcześniej) dla porównania z nowym migawka.Najnowsze migawka jest buforowany lokalnie i nie musi być pobierane z magazyn danych zarządzania.

  3. Wybiera górny trzech kwerend z każdej migawka przy użyciu następujących wskaźników:

    • Upłynięteczas

    • Pracownikczas

    • Odczyty logicznych

    • Zapisy logicznych

    • Fizyczny Odczyty

    • Licznik wykonanie

    Proces ten zawiera 6 x 3 sql_handles i plan_handles.

  4. Identyfikuje unikatowy sql_handles i plan_handles.

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

    Nowy sql_handles i plan_handles uzyskuje plan i tekst z serwera.Jeśli nie można odnaleźć planu lub tekst (go może zostały już 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 i literałów) i oblicza wartość mieszania unikatowy znormalizowana tekstu.Przechowuje znormalizowany 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 i następnie prześlij dane do magazyn danych zarządzania.Ta kwerenda jest zdefiniowany w QueryActivityUpload.dtsx SSIS pakiet.

Zapytanie

 
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

Wynik kwerendy

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