• Статья

Наборы сбора системных данных

При установке SQL Server 2008 сборщик данных устанавливает три набора сбора системных данных. Эти наборы сбора можно настроить в соответствии с требованиями пользователя к наблюдению, но нельзя удалить. Наборы сбора системных данных состоят из следующих элементов.

  • Занято места на диске. Выполняет сбор данных об использовании диска и журнала для всех баз данных, установленных в системе.

  • Активность сервера. Выполняет сбор статистики по использованию ресурсов и данных производительности по серверу и SQL Server.

  • Статистика запросов. Выполняет сбор статистики по запросам, текстов отдельных запросов, планов запросов и конкретных запросов.

Набор сбора «Занято места на диске»

Набор сбора «Занято места на диске» отслеживает рост базы данных и файлов журнала и предоставляет статистику по файлам, такую как средний рост (в мегабайтах) в день.

В наборе сбора есть два элемента сбора: Занято места на диске — файлы данных и Занято места на диске — файлы журнала. Для обоих используется тип сборщика «Универсальный запрос T-SQL». Набор сбора собирает следующие данные:

  • Моментальные снимки размеров файлов данных, полученных из представлений sys.partitions и sys.allocation_units.

  • Моментальные снимки размеров файлов журнала, полученных из команды DBCC SQLPERF (LOGSPACE).

  • Моментальные снимки статистики ввода-вывода из функции sys.dm_io_virtual_file_stats.

В следующих таблицах представлены подробные сведения о наборе сбора «Занято места на диске» и его элементах сбора.

Имя набора сбора

Использование диска

Режим сбора

Без кэширования

Частота передачи по расписанию

Каждые 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.

Набор сбора «Активность сервера» дает полное представление о системе, связанное с использованием ресурсов и узкими местами ресурсов. Использование ресурсов отслеживается в четырех основных областях: ЦП, дисковые операции ввода-вывода, память и сеть. Выборка из представлений sys.dm_exec_sessions, sys.dm_exec_requests и sys.dm_os_waiting_tasks позволяет выполнять сопоставление системной активности с узкими местами ресурсов и с проблемами блокирования.

При самостоятельном запуске этот набор сбора позволяет связать узкие места ресурсов с заблокированным сеансом и показать цепочки блокировки на уровне сеанса. Несмотря на то что тексты запросов не собираются, можно использовать данные из столбцов sql_handle и plan_handle, собранные набором сбора «Статистика запросов», для детализации углублением ниже уровня сеанса.

В следующих таблицах представлены подробные сведения о наборе сбора «Активность сервера» и его элементов сбора.

Имя набора сбора

Активность сервера

Режим сбора

Кэшируется

Частота передачи по расписанию

Каждые 15 минут

Хранение данных

14 дней

Элементы сбора

Активность сервера — моментальные снимки динамических административных представлений

Активность сервера — счетчики производительности

Имя элемента сбора

Активность сервера — моментальные снимки динамических административных представлений

Тип сборщика

Универсальный запрос 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"="Pages/sec"

Счетчик объекта «Память»=«Чтений страниц/сек»

Счетчик объекта «Память»=«Записей страниц/сек»

Счетчик объекта «Память»=«Ошибок страницы/сек»

Счетчик объекта «Память»=«Байт в невыгружаемом страничном пуле»

Счетчик объекта «Память»=«Байт в выгружаемом страничном пуле»

Счетчик объекта «Память»=«Байты основного резервного кэша»

Счетчик объекта «Память»=«Байты резервного кэша обычного приоритета»

Счетчик объекта «Память»=«Байты резерва резервного кэша»

Счетчик объекта «Память»=«Байт в выгружаемом страничном пуле»

Счетчик объекта «Память»=«Запись копий страниц/сек»

Счетчики объекта "Process"="*" Экземпляры="_Total"

Счетчики объекта "Process"="*" Экземпляры="$(TARGETPROCESS)"

Счетчик объекта «Процесс»=«Счетчик потоков», экземпляр="*"

Счетчик объекта «Процесс»=«% загруженности процессора», экземпляр=«*»

Счетчик объекта «Процесс»=«I/O — чтение байт в сек», экземпляр="*"

Счетчик объекта «Процесс»=«I/O — запись байт в сек», экземпляр="*"

Счетчик объекта «Процесс»=«Байт исключительного пользования», экземпляр="*"

Счетчик объекта «Процесс»=«Рабочее множество», экземпляр="*"

Счетчик объекта «Процессор»=«% загруженности процессора», экземпляр="*"

Счетчик объекта «Процессор»=«% работы в пользовательском режиме», экземпляр="*"

Счетчик объекта «Процессор»=«% работы в привилегированном режиме», экземпляр="*"

Счетчик объекта «Рабочие очереди сервера»=«Длина очереди», экземпляр="*"

Счетчик объекта «Логический диск»=«% активности диска», экземпляр="*"

Счетчики объекта "LogicalDisk"="Avg. Disk Queue Length" Экземпляры="*"

Счетчики объекта "LogicalDisk"="Avg. Disk Read Queue Length" Экземпляры="*"

Счетчики объекта "LogicalDisk"="Avg. Disk Write Queue Length" Экземпляры="*"

Счетчики "LogicalDisk" = "Avg. Disk sec/Read" Экземпляры="*"

Счетчики "LogicalDisk" = "Avg. Disk sec/Write" Экземпляры="*"

Счетчики "LogicalDisk" = "Avg. Disk sec/Transfer" Экземпляры="*"

Счетчик объекта «Логический диск»=«Обращений чтения с диска/сек», экземпляр="*"

Счетчики объекта "LogicalDisk"="Disk Bytes/sec" Экземпляры="*"

Счетчик объекта «Логический диск»=«Обращений записи на диск/сек», экземпляр="*"

Счетчик объекта «Логический диск»=«Расщеплений ввода-вывода/сек», экземпляр="*"

Счетчик объекта «Система»=«Длина очереди процессора»

Счетчик объекта «Система» =«Операций чтения файлов/сек»

Счетчик объекта «Система» =«Операций записи файлов/сек»

Счетчик объекта «Система» =«Операций управления файлами/сек»

Счетчик объекта «Система» =«Байт чтения файлов/сек»

Счетчик объекта «Система» =«Байт записи файлов/сек»

Счетчик объекта «Система» =«Байт управления файлами/сек»

Счетчик объекта «Сетевой интерфейс» =«Всего байт в секунду», экземпляр="*"

Счетчик объекта «Сетевой интерфейс»=«Длина очереди вывода», экземпляр="*"

Счетчики объекта "SQLServer:Buffer Manager"="Stolen pages"

Счетчики объекта "SQLServer:Buffer Manager"="Page life expectancy"

Счетчики объекта "SQLServer:Memory Manager"="Memory Grants Outstanding"

Счетчики объекта "SQLServer:Memory Manager"="Memory Grants Pending"

Счетчики объекта "SQLServer:Databases"="Transactions/sec" Экземпляры="_Total"

Счетчики объекта "SQLServer:Databases"="Transactions/sec" Экземпляры="tempdb"

Счетчики объекта "SQLServer:Databases"="Active Transactions" Экземпляры="*"

Счетчик объекта «SQLServer: общая статистика»=«Входов/с»

Счетчик объекта «SQLServer: общая статистика»=«Выходов/с»

Счетчик объекта «SQLServer: общая статистика»=«Соединений пользователей»

Счетчик объекта «SQLServer: общая статистика»=«Логических соединений»

Счетчик объекта «SQLServer: общая статистика»=«Транзакций»

Счетчик объекта «SQLServer: общая статистика»=«Заблокированных процессов»

Счетчики объекта "SQLServer:General Statistics"="Active Temp Tables"

Счетчик объекта «SQLServer: статистика SQL»=«Запросов пакетов/с»

Счетчик объекта «SQLServer: статистика SQL»=«Компиляций SQL/с»

Счетчик объекта «SQLServer: статистика SQL»=«Повторных компиляций SQL/с»

Счетчик объекта «SQLServer: статистика SQL»=«Скорость предупреждений SQL»

Счетчики объекта "SQLServer:SQL Statistics"="Auto-Param Attempts/sec"

Счетчики объекта "SQLServer:SQL Statistics"="Failed Auto-Params/sec"

Счетчики объекта "SQLServer:Plan Cache"="Cache Hit Ratio" Экземпляры="_Total"

Счетчики объекта "SQLServer:Plan Cache"="Cache Hit Ratio" Экземпляры="Object Plans"

Счетчики объекта "SQLServer:Plan Cache"="Cache Hit Ratio" Экземпляры="SQL Plans"

Счетчики объекта "SQLServer:Plan Cache"="Cache Hit Ratio" Экземпляры="Temporary Tables &amp; Table Variables"

Счетчики объекта "SQLServer:Transactions"="Free Space in tempdb (KB)"

Счетчики объекта "SQLServer:Workload Group Stats"="Active requests" Экземпляры="*"

Счетчики объекта "SQLServer:Workload Group Stats"="Blocked tasks" Экземпляры="*"

Счетчики объекта "SQLServer:Workload Group Stats"="CPU usage %" Экземпляры="*"

Набор сбора «Статистика запросов»

Набор сбора «Статистика запросов» собирает данные по статистике запросов, а также тексты отдельных запросов, планы запросов и конкретные запросы. Эти данные в сочетании с системной статистикой и действиями позволяют проводить детализацию углублением ниже уровня сеанса к отдельным запросам.

Этот набор сбора собирает образцы данных из следующих источников:

  • sys.dm_exec_requests, sys.dm_exec_sessions, sys.dm_exec_query_stats и другие связанные динамические административные представления;

  • текст выбранных пакетов и запросов;

  • план выбранных пакетов и запросов;

  • нормализованный текст выбранных пакетов.

Набор сбора «Статистика запросов» использует тип сборщика «Активность запросов». Тип сборщика «Активность запросов» собирает данные с использованием пакета QueryActivityCollect.dtsx служб SSIS и передает данные с использованием пакета QueryActivityUpload.dtsx служб SSIS. Дополнительные сведения о стадиях сбора и передачи типа сборщика «Активность запросов», включая используемые запросы, см. в разделе Сборщик типа «Активность запросов».

В следующих таблицах представлены сведения о наборе сбора «Статистика запросов» и его элементе сбора.

Имя набора сбора

Статистика запросов

Режим сбора

Кэшируется

Частота передачи по расписанию

Каждые 15 минут

Хранение данных

14 дней

Элемент сбора

Статистика запросов — действия запросов