Artikel
09/24/2010

Abfrageaktivitäts-Auflistertyp

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

Mit diesem Auflistertyp 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 Auflistertyp einen eigenen Auflistsatz erstellen, erfasst dieser dieselben Informationen wie der Auflistsatz für die Abfragestatistik. Deshalb empfiehlt es sich, dass Sie den vordefinierten Auflistsatz für Abfragestatistiken verwenden.

Architektur und Verarbeitung

Der Abfrageaktivitäts-Auflistertyp 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 Auflistertyp benutzerdefinierte Tasks oder Transformationen zum Analysieren und Auswählen von Daten, die im Verwaltungs-Data Warehouse gespeichert werden sollen.

Der Abfrageaktivitäts-Auflistertyp 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 Datenauflistung erfolgt mit der für das Auflistelement angegebenen Auflistungshäufigkeit.
Er erfasst bei jedem Hochladen der Auflistsätze Snapshots der dynamischen Verwaltungssicht dm_exec_query_stats. In der Standardeinstellung wird der Auflistsatz für Abfragestatistiken alle 15 Minuten hochgeladen.

Auflistungsphase

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

Auflistungshä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:

Er erfasst einen Snapshot von sys.dm_exec_query_stats. Dieser Snapshot wird bei jedem Upload der Auflistsätze erfasst. (In der Standardeinstellung ist dies alle 15 Minuten.)
Er ruft den zuletzt (vor 15 Minuten) erstellten Snapshot zum Vergleich mit dem neuen Snapshot ab. Der zuletzt entstandene Snapshot wird lokal zwischengespeichert und muss nicht aus dem Verwaltungs-Data Warehouse abgerufen werden.
Er wählt die obersten drei Abfragen jedes Snapshots 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.
Er identifiziert die eindeutigen sql_handles und plan_handles.
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.
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 Snapshots 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

Änderungsverlauf

Aktualisierter Inhalt
Die Abschnitte "Auflistungsphase" und "Uploadphase" wurden hinzugefügt.
Er ersetzt die Kriterien, welche Daten im Verwaltungs-Data Warehouse während der Uploadphase gespeichert werden, mit der Abfrage, die im SSIS-Paket QueryActivityUpload.dtsx definiert wird.