ВMicrosoft SQL Server у специалистов по обработке данных есть несколько способов собрать данные о внутренних механизмах платформы: от динамических административных представлений (DMV) и функций (DMF) до расширенных событий, представлений каталога и огромного количества сторонних инструментов. С их помощью можно получить почти любые сведения о внутреннем функционировании SQL Server, если вы знаете, где смотреть.

Мне много приходилось писать о динамических административных объектах (DMO), они появляются с каждой новой версией SQL Server. В данной статье мы сосредоточимся на объекте, впервые представленном в Microsoft SQL Server 2016: dm_exec_function_stats.

Выходные данные dm_exec_function_stats похожи на результаты двух других DMV: dm_exec_procedure_stats и dm_exec_query_stats. Каждый представляет метрики для объектов определенных типов в кэше: dm_exec_function_stats (функции, определяемые пользователем), dm_exec_procedure_stats (несистемные хранимые процедуры) и dm_exec_query_stats (созданные пользователем запросы).

Объект dm_exec_function_stats обеспечивает более детальный анализ производительности, нежели dm_exec_procedure_stats и dm_exec_query_stats. Объекты procedure_stats и query_stats предоставляют метаданные о процессоре, оперативной памяти, вводе-выводе и сведения о длительности полного выполнения хранимой процедуры или запроса, а function_stats выводит те же данные для определяемой пользователем функции, которая никогда не бывает изолированным объектом. Необходимо использовать функцию внутри инструкции. Эта инструкция сама по себе или в сочетании с другими командами T-SQL станет частью хранимой процедуры или нерегламентированного запроса. Таким образом, использование dm_exec_function_stats позволяет получить детальную информацию о производительности функции. Применение dm_exec_procedure_stats и dm_exec_query_stats охватывает все элементы T-SQL, являющиеся частью хранимых процедур или пакетов нерегламентированных запросов, которые могут содержать одну или несколько определяемых пользователем функций (UDF).

Как и у родственных объектов, выходные данные dm_exec_function_stats содержат значительное количество столбцов, охватывающих классы ключевых индикаторов, необходимых при настройке производительности элементов в SQL Server.

  • Процессор: минимальный, максимальный и последний результаты выполнения.
  • Оперативная память: минимальный, максимальный и последний результаты выполнения.
  • Длительность: минимальный, максимальный и последний результаты выполнения.
  • Ввод-вывод: логические и физические операции чтения; физические операции записи: минимальный, максимальный и последний результаты выполнения.
  • Число выполнений.
  • Дескриптор SQL: уникальная идентификация текста T-SQL, который содержит определение функции.
  • Дескриптор плана: идентифицирует план запроса, кэшированного для функции.
  • Идентификация информации, такой как database_id и object_id, type.

Существование sql_handle и plan_handle в этом DMV позволяет выполнить присоединение к другим объектам DMO, чтобы возвратить определяющий код для функции и посмотреть графический план выполнения. Можно также задействовать системные функции, например dm_name() и object_name(), чтобы пользователю было удобнее просматривать результаты в таких столбцах, как database_id и object_id.

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

Я разделил результаты на логические фрагменты, чтобы, помимо прочего, упростить чтение большого набора результатов (см. экраны 1-5).

Показатели времени исполнения функции
Экран 1. Показатели времени исполнения функции
Показатели времени загрузки процессора и длительности выполнения функции
Экран 2. Показатели времени загрузки процессора и длительности выполнения функции
Показатели логических и физических операций считывания
Экран 3. Показатели логических и физических операций считывания
Показатели операций записи
Экран 4. Показатели операций записи
Список исполняемых процедур
Экран 5. Список исполняемых процедур

Запрос легко настроить только на интересующие вас функции с помощью параметров для сортировки по длительности, операциям чтения, записи, загрузке процессора и т. д., в зависимости от того, с какой строки вы снимите комментарии в предложении ORDER BY.

Еще один фактор, который следует учесть, рассматривая результаты, полученные от этих объектов DMO, — подходящая единица измерения. Вы вряд ли станете измерять размеры гаража в миллиметрах, чтобы выяснить, войдет ли в него ваш новый автомобиль. Вы выберете единицу измерения, более подходящую для этой задачи. То же самое относится к функциям в сравнении с выполнением процедур или нерегламентированных запросов. Длительность в различных столбцах, предоставленная этими объектами, измеряется в микросекундах (1/1 000 000 доля секунды). Создавая запросы с использованием dm_exec_procedure_stats и dm_exec_query_stats для анализа, я всегда увеличивал единицу измерения в 1000 раз, чтобы измерять длительность в миллисекундах (1/1000 доля секунды). Это соответствует единице измерения по умолчанию для тестов производительности запросов. Функции — лишь компонент более крупного запроса (будь то хранимая процедура или нерегламентированная форма), поэтому я оставил микросекунды в качестве единицы измерения.

При рассмотрении результатов первое, что привлекает внимание, — разброс времени выполнения между средним и максимальным значениями. Было бы очень любопытно более глубоко проанализировать результаты и выяснить, почему производительность (возможно) нестабильна. Такой запрос полезен, когда нужно идентифицировать функции, требующие настройки. Кроме того, он может помочь улучшить производительность там, где есть соответствующие возможности, обнаруживаемые в ходе анализа хранимых процедур и нерегламентированных запросов, в которых функции играют определенную роль. dm_exec_function_stats — еще один инструмент, который полезно иметь под рукой при анализе производительности экземпляров Microsoft SQL Server.

Листинг. Запрос состояния определяемых пользователем скалярных функций 
SELECT TOP 25 db_name(eST.[dbid]) AS [database]
       , OBJECT_SCHEMA_NAME(eFS.[object_id], eFS.database_id) AS [schema_name]
       , OBJECT_NAME(eFS.[object_id], eFS.database_id) AS [function_name]
       , eFS.execution_count
       -- CPU
       , CAST(eFS.min_worker_time/1000.0 AS decimal(16,4)) AS [min_cpu_time_mcs]
       , CAST(eFS.max_worker_time/1000.0 AS decimal(16,4)) AS [max_cpu_time_mcs]
       , CAST(eFS.total_worker_time/(ISNULL(eFS.execution_count, 1.0)) AS decimal(16,4)) AS [avg_cpu_time_mcs]
       -- ELAPSED TIME
       , CAST( eFS.min_elapsed_time/1000.0 AS decimal(16,4)) AS [min_duration_mcs]
       , CAST(eFS.max_elapsed_time/1000.0 AS decimal(16,4)) AS [max_duration_mcs]
       , CAST(eFS.total_elapsed_time/(ISNULL(eFS.execution_count, 1.0)) AS decimal(16,4)) AS [avg_duration_mcs]
       -- LOGICAL READS
       , eFS.min_logical_reads AS [min_logical_reads]
       , eFS.max_logical_reads AS [max_logical_reads]
       , eFS.total_logical_reads/ISNULL(eFS.execution_count, 1) AS [avg_logical_reads]
       -- PHYSICAL READS
       , eFS.min_physical_reads AS [min_physical_reads]
       , eFS.max_physical_reads AS [max_physical_reads]
       , eFS.total_physical_reads/ISNULL(eFS.execution_count, 1) AS [avg_physical_reads]
       -- LOGICAL WRITES
       , eFS.min_logical_writes AS [min_writes]
       , eFS.max_logical_writes AS [max_writes]
       , eFS.total_logical_writes/ISNULL(eFS.execution_count, 1) AS [avg_writes]
       -- CACHE & EXEC TIMES
       , eFS.last_execution_time
       --STATEMENTS AND QUERY TEXT DETAILS
       , eST.text AS [procedure_code]
       , eFS.[plan_handle]
FROM sys.dm_exec_function_stats AS eFS
       CROSS APPLY sys.dm_exec_sql_text(eFS.sql_handle) AS eST
       CROSS APPLY sys.dm_exec_query_plan (eFS.plan_handle) AS eQP
WHERE eST.[dbid] = db_id()
--ORDER BY eFS.total_logical_reads/ISNULL(eFS.execution_count, 1) DESC;        -- [avg_logical_reads]
--ORDER BY eFS.total_physical_reads/ISNULL(eFS.execution_count, 1) DESC;       -- [avg_physical_reads]
--ORDER BY eFS.total_logical_writes/ISNULL(eFS.execution_count, 1) DESC;       -- [avg_logical_writes]
ORDER BY eFS.total_worker_time/ISNULL(eFS.execution_count, 1)    DESC;         -- [avg_cpu]
--ORDER BY eFS.total_elapsed_time/ISNULL(eFS.execution_count, 1) DESC;         -- [avg_duration]