Freigeben über

  • Artikel

Systemdaten-Sammlungssätze

Der Datensammler installiert während des SQL Server 2008-Setupprozesses drei Systemdaten-Sammlungssätze. Diese Sammlungssätze können so konfiguriert werden, dass sie Ihren Überwachungsanforderungen entsprechen. Sie können aber nicht gelöscht werden. Die Systemdaten-Sammlungssätze bestehen aus folgenden Teilen:

  • Datenträgerverwendung. Sammelt Daten über die Datenträger- und Protokollverwendung für alle auf dem System installierten Datenbanken.

  • Serveraktivität. Erfasst die Ressourcenverwendungsstatistik und die Leistungsdaten von Server und SQL Server.

  • Abfragestatistik. Erfasst Abfragestatistiken, einzelnen Abfragetext, Abfragepläne und bestimmte Abfragen.

Sammlungssatz für Datenträgerverwendung

Der Sammlungssatz für die Datenträgerverwendung verfolgt die Vergrößerung der Datenbank- und Protokolldateien und bietet dateibezogene Statistiken, wie die durchschnittliche Vergrößerung (in Megabyte) pro Tag.

Der Sammlungssatz verfügt über die beiden Sammelelemente Datenträgerverwendung - Datendateien und Datenträgerverwendung - Protokolldateien. Beide verwenden den generischen T-SQL-Abfragesammlertyp. Der Sammlungssatz erfasst die folgenden Daten:

  • Snapshots mit Datendateigrößen, die von den Sichten sys.partitions und sys.allocation_units stammen.

  • Snapshots mit Protokolldateigrößen, die von dem Befehl DBCC SQLPERF (LOGSPACE) stammen.

  • Snapshots der E/A-Statistik von der Funktion sys.dm_io_virtual_file_stats.

Die folgenden Tabellen enthalten ausführliche Informationen über den Sammlungssatz für die Datenträgerverwendung und seine Sammelelemente.

Sammlungssatzname

Datenträgerverwendung

Sammlungsmodus

Nicht zwischengespeichert

Häufigkeit für den Hochladezeitplan

Alle 6 Stunden

Datenbeibehaltung

730 Tage

Sammelelemente

Datenträgerverwendung - Datendateien

Datenträgerverwendung - Protokolldateien

Sammelelementname

Datenträgerverwendung - Datendateien

Sammlertyp

Generische T-SQL-Abfrage

Abfrage 1

 
SELECT @dbsize = SUM(convert(bigint,case when type = 0 then size else 0 end)) 
      ,@logsize = SUM(convert(bigint,case when type = 1 then size else 0 end)) 
      ,@ftsize = SUM(convert(bigint,case when type = 4 then size else 0 end)) 
FROM sys.database_files
SELECT @reservedpages = SUM(a.total_pages) 
       ,@usedpages = SUM(a.used_pages) 
       ,@pages = SUM(CASE 
                        WHEN it.internal_type IN (202,204) THEN 0 
                        WHEN a.type != 1 THEN a.used_pages 
                        WHEN p.index_id < 2 THEN a.data_pages 
                        ELSE 0 
                     END) 
FROM sys.partitions p  
JOIN sys.allocation_units a ON p.partition_id = a.container_id 
LEFT JOIN sys.internal_tables it ON p.object_id = it.object_id 
SELECT 
        @dbsize as ''dbsize'',
        @logsize as ''logsize'',
        @ftsize as ''ftsize'',
        @reservedpages as ''reservedpages'',
        @usedpages as ''usedpages'',
        @pages as ''pages''

Ausgabe Abfrage 1

disk_usage

Sammelelementname

Datenträgerverwendung - Protokolldateien

Sammlertyp

Generische T-SQL-Abfrage

Abfrage 1

 
INSERT INTO @tran_log_space_usage 
EXEC(''DBCC SQLPERF (LOGSPACE) WITH NO_INFOMSGS'');
SELECT 
    database_name,
    log_size_mb,
    log_space_used,
    status    
FROM @tran_log_space_usage

Ausgabe Abfrage 1

log_usage

Sammlungssatz für Serveraktivität

Der Sammlungssatz für die Serveraktivität bietet eine Übersicht über die SQL Server-Aktivität, die SQL Server-Ressourcennutzung und die SQL Server-Ressourcenkonflikte. Der Sammlungssatz stellt außerdem eine gekapselte Sicht der gesamten Systemressourcennutzung bereit, über die Sie feststellen können, ob Leistungsprobleme mit Aktivitäten außerhalb des SQL Server-Bereichs in Verbindung stehen.

Dieser Sammlungssatz sammelt Datenbeispiele der folgenden dynamischen Verwaltungssichten:

  • sys.dm_os_wait_stats

  • sys.dm_os_latch_stats

  • sys.dm_os_schedulers

  • sys.dm_exec_sessions, sys.dm_exec_requests, sys.dm_os_waiting_tasks (unter Verwendung einer verknüpften Abfrage)

  • sys.dm_os_process_memory

  • sys.dm_os_memory_nodes

Darüber hinaus werden Datenbeispiele von mehreren System- und SQL Server-Leistungsindikatoren gesammelt.

Der Sammlungssatz für die Serveraktivität bietet hinsichtlich der Ressourcennutzung und der Ressourcenengpässe eine allgemeine Übersicht über das System. Die Ressourcenverwendung wird in vier allgemeinen Bereichen nachverfolgt: CPU, Datenträger-E/A, Arbeitsspeicher und Netzwerk. Die Stichproben von sys.dm_exec_sessions, sys.dm_exec_requests und sys.dm_os_waiting_tasks ermöglichen die Korrelation der Systemaktivität mit Ressourcenengpässen und Blockierungsproblemen.

Selbständig ausgeführt können Sie mit diesem Sammlungssatz einer blockierten Sitzung Ressourcenengpässe zuordnen und Blockierungsketten auf Sitzungsebene anzeigen. Obwohl keine Abfragetexte erfasst werden, können Sie mit den sql_handle- und den plan_handle-Informationen, die mit dem Sammlungssatz für die Abfragestatistik erfasst werden, einen Drilldown unter die Sitzungsebene ausführen.

Die folgenden Tabellen stellen ausführliche Informationen über den Sammlungssatz für die Serveraktivität und seine Sammelelemente bereit.

Sammlungssatzname

Serveraktivität

Sammlungsmodus

Zwischengespeichert

Häufigkeit für den Hochladezeitplan

Alle 15 Minuten

Datenbeibehaltung

14 Tage

Sammelelemente

Serveraktivität - DMV-Momentaufnahmen

Serveraktivität - Leistungsindikatoren

Sammelelementname

Serveraktivität - DMV-Snapshots

Sammlertyp

Generische T-SQL-Abfrage

Sammlungshäufigkeit

60 Sekunden

Abfrage 1

 
SELECT 
    LEFT (wait_type, 45) AS wait_type, 
    SUM (waiting_tasks_count) AS waiting_tasks_count, 
    SUM (wait_time_ms) AS wait_time_ms, 
    SUM (signal_wait_time_ms) AS signal_wait_time_ms
FROM
 (SELECT 
    LEFT (wait_type, 45) AS wait_type, 
    waiting_tasks_count, 
    wait_time_ms,  
    signal_wait_time_ms
FROM sys.dm_os_wait_stats 
WHERE waiting_tasks_count > 0 OR wait_time_ms > 0 OR signal_wait_time_ms > 0
UNION ALL 
    SELECT 
        LEFT (wait_type, 45) AS wait_type, 
        1 AS waiting_tasks_count, 
        wait_duration_ms AS wait_time_ms, 
        0 AS signal_wait_time_ms
    FROM sys.dm_os_waiting_tasks
    WHERE wait_duration_ms > 60000
) AS merged_wait_stats
GROUP BY wait_type

Ausgabe Abfrage 1

snapshots.os_wait_stats

Abfrage 2

 
SELECT 
  LEFT(latch_class,45) as latch_class,
  waiting_requests_count,
  wait_time_ms
FROM sys.dm_os_latch_stats 
WHERE waiting_requests_count > 0 OR wait_time_ms > 0

Ausgabe Abfrage 2

snapshots.os_latch_stats

Abfrage 3

 
SELECT 
    pm.physical_memory_in_use_kb            AS sql_physical_memory_in_use_kb, 
    pm.large_page_allocations_kb            AS sql_large_page_allocations_kb, 
    pm.locked_page_allocations_kb           AS sql_locked_page_allocations_kb, 
    pm.total_virtual_address_space_kb       AS sql_total_virtual_address_space_kb, 
    pm.virtual_address_space_reserved_kb    AS sql_virtual_address_space_reserved_kb, 
    pm.virtual_address_space_committed_kb   AS sql_virtual_address_space_committed_kb, 
    pm.virtual_address_space_available_kb   AS sql_virtual_address_space_available_kb, 
    pm.page_fault_count                     AS sql_page_fault_count, 
    pm.memory_utilization_percentage        AS sql_memory_utilization_percentage, 
    pm.available_commit_limit_kb            AS sql_available_commit_limit_kb, 
    pm.process_physical_memory_low          AS sql_process_physical_memory_low, 
    pm.process_virtual_memory_low           AS sql_process_virtual_memory_low, 
    
    sm.total_physical_memory_kb             AS system_total_physical_memory_kb, 
    sm.available_physical_memory_kb         AS system_available_physical_memory_kb, 
    sm.total_page_file_kb                   AS system_total_page_file_kb, 
    sm.available_page_file_kb               AS system_available_page_file_kb, 
    sm.system_cache_kb                      AS system_cache_kb, 
    sm.kernel_paged_pool_kb                 AS system_kernel_paged_pool_kb, 
    sm.kernel_nonpaged_pool_kb              AS system_kernel_nonpaged_pool_kb, 
    sm.system_high_memory_signal_state      AS system_high_memory_signal_state, 
    sm.system_low_memory_signal_state       AS system_low_memory_signal_state, 
    
    si.bpool_commit_target                  AS bpool_commit_target, 
    si.bpool_committed                      AS bpool_committed, 
    si.bpool_visible                        AS bpool_visible
FROM sys.dm_os_process_memory AS pm
CROSS JOIN sys.dm_os_sys_memory AS sm   -- single-row DMV
CROSS JOIN sys.dm_os_sys_info AS si;    -- single-row DMV

Ausgabe Abfrage 3

snapshots.sql_process_and_system_memory

Abfrage 4

 
SELECT 
    memory_node_id, 
    virtual_address_space_reserved_kb, 
    virtual_address_space_committed_kb, 
    locked_page_allocations_kb, 
    single_pages_kb, 
    multi_pages_kb, 
    shared_memory_reserved_kb, 
    shared_memory_committed_kb
FROM sys.dm_os_memory_nodes

Ausgabe Abfrage 4

snapshots.os_memory_nodes

Abfrage 5

 
SELECT 
    type,
    memory_node_id as memory_node_id,
    SUM(single_pages_kb) as single_pages_kb,
    SUM(multi_pages_kb) as multi_pages_kb,
    SUM(virtual_memory_reserved_kb) as virtual_memory_reserved_kb,
    SUM(virtual_memory_committed_kb) as virtual_memory_committed_kb,
    SUM(awe_allocated_kb) as awe_allocated_kb,
    SUM(shared_memory_reserved_kb) as shared_memory_reserved_kb,
    SUM(shared_memory_committed_kb) as shared_memory_committed_kb
FROM sys.dm_os_memory_clerks
GROUP BY type, memory_node_id

Ausgabe Abfrage 5

snapshots.os_memory_clerks

Abfrage 6

 
SELECT 
    [parent_node_id],
    [scheduler_id],
    [cpu_id],
    [status],
    [is_idle],
    [preemptive_switches_count],
    [context_switches_count],
    [yield_count],
    [current_tasks_count],
    [runnable_tasks_count],
    [work_queue_count],
    [pending_disk_io_count]
FROM sys.dm_os_schedulers
WHERE scheduler_id < 128

Ausgabe Abfrage 6

snapshots.os_schedulers

Abfrage 7

 
SELECT 
    DB_NAME (f.database_id) AS database_name, f.database_id, f.name AS logical_file_name, f.[file_id], f.type_desc, 
    CAST (CASE 
        -- Handle UNC paths (e.g. ''\\fileserver\readonlydbs\dept_dw.ndf'' --&gt; ''\\fileserver\readonlydbs'')
        WHEN LEFT (LTRIM (f.physical_name), 2) = ''\\'' 
            THEN LEFT (LTRIM (f.physical_name), 
            CHARINDEX (''\'', 
            LTRIM (f.physical_name), 
            CHARINDEX (''\'', 
            LTRIM (f.physical_name), 3) + 1) - 1)
        -- Handle local paths (e.g. ''C:\Program Files\...\master.mdf'' --&gt; ''C:'') 
        WHEN CHARINDEX (''\'', LTRIM(f.physical_name), 3) &gt; 0 
            THEN UPPER (LEFT (LTRIM (f.physical_name), CHARINDEX (''\'', LTRIM (f.physical_name), 3) - 1))
        ELSE f.physical_name
    END AS nvarchar(255)) AS logical_disk, 
    fs.num_of_reads, fs.num_of_bytes_read, fs.io_stall_read_ms, fs.num_of_writes, fs.num_of_bytes_written, 
    fs.io_stall_write_ms, fs.size_on_disk_bytes
FROM sys.dm_io_virtual_file_stats (default, default) AS fs
INNER JOIN sys.master_files AS f ON fs.database_id = f.database_id AND fs.[file_id] = f.[file_id]

Ausgabe Abfrage 7

snapshots.io_virtual_file_stats

Sammelelementname

Serveraktivität - Leistungsindikatoren

Sammlertyp

Leistungsindikatoren

Sammlungshäufigkeit

60 Sekunden

Verwendete Leistungsindikatoren

"Memory" Counters="% Committed Bytes In Use"

"Memory" Counters="Available Bytes"

"Memory" Counters="Cache Bytes"

"Memory" Counters="Cache Faults/sec"

"Memory" Counters="Committed Bytes"

"Memory" Counters="Free &amp; Zero Page List Bytes"

"Memory" Counters="Modified Page List Bytes"

"Memory" Counters="Pages/sec"

"Memory" Counters="Page Reads/sec"

"Memory" Counters="Page Write/sec"

"Memory" Counters="Page Faults/sec"

"Memory" Counters="Pool Nonpaged Bytes"

"Memory" Counters="Pool Paged Bytes"

"Memory" Counters="Standby Cache Core Bytes"

"Memory" Counters="Standby Cache Normal Priority Bytes"

"Memory" Counters="Standby Cache Reserve Bytes"

"Memory" Counters="Pool Paged Bytes"

"Memory" Counters="Write Copies/sec"

"Process" Counters="*" Instances="_Total"

"Process" Counters="*" Instances="$(TARGETPROCESS)"

"Process" Counters="Thread Count" Instances="*"

"Process" Counters="% Processor Time" Instances="*"

"Process" Counters="IO Read Bytes/sec" Instances="*"

"Process" Counters="IO Write Bytes/sec" Instances="*"

"Process" Counters="Private Bytes" Instances="*"

"Process" Counters="Working Set" Instances="*"

"Processor" Counters="% Processor Time" Instances="*"

"Processor" Counters="% User Time" Instances="*"

"Processor" Counters="% Privileged Time" Instances="*"

"Server Work Queues" Counters="Queue Length" Instances="*"

"LogicalDisk" Counters="% Disk Time" Instances="*"

"LogicalDisk" Counters="Avg. Disk Queue Length" Instances="*"

"LogicalDisk" Counters="Avg. Disk Read Queue Length" Instances="*"

"LogicalDisk" Counters="Avg. Disk Write Queue Length" Instances="*"

"LogicalDisk" Counters="Avg. Disk sec/Read" Instances="*"

"LogicalDisk" Counters="Avg. Disk sec/Write" Instances="*"

"LogicalDisk" Counters="Avg. Disk sec/Transfer" Instances="*"

"LogicalDisk" Counters="Disk Reads/sec" Instances="*"

"LogicalDisk" Counters="Disk Bytes/sec" Instances="*"

"LogicalDisk" Counters="Disk Writes/sec" Instances="*"

"LogicalDisk" Counters="Split IO/sec" Instances="*"

"System" Counters="Processor Queue Length"

"System" Counters="File Read Operations/sec"

"System" Counters="File Write Operations/sec"

"System" Counters="File Control Operations/sec"

"System" Counters="File Read Bytes/sec"

"System" Counters="File Write Bytes/sec"

"System" Counters="File Control Bytes/sec"

"Network Interface" Counters="Bytes Total/sec" Instances="*"

"Network Interface" Counters="Output Queue Length" Instances="*"

"SQLServer:Buffer Manager" Counters="Stolen pages"

"SQLServer:Buffer Manager" Counters="Page life expectancy"

"SQLServer:Memory Manager" Counters="Memory Grants Outstanding"

"SQLServer:Memory Manager" Counters="Memory Grants Pending"

"SQLServer:Databases" Counters="Transactions/sec" Instances="_Total"

"SQLServer:Databases" Counters="Transactions/sec" Instances="tempdb"

"SQLServer:Databases" Counters="Active Transactions" Instances="*"

"SQLServer:General Statistics" Counters="Logins/sec"

"SQLServer:General Statistics" Counters="Logouts/sec"

"SQLServer:General Statistics" Counters="User Connections"

"SQLServer:General Statistics" Counters="Logical Connections"

"SQLServer:General Statistics" Counters="Transactions"

"SQLServer:General Statistics" Counters="Processes blocked"

"SQLServer:General Statistics" Counters="Active Temp Tables"

"SQLServer:SQL Statistics" Counters="Batch Requests/sec"

"SQLServer:SQL Statistics" Counters="SQL Compilations/sec"

"SQLServer:SQL Statistics" Counters="SQL Re-Compilations/sec"

"SQLServer:SQL Statistics" Counters="SQL Attention rate"

"SQLServer:SQL Statistics" Counters="Auto-Param Attempts/sec"

"SQLServer:SQL Statistics" Counters="Failed Auto-Params/sec"

"SQLServer:Plan Cache" Counters="Cache Hit Ratio" Instances="_Total"

"SQLServer:Plan Cache" Counters="Cache Hit Ratio" Instances="Object Plans"

"SQLServer:Plan Cache" Counters="Cache Hit Ratio" Instances="SQL Plans"

"SQLServer:Plan Cache" Counters="Cache Hit Ratio" Instances="Temporary Tables &amp; Table Variables"

"SQLServer:Transactions" Counters="Free Space in tempdb (KB)"

"SQLServer:Workload Group Stats" Counters="Active requests" Instances="*"

"SQLServer:Workload Group Stats" Counters="Blocked tasks" Instances="*"

"SQLServer:Workload Group Stats" Counters="CPU usage %" Instances="*"

Sammlungssatz für Abfragestatistiken

Der Sammlungssatz für Abfragestatistiken erfasst Daten über Abfragestatistiken sowie einzelne Abfragetexte, Abfragepläne und bestimmte Abfragen. Diese Daten in Verbindung mit Statistiken und Aktivitäten auf Systemebene ermöglichen Ihnen, einen Drilldown unter die Sitzungsebene bis zu einer einzelnen Abfrage hin auszuführen.

Dieser Sammlungssatz erfasst Daten der folgenden Quellen:

  • sys.dm_exec_requests, sys.dm_exec_sessions, sys.dm_exec_query_stats und andere verwandte dynamische Verwaltungssichten.

  • Der Text ausgewählter Batches und Abfragen.

  • Der Plan ausgewählter Batches und Abfragen.

  • Der normalisierte Text ausgewählter Batches.

Der Sammlungssatz für Abfragestatistiken verwendet den Abfrageaktivitäts-Sammlertyp. Der Abfrageaktivitäts-Sammlertyp erfasst Daten mit dem SSIS-Paket QueryActivityCollect.dtsx und lädt Daten mit dem SSIS-Paket QueryActivityUpload.dtsx hoch. Weitere Informationen über die Erfassungs- und Aktualisierungsphasen des Abfrageaktivitäts-Sammlertyps, einschließlich der verwendeten Abfragen, finden Sie unter Abfrageaktivitäts-Sammlertyp.

Die folgenden Tabellen enthalten Informationen über den Sammlungssatz für Abrufstatistiken und dessen Sammelelement.

Sammlungssatzname

Abfragestatistik

Sammlungsmodus

Zwischengespeichert

Häufigkeit für den Hochladezeitplan

Alle 15 Minuten

Datenbeibehaltung

14 Tage

Sammelelement

Abfragestatistik - Abfrageaktivität