SQL-Ex blog

Поскольку, несмотря на наши усилия, начиная с SQL Server 2000, мы не можем эффективно использовать скалярные UDF, нельзя ли просто заставить SQL Server лучше обрабатывать их?

SQL Server 2019 вводит новую возможность, названную Scalar UDF Inlining (встраивание скалярных пользовательских функций). Вместо отдельной обработки функции, она встраивается в общий план. Это приводит к значительно лучшему плану выполнения и, в свою очередь, к лучшей производительности.

Но сначала, чтобы наглядно продемонстрировать источник проблемы, давайте возьмем пару простых таблиц, заполненных небольшим числом строк, в базе данных, запущенной под SQL Server 2017 (или под 2019, но с более низким уровнем совместимости):

CREATE DATABASE Whatever;

GO

ALTER DATABASE Whatever SET COMPATIBILITY_LEVEL = 140;

GO

USE Whatever;

GO

 CREATE TABLE dbo.Languages

(

  LanguageID int PRIMARY KEY,

  Name sysname

);

 CREATE TABLE dbo.Employees

(

  EmployeeID int PRIMARY KEY,

  LanguageID int NOT NULL FOREIGN KEY REFERENCES dbo.Languages(LanguageID)

);

 INSERT dbo.Languages(LanguageID, Name) VALUES(1033, N'English'), (45555, N'Klingon');

 INSERT dbo.Employees(EmployeeID, LanguageID)

  SELECT [object_id], CASE ABS([object_id]%2) WHEN 1 THEN 1033 ELSE 45555 END 

  FROM sys.all_objects;

Далее у нас есть простой запрос, в котором мы хотим для каждого сотрудника показать название его родного языка. Пусть, например, этот запрос используется во множестве мест и/или разными способами, поэтому, вместо построения запроса с соединением, мы пишем скалярную UDF, чтобы избежать этого соединения:

CREATE FUNCTION dbo.GetLanguage(@id int)

RETURNS sysname

AS

BEGIN

  RETURN (SELECT Name FROM dbo.Languages WHERE LanguageID = @id);

END

Тогда наш запрос будет выглядеть как-то так:

SELECT TOP (6) EmployeeID, Language = dbo.GetLanguage(LanguageID)

  FROM dbo.Employees;

Если мы посмотрим на план выполнения этого запроса, то обнаружим, что кое-что странным образом там отсутствует:



Как осуществляется доступ к таблице Languages? Этот план выглядит очень эффективным (как и сама функция), он просто абстрагируется от некоторой присущей ему сложности. Фактически этот графический план идентичен запросу, который только присваивает константу или переменную столбцу Language:

SELECT TOP (6) EmployeeID, Language = N'Sanskrit'

  FROM dbo.Employees;

Однако, если вы запустите трассировку оригинального запроса, то увидите, что на самом деле выполняется 6 вызовов функции (по разу на каждую строку) дополнительно к основному запросу. Но эти планы не возвращаются SQL Server.

Вы также можете это проверить с помощью sys.dm_exec_function_stats, но это не гарантируется:

SELECT [function] = OBJECT_NAME([object_id]), execution_count 

  FROM sys.dm_exec_function_stats

  WHERE object_name(object_id) IS NOT NULL;



function         execution_count

-----------        -----------------------

GetLanguage             6

SentryOne Plan Explorer покажет операторы, если вы генерируете актуальный план внутри этого продукта, но мы можем только получить их из трассы. И по-прежнему нет планов, собранных вместе или показанных для отдельных вызовов функции:



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

Перемотка к SQL Server 2019

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

Мы может использовать тот же самый пример, либо создав тот же набор объектов в базе данных SQL Server 2019, либо очистить кэш планов и поднять уровень совместимости до 150:

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

GO

ALTER DATABASE Whatever SET COMPATIBILITY_LEVEL = 150;

GO

Теперь, когда мы выполним наш шестистрочный запрос снова:

SELECT TOP (6) EmployeeID, Language = dbo.GetLanguage(LanguageID)

  FROM dbo.Employees;

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



Здесь оптимизатор выбирает соединение вложенными циклами (nested loops join), но при определенных обстоятельствам может быть выбрана другая стратегия соединения, предполагающая параллелизм и весьма свободная в полном изменении формы плана. Вряд ли вы увидите это в запросе, который возвращает 6 строк, и какие-либо проблемы с производительностью, но это возможно на больших объемах.

План отражает тот факт, что функция уже не вызывается для каждой строки. Хотя поиск действительно выполняется шесть раз, вы можете видеть, что сама функция больше не показывается в sys.dm_exec_function_stats. Один минус, который вы можете отбросить, - это то, что использование данного DMV для определения активного использования функции (как мы часто поступаем для процедур и индексов) не является больше надежным.

Возражения

Не каждая скалярная функция является встраиваемой и, даже когда функция является встраиваемой, она не обязательно будет встраиваться в каждом сценарии. Это часто происходит либо из-за сложности функции, либо из-за сложности запроса, в который она входит, либо из-за того и другого вместе. Вы можете проверить, является ли функция встраиваемой в представлении каталога sys.sql_modules:

SELECT OBJECT_NAME([object_id]), definition, is_inlineable

  FROM sys.sql_modules;

И, если по каким-либо причинам вы не хотите, чтобы некоторая функция (или любая функция в базе данных) встраивалась, вы не должны полагаться на уровень совместимости базы данных. Вы можете контролировать ее поведение на уровне модуля с помощью опции INLINE:

ALTER FUNCTION dbo.GetLanguage(@id int)

RETURNS sysname

WITH INLINE = OFF

AS

BEGIN

  RETURN (SELECT Name FROM dbo.Languages WHERE LanguageID = @id);

END

GO

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

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = OFF;

Заключение

Я не предлагаю, чтобы Вы бросились переводить всю логику в скалярные UDF, и считать, что теперь только SQL Server будет заботиться обо всем этом. Если Ваша база данных широко использует скалярные UDF, Вы должны загрузить последний SQL Server 2019 CTP, восстановить туда резервную копию вашей базы данных и проверить DMV на предмет того, сколько из этих функций будет встраиваться, когда это потребуется. Это может стать доводом в пользу обновления.

Между тем, если Вы испытываете проблемы с производительностью от скалярных UDF и не собираете переходить SQL Server 2019 в ближайшее время, можно рассмотреть другие способы смягчения проблемы.