Отслеживание производительности скомпилированных в собственном коде хранимых процедур
Область применения:
SQL Server База данных SQL 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 для процедуры.