通过

  • 项目

系统数据收集组

在 SQL Server 2008 安装过程中，数据收集器将安装三个系统数据收集组。您可以针对自己的监视要求配置这些收集组，但不能将其删除。系统数据收集组包括：

  • 磁盘使用情况。收集有关系统中安装的所有数据库的磁盘和日志使用情况的数据。

  • 服务器活动。从服务器和 SQL Server 中收集资源使用情况统计信息和性能数据。

  • 查询统计信息。收集查询统计信息、单个查询文本、查询计划和特定查询。

磁盘使用情况收集组

磁盘使用情况收集组跟踪数据库和日志文件的增长情况并提供与文件相关的统计信息，例如每天的平均增长量（以 MB 为单位）。

该收集组有两个收集项：“磁盘使用情况 - 数据文件”和“磁盘使用情况 - 日志文件”。它们都使用一般 T-SQL 查询收集器类型。该收集组收集以下数据：

  • 从 sys.partitions 和 sys.allocation_units 视图获取的数据文件大小的快照。

  • 从 DBCC SQLPERF (LOGSPACE) 命令获取的日志文件大小的快照。

  • 从 sys.dm_io_virtual_file_stats 函数获取的 I/O 统计信息的快照。

下面的表提供了有关磁盘使用情况收集组及其收集项的详细信息。

收集组名称

磁盘使用情况

收集模式

非缓存

上载计划频率

每 6 小时

数据保持期

730 天

收集项

磁盘使用情况 - 数据文件

磁盘使用情况 - 日志文件

收集项名称

磁盘使用情况 - 数据文件

收集器类型

一般 T-SQL 查询

查询 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''

查询 1 输出

磁盘使用情况

收集项名称

磁盘使用情况 - 日志文件

收集器类型

一般 T-SQL 查询

查询 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

查询 1 输出

log_usage

服务器活动收集组

服务器活动收集组提供有关 SQL Server 活动、SQL Server 资源利用情况以及 SQL Server 资源争用情况的概况。该收集组还提供了系统资源整体利用情况的总视图，使您能够确定性能问题是否与 SQL Server 范围之外的活动有关。

此收集组收集来自以下动态管理视图的数据样本：

  • 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（使用联接查询）

  • sys.dm_os_process_memory

  • sys.dm_os_memory_nodes

此外，也从一些系统和 SQL Server 性能计数器那里收集数据样本。

服务器活动收集组为您提供了一个有关系统的资源利用情况和资源瓶颈的总体视图。将从以下四个常规方面跟踪资源的使用情况：CPU、磁盘 I/O、内存和网络。对 sys.dm_exec_sessions、sys.dm_exec_requests 和 sys.dm_os_waiting_tasks 的抽样在系统活动与资源瓶颈和阻塞问题之间建立了关联。

独立运行时，此收集组使您能够在资源瓶颈与受阻的会话之间建立关联，并显示会话级别的阻塞链。虽然它不会收集查询文本，但您可以利用查询统计信息收集组收集到的 sql_handle 和 plan_handle 信息深化到会话级别之下。

下面的表提供了有关服务器活动收集组及其收集项的详细信息。

收集组名称

服务器活动

收集模式

缓存

上载计划频率

每 15 分钟

数据保持期

14 天

收集项

服务器活动 - DMV 快照

服务器活动 - 性能计数器

收集项名称

服务器活动 - DMV 快照

收集器类型

一般 T-SQL 查询

收集频率

60 秒

查询 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

查询 1 输出

snapshots.os_wait_stats

查询 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

查询 2 输出

snapshots.os_latch_stats

查询 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

查询 3 输出

snapshots.sql_process_and_system_memory

查询 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

查询 4 输出

snapshots.os_memory_nodes

查询 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

查询 5 输出

snapshots.os_memory_clerks

查询 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

查询 6 输出

snapshots.os_schedulers

查询 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]

查询 7 输出

snapshots.io_virtual_file_stats

收集项名称

服务器活动 - 性能计数器

收集器类型

性能计数器

收集频率

60 秒

使用的性能计数器

"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="*"

查询统计信息收集组

查询统计信息收集组收集有关查询统计信息和单个查询文本、查询计划以及特定查询的数据。当与系统级的统计信息和活动联系起来时，此数据使您可以深化至会话级别以下，直至单个查询。

此收集组收集来自以下源的数据：

  • sys.dm_exec_requests、sys.dm_exec_sessions、sys.dm_exec_query_stats 和其他相关动态管理视图。

  • 所选批处理和查询的文本。

  • 所选批处理和查询的计划。

  • 所选批处理的规范化文本。

查询统计信息收集组使用查询活动收集器类型。查询活动收集器类型使用 QueryActivityCollect.dtsx SSIS 包收集数据，使用 QueryActivityUpload.dtsx SSIS 包上载数据。有关查询活动收集类型的收集和上载阶段的详细信息（包括使用的查询），请参阅查询活动收集器类型。

下面的表提供了有关查询统计信息收集组及其收集项的信息。

收集组名称

查询统计信息

收集模式

缓存

上载计划频率

每 15 分钟

数据保持期

14 天

收集项

查询统计信息 - 查询活动