Перейти к основному содержимому
Версия: 4.12

Статистика топ-100 долгих запросов в БД MS SQL Server.

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 или с помощью команды:

  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

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

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;

Пример выполнения в MS SQL 2014image-2025-4-16_15-46-18.png

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

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

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

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

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

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

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

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

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

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

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

  • Сбор самых долгих (по общему времени выполнения) запросов:
SELECT TOP 100
    qs.query_hash,
    SUBSTRING(
        qt.text, 
        (qs.statement_start_offset / 2) + 1,
        (CASE qs.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 qs.total_elapsed_time DESC;

Пример выполнения в MS SQL 2014

image-2025-4-16_15-57-28.png

  • Сбор самых долгих (по среднему времени выполнения) запросов:
SELECT TOP 100
    qs.query_hash,
    SUBSTRING(
        qt.text, 
        (qs.statement_start_offset / 2) + 1,
        (CASE qs.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;

Пример выполнения в MS SQL 2014

image-2025-4-16_15-58-41.png

  • Сбор самых долгих (по максимальному времени выполнения) запросов:
SELECT TOP 100
    qs.query_hash,
    SUBSTRING(
        qt.text, 
        (qs.statement_start_offset / 2) + 1,
        (CASE qs.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 qs.max_elapsed_time DESC;

Пример выполнения в MS SQL 2014

image-2025-4-16_15-59-7.png

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

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

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

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