1. Особенности версий MS SQL Server 2012 - 2016+ для сбора статистики

Microsoft SQL Server (MS SQL) версии 2012-2016 поддерживают множество функций для мониторинга

и сбора статистики выполнения запросов. Однако для каждой версии могут быть свои нюансы и

особенности. Вот основные моменты, которые стоит учесть:

Поддерживаемые версии и функционал

SQL Server 2012

  • Поддержка DMVs: Поддерживаются представления динамических систем (DMVs), такие как sys.dm_exec_requests, sys.dm_exec_query_stats и sys.dm_exec_sessions.
  • Статистика: SQL Server 2012 предоставляет возможность собирать базовые статистики о выполнении запросов, включая общее время выполнения и количество вызовов через sys.dm_exec_query_stats.

SQL Server 2014

  • Улучшенная производительность: Введены новые функции для улучшенного мониторинга, такие как Query Store (но требует дальнейших обновлений в SQL Server 2016).
  • Поддержка DMVs: Повышенные возможности анализа производительности через дополнительные DMVs.

SQL Server 2016

  • Query Store: Представляет собой важную новую функцию, которая сохраняет информацию о производительности запросов, позволяя делать анализ более детальным. Это позволяет отслеживать и сохранять информацию о выполнении запросов между перезапусками службы.
  • Дополнительные DMVs: Обновления в DMVs для более детального мониторинга и улучшенного анализа производительности.

Нюансы

  1. Query Store:

    • Поддержка: Доступен с SQL Server 2016. Позволяет сохранять и анализировать статистику выполнения запросов. Вместе с исправлениями и улучшениями может помочь в определении изменений в производительности запросов.
    • Настройка: Нужно будет включить включение Query Store для каждой базы данных отдельно. Это делается через SQL Server Management Studio или с помощью команды:
    MSSQL
    ALTER DATABASE [YourDatabaseName] SET QUERY_STORE = ON;

  2. Использование DMVs:

    • Возраст статистики: DMVs возвращают информацию, которая обновляется в реальном времени, но на момент запроса. Это значит, что данные о завершенных запросах недоступны.
    • Разрешения: Для доступа к определенным DMVs могут потребоваться соответствующие разрешения. Например, нужно иметь разрешение VIEW SERVER STATE, чтобы видеть информацию о всех сессиях, иначе видны будут только ваши собственные.

  3. Индексирование:

    • Нельзя забывать о важности качественного индексирования, которое напрямую определяет производительность запросов. Нехватка индексов или неправильные индексы могут вызвать проблемы с производительностью, даже если доступ к статистике осуществляется правильно.

  4. Пропускная способность и нагрузка:

    • При использовании DMVs для мониторинга запросов следует учитывать, что извлеченные данные могут сами потребовать ресурсов при высоких нагрузках на систему.

Что в итоге?

Версии MS SQL 2012-2016 достаточно хорошо поддерживают функции для мониторинга и сбору статистики, однако каждая версия привносит свои особенности и требования к настройке. Для полноценного мониторинга рекомендуется использовать Query Store в SQL Server 2016 и выше, а также заранее планировать индексы и разрешения для доступа к нужным представлениям DMVs.


2. Мониторинг в реальном времени через DMV sys.dm_exec_requests и sys.dm_exec_sessions

Используйте представления динамических управляющих объектов (DMV) для мониторинга

текущих активных соединений и запросов. Запрос к sys.dm_exec_requests и sys.dm_exec_sessions

покажет, какие запросы работают в БД в конкретный момент времени.

MSSQL
SELECT r.session_id,
       r.status,
       r.start_time,
       r.command,
       r.total_elapsed_time,
       r.cpu_time,
       r.reads,
       r.writes,
       s.login_name,
       s.host_name,
       s.program_name
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s
ON r.session_id = s.session_id
WHERE r.status <> 'background'
ORDER BY r.total_elapsed_time DESC;


Этот функционал полезен для диагностики текущих проблем с производительностью

и позволяет увидеть долго выполняющиеся запросы.

Однако он не покажет статистику по работе запросов, которые уже завершились,

поэтому его нельзя рассматривать как полноценный инструмент мониторинга.


3. Сбор статистики выполнения запросов с помощью встроенных функций

MS SQL Server не имеет прямого аналога pg_stat_statements как в PostgreSQL, однако вы можете

использовать системные представления и функции для сбора информации.

3.1 Настройка сборка статистики

Вы можете включить сбор статистики, используя следующие команды:

MSSQL
-- Включите ведение статистики для запросов
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;

3.2 Сбор статистики

Для сбора статистики о долгих запросах можно использовать следующий запрос:

  • Сбор самых долгих (по общему времени выполнения) запросов:
MSSQL
SELECT TOP 100 
       qs.query_hash,
       SUBSTRING(qt.text, (qs.statement_start_offset / 2) + 1,
       (CASE statement_end_offset
             WHEN -1 THEN DATALENGTH(qt.text)
             ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2 + 1) AS query_text,
       qs.execution_count,
       qs.total_elapsed_time,
       qs.total_elapsed_time / qs.execution_count AS mean_time,
       qs.max_elapsed_time,
       qs.total_rows
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY total_elapsed_time DESC;


  • Сбор самых долгих (по среднему времени выполнения) запросов:
MSSQL
SELECT TOP 100 
       qs.query_hash,
       SUBSTRING(qt.text, (qs.statement_start_offset / 2) + 1,
       (CASE statement_end_offset
             WHEN -1 THEN DATALENGTH(qt.text)
             ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2 + 1) AS query_text,
       qs.execution_count,
       qs.total_elapsed_time,
       qs.total_elapsed_time / qs.execution_count AS mean_time,
       qs.max_elapsed_time,
       qs.total_rows
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY mean_time DESC;


  • Сбор самых долгих (по максимальному времени выполнения) запросов:
MSSQL
SELECT TOP 100 
       qs.query_hash,
       SUBSTRING(qt.text, (qs.statement_start_offset / 2) + 1,
       (CASE statement_end_offset
             WHEN -1 THEN DATALENGTH(qt.text)
             ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2 + 1) AS query_text,
       qs.execution_count,
       qs.total_elapsed_time,
       qs.total_elapsed_time / qs.execution_count AS mean_time,
       qs.max_elapsed_time,
       qs.total_rows
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY max_elapsed_time DESC;


Для анализа будут полезны все три вида статистики. 

После сбора статистики вы можете отправить информацию на анализ в 
Отдел сопровождения и внедрения: support@iqstore.ru.


3.3 Аналоги PWR-отчёта (из PostgresPro)

Если вы хотите использовать аналоги PWR-отчетов, в MS SQL Server есть функционал
для генерации отчетов производительности, такие как SQL Server Reporting Services (SSRS),
которые могут быть использованы для создания более сложных отчетов.


  • No labels