Freigeben über

  • Artikel

Abfrageaktivitäts-Sammlertyp

Der Abfrageaktivitäts-Sammlertyp ist ein benutzerdefinierter Sammlertyp, der von dem Sammlungssatz für die Abfragestatistik, einem der vordefinierten Systemdaten-Sammlungssätze, verwendet wird.

Mit diesem Sammlertyp werden Abfragestatistiken und Informationen zur Abfrageaktivität zusammen mit dem Abfrageplan und dem Abfragetext für Abfragen erfasst, die vordefinierten Kriterien entsprechen. Wenn Sie mit diesem Sammlertyp einen eigenen Sammlungssatz erstellen, erfasst dieser dieselben Informationen wie der Sammlungssatz für die Abfragestatistik. Deshalb empfiehlt es sich, dass Sie den vordefinierten Sammlungssatzsatz für Abfragestatistiken verwenden.

Architektur und Verarbeitung

Der Abfrageaktivitäts-Sammlertyp besteht aus den folgenden Elementen:

  • Einem Schema für Eingabeparameter (intern)

  • Einem SSIS-Paket zum Erfassen von Daten (QueryActivityCollect.dtsx)

  • Einem SSIS-Paket zum Hochladen von Daten (QueryActivityUpload.dtsx)

Zusätzlich verwendet dieser Sammlertyp benutzerdefinierte Tasks oder Transformationen zum Analysieren und Auswählen von Daten, die im Verwaltungs-Data Warehouse gespeichert werden sollen.

Der Abfrageaktivitäts-Sammlertyp führt die folgenden Vorgänge aus:

  • Er listet Beispiele der dynamischen Verwaltungssichten dm_exec_requests und dm_exec_sessions sowie ausgewählter verwandter dynamischer Verwaltungssichten auf. Dies wird als einzelne verknüpfte Abfrage implementiert. Die Datensammlung erfolgt mit der für das Sammelelement angegebenen Sammlungshäufigkeit.

  • Er erfasst bei jedem Hochladen der Sammlungssätze Momentaufnahmen der dynamischen Verwaltungssicht dm_exec_query_stats. In der Standardeinstellung wird der Sammlungssatz für Abfragestatistiken alle 15 Minuten hochgeladen.

Sammlungsphase

In der folgenden Tabelle wird die Abfrage angezeigt, die während der Sammlungsphase verwendet wird. Diese Abfrage wird im SSIS-Paket QueryActivityCollect.dtsx definiert.

Sammlungshäufigkeit

10 Sekunden

Abfrage

 
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)

Uploadphase

Während der Uploadphase werden die aufgelisteten Daten analysiert, um zu bestimmen, welche Daten im Verwaltungs-Data Warehouse gespeichert werden sollen. Anhand dieser Analyse werden ein Satz von Abfragestatistiken, Abfragepläne und ein Abfragetext bestimmt, die auf Grundlage der folgenden Kriterien gespeichert werden müssen:

Ein Schlüsselelement ist ein Algorithmus, der auswählt, welche Abfragen und Abfragepläne im Data Warehouse gespeichert werden sollen. Dieser Algorithmus funktioniert wie folgt:

  1. Er erfasst eine Momentaufnahme von sys.dm_exec_query_stats. Diese Momentaufnahme wird bei jedem Upload der Sammlungssätze erfasst. (In der Standardeinstellung ist dies alle 15 Minuten.)

  2. Er ruft die zuletzt (vor 15 Minuten) erstellte Momentaufnahme zum Vergleich mit der neuen Momentaufnahme ab. Die zuletzt entstandene Momentaufnahme wird lokal zwischengespeichert und muss nicht aus dem Verwaltungs-Data Warehouse abgerufen werden.

  3. Er wählt die obersten drei Abfragen jeder Momentaufnahme mit der folgenden Metrik aus:

    • Verstrichene Zeit

    • Arbeitszeit

    • Logische Lesevorgänge

    • Logische Schreibvorgänge

    • Physische Lesevorgänge

    • Ausführungsanzahl

    Dieser Prozess stellt 6 x 3 sql_handles und plan_handles bereit.

  4. Er identifiziert die eindeutigen sql_handles und plan_handles.

  5. Er erstellt einen Durchschnitt zwischen diesem Ergebnis und den im Data Warehouse gespeicherten sql_handles und plan_handles.

    Er ruft den Plan und den Text für neue sql_handles und plan_handles vom Server ab. Wird der Plan oder der Text nicht gefunden (da er womöglich bereits aus dem lokalen Cache entfernt wurde), speichert er die Handles im Verwaltungs-Data Warehouse.

  6. Er normalisiert den Text jedes erfassten sql_handle-Texts (beispielsweise entfernt er Parameter und Literale), und er berechnet den eindeutigen Hashwert für den normalisierten Text. Er speichert den normalisierten Text, den Hashwert und die Zuordnung zum ursprünglichen sql_handle im Verwaltungs-Data Warehouse.

In der folgenden Tabelle ist die Abfrage dargestellt, die dazu verwendet wird, die Momentaufnahmen zu erhalten und die Daten im Verwaltungs-Data Warehouse zu analysieren und anschließend hochzuladen. Diese Abfrage wird im SSIS-Paket QueryActivityUpload.dtsx definiert.

Abfrage

 
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

Abfrageausgabe

snapshots.query_stats, snapshots.notable_query_text und snapshots.notable_query_plan