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 для более детального мониторинга и улучшенного анализа производительности.

Нюансы

Query Store:
- Поддержка: Доступен с SQL Server 2016. Позволяет сохранять и анализировать статистику выполнения запросов. Вместе с исправлениями и улучшениями может помочь в определении изменений в производительности запросов.
- Настройка: Нужно будет включить включение Query Store для каждой базы данных отдельно. Это делается через SQL Server Management Studio или с помощью команды:
ALTER DATABASE [YourDatabaseName] SET QUERY_STORE = ON;
Использование DMVs:
- Возраст статистики: DMVs возвращают информацию, которая обновляется в реальном времени, но на момент запроса. Это значит, что данные о завершенных запросах недоступны.
- Разрешения: Для доступа к определенным DMVs могут потребоваться соответствующие разрешения. Например, нужно иметь разрешение VIEW SERVER STATE, чтобы видеть информацию о всех сессиях, иначе видны будут только ваши собственные.
Индексирование:
- Нельзя забывать о важности качественного индексирования, которое напрямую определяет производительность запросов. Нехватка индексов или неправильные индексы могут вызвать проблемы с производительностью, даже если доступ к статистике осуществляется правильно.
Пропускная способность и нагрузка:
- При использовании 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

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

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 Настройка сборка статистики

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

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

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

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

Сбор самых долгих (по общему времени выполнения) запросов:

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;

Сбор самых долгих (по среднему времени выполнения) запросов:

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;

Сбор самых долгих (по максимальному времени выполнения) запросов:

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),
которые могут быть использованы для создания более сложных отчетов.

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