共用方式為

  • 發行項

系統資料收集組

資料收集器在 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 輸出

disk_usage

收集項名稱

磁碟使用量 - 記錄檔

收集器型別

一般 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 天

收集項

查詢統計資料 - 查詢活動