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

Статья
08/21/2023

Применяется к:SQL Server Azure SQL Database Azure, управляемому экземпляру SQL Azure

В этой статье показано, как наблюдать за производительностью хранимых процедур, скомпилированных в собственном коде, а также других скомпилированных в собственном коде модулей T-SQL.

Использование расширенных событий

Для трассировки выполнения запроса используйте расширенное событие sp_statement_completed . Создайте сеанс с этим событием, при этом можно использовать фильтр в object_id для определенной хранимой процедуры, скомпилированной в собственном коде. Расширенное событие вызывается после выполнения каждого запроса. Время ЦП и время существования, указанные расширенным событием, показывают объем ресурсов ЦП, который потребовался на выполнение запроса, и время его выполнения. Скомпилированная в собственном коде хранимая процедура, которая потребляет значительное время ЦП, может сталкиваться с проблемами производительности.

line_numberвместе с object_id в расширенном событии можно использовать для анализа запросов. Следующий запрос может использоваться для получения определения процедуры. Номер строки можно использовать для поиска запроса в определении.

SELECT [definition]
FROM sys.sql_modules
WHERE object_id=object_id;

Использование динамических административных представлений и хранилища запросов

SQL Server и База данных SQL Azure поддерживают сбор статистики выполнения для скомпилированных хранимых процедур, как на уровне процедуры, так и на уровне запроса. Из-за влияния на производительность сбор статистики выполнения по умолчанию не используется.

Статистика выполнения отражается в системных представлениях sys.dm_exec_procedure_stats и sys.dm_exec_query_stats, а также в хранилище запросов.

Статистика выполнения на уровне процедур

SQL Server: включение или отключение сбора статистики для скомпилированных в собственном коде хранимых процедур на уровне процедуры с помощью sys.sp_xtp_control_proc_exec_stats (Transact-SQL). Следующая инструкция включает сбор статистики выполнения на уровне процедуры для всех скомпилированных в собственном коде модулей T-SQL текущего экземпляра:

EXEC sys.sp_xtp_control_proc_exec_stats 1

База данных SQL Azure и SQL Server: включение или отключение сбора статистики для скомпилированных в собственном коде хранимых процедур на уровне процедуры с помощью параметра XTP_PROCEDURE_EXECUTION_STATISTICSконфигурации с областью базы данных. Следующая инструкция включает сбор статистики выполнения на уровне процедуры для всех скомпилированных в собственном коде модулей T-SQL текущей базы данных:

ALTER DATABASE SCOPED CONFIGURATION SET XTP_PROCEDURE_EXECUTION_STATISTICS = ON;

Статистика выполнения на уровне запросов

SQL Server: включение или отключение сбора статистики для скомпилированных в собственном коде хранимых процедур на уровне запроса с помощью sys.sp_xtp_control_query_exec_stats (Transact-SQL). Следующая инструкция включает сбор статистики выполнения на уровне запроса для всех скомпилированных в собственном коде модулей T-SQL текущего экземпляра:

EXEC sys.sp_xtp_control_query_exec_stats 1

База данных SQL Azure и SQL Server: включение или отключение сбора статистики для скомпилированных в собственном коде хранимых процедур на уровне инструкции с помощью параметра XTP_QUERY_EXECUTION_STATISTICSконфигурации с областью базы данных. Следующая инструкция включает сбор статистики выполнения на уровне запроса для всех скомпилированных в собственном коде модулей T-SQL текущей базы данных:

ALTER DATABASE SCOPED CONFIGURATION SET XTP_QUERY_EXECUTION_STATISTICS = ON;

Примеры запросов

После сбора статистики статистику выполнения для скомпилированных хранимых процедур в собственном коде можно запросить процедуру с sys.dm_exec_procedure_stats (Transact-SQL) и запросы с помощью sys.dm_exec_query_stats (Transact-SQL).

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

SELECT object_id, object_name(object_id) AS 'object name',
       cached_time, last_execution_time, execution_count,
       total_worker_time, last_worker_time,
       min_worker_time, max_worker_time,
       total_elapsed_time, last_elapsed_time,
       min_elapsed_time, max_elapsed_time
FROM sys.dm_exec_procedure_stats
WHERE database_id = DB_ID()
      AND object_id IN (SELECT object_id FROM sys.sql_modules WHERE uses_native_compilation = 1)
ORDER BY total_worker_time desc;

Следующий запрос возвращает текст запроса, а также статистику выполнения всех запросов из скомпилированных в собственном коде хранимых процедур в текущей базе данных, для которой были собраны статистические данные. Статистика при этом упорядочивается по общему времени рабочей роли в убывающем порядке.

SELECT st.objectid,
        OBJECT_NAME(st.objectid) AS 'object name',
        SUBSTRING(
            st.text,
            (qs.statement_start_offset/2) + 1,
            ((qs.statement_end_offset-qs.statement_start_offset)/2) + 1
            ) AS 'query text',
        qs.creation_time, qs.last_execution_time, qs.execution_count,
        qs.total_worker_time, qs.last_worker_time, qs.min_worker_time, 
        qs.max_worker_time, qs.total_elapsed_time, qs.last_elapsed_time,
        qs.min_elapsed_time, qs.max_elapsed_time
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
WHERE database_id = DB_ID()
      AND object_id IN (SELECT object_id FROM sys.sql_modules WHERE uses_native_compilation = 1)
ORDER BY total_worker_time desc;

Планы выполнения запросов

Скомпилированные в собственном коде хранимые процедуры поддерживают SHOWPLAN_XML (предполагаемый план выполнения). С помощью предполагаемого плана выполнения можно проверять план запроса с целью выявления проблем. Общие причины появления некачественных планов.

Статистика не была обновлена перед созданием процедуры.
Отсутствующие индексы

Showplan XML получается путем выполнения следующих инструкций Transact-SQL:

SET SHOWPLAN_XML ON  
GO  
EXEC my_proc   
GO  
SET SHOWPLAN_XML OFF  
GO

Кроме того, в СРЕДЕ SQL Server Management Studio выберите имя процедуры и нажмите кнопку "Показать предполагаемый план выполнения".

Предполагаемый план выполнения для скомпилированных в собственном коде хранимых процедур показывает операторы и выражения для запросов из процедуры. SQL Server 2014 (12.x) не поддерживает все атрибуты SHOWPLAN_XML для скомпилированных в собственном коде хранимых процедур. Например, атрибуты, связанные с оценкой затрат оптимизатора запросов, не являются частью SHOWPLAN_XML для процедуры.

См. также

Скомпилированные в собственном коде хранимые процедуры