Share via

  • Article

System Data Collection Sets

The data collector installs three System Data collection sets during the SQL Server 2008 Setup process. These collection sets can be configured to suit your monitoring requirements but cannot be deleted. The System Data collection sets consist of the following:

  • Disk Usage. Collects data about disk and log usage for all the databases installed on the system.

  • Server Activity. Collects resource usage statistics and performance data from the server and SQL Server.

  • Query Statistics. Collects query statistics, individual query text, query plans, and specific queries.

Disk Usage Collection Set

The Disk Usage collection set tracks the growth of database and log files and provides file-related statistics such as the average growth (in megabytes) per day.

The collection set has two collection items, Disk Usage - Data Files and Disk Usage - Log Files. Both use the Generic T-SQL Query collector type. The collection set gathers the following data:

  • Snapshots of data file sizes obtained from the sys.partitions and sys.allocation_units views.

  • Snapshots of log file sizes obtained from the DBCC SQLPERF (LOGSPACE) command.

  • Snapshots of I/O statistics from the sys.dm_io_virtual_file_stats function.

The following tables provide detailed information about the Disk Usage collection set and its collection items.

Collection set name

Disk Usage

Collection mode

Non-cached

Upload schedule frequency

Every 6 hours

Data retention

730 days

Collection items

Disk Usage - Data Files

Disk Usage - Log Files

Collection item name

Disk Usage - Data Files

Collector type

Generic T-SQL Query

Query 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''

Query 1 output

disk_usage

Collection item name

Disk Usage - Log Files

Collector type

Generic T-SQL Query

Query 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

Query 1 output

log_usage

Server Activity Collection Set

The Server Activity collection set provides an overview of SQL Server activity, SQL Server resource utilization and SQL Server resource contention. The collection set also provides an encapsulated view of overall system resource utilization that enables you to determine if performance issues are related to activities outside the scope of SQL Server.

This collection set collects data samples from the following dynamic management views:

  • 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 (using a joined query)

  • sys.dm_os_process_memory

  • sys.dm_os_memory_nodes

Additionally, data samples are collected from several system and SQL Server performance counters.

The Server Activity collection set gives you an overall view of the system in terms of resource utilization and resource bottlenecks. Resource usage is tracked in four general areas: CPU, Disk I/O, Memory, and Network. The sampling of sys.dm_exec_sessions, sys.dm_exec_requests and sys.dm_os_waiting_tasks enables the correlation of system activity with resource bottlenecks and with blocking problems.

Run on its own, this collection set enables you to associate resource bottlenecks with a blocked session and to show blocking chains at the session level. Although query texts are not collected, you can use the sql_handle and plan_handle information that is collected by the Query Statistics collection set to drill down below the session level.

The following tables provide detailed information about the Server Activity collection set and its collection items.

Collection set name

Server Activity

Collection mode

Cached

Upload schedule frequency

Every 15 minutes

Data retention

14 days

Collection items

Server Activity - DMV Snapshots

Server Activity – Performance Counters

Collection item name

Server Activity – DMV Snapshots

Collector type

Generic T-SQL Query

Collection frequency

60 seconds

Query 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

Query 1 output

snapshots.os_wait_stats

Query 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

Query 2 output

snapshots.os_latch_stats

Query 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

Query 3 output

snapshots.sql_process_and_system_memory

Query 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

Query 4 output

snapshots.os_memory_nodes

Query 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

Query 5 output

snapshots.os_memory_clerks

Query 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

Query 6 output

snapshots.os_schedulers

Query 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]

Query 7 output

snapshots.io_virtual_file_stats

Collection item name

Server Activity – Performance Counters

Collector type

Performance Counters

Collection frequency

60 seconds

Performance counters used

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

Query Statistics Collection Set

The Query Statistics collection set gathers data about query statistics and individual query text, query plans, and specific queries. This data, when linked with system-level statistics and activities, enables you to drill down below the session level to an individual query.

This collection set collects data from the following sources:

  • sys.dm_exec_requests, sys.dm_exec_sessions, sys.dm_exec_query_stats and other related dynamic management views.

  • The text of selected batches and queries.

  • The plan of selected batches and queries.

  • The normalized text of selected batches.

The Query Statistics collection set uses the Query Activity collector type. The Query Activity collector type collects data by using the QueryActivityCollect.dtsx SSIS package, and uploads data by using the QueryActivityUpload.dtsx SSIS package. For more information about the collection and upload phases of the Query Activity collection type, including the queries that are used, see Query Activity Collector Type.

The following tables provided information about the Query Statistics collection set and its collection item.

Collection set name

Query Statistics

Collection mode

Cached

Upload schedule frequency

Every 15 minutes

Data retention

14 days

Collection item

Query Statistics - Query Activity