Inlining de UDF escalar

Artigo
01/31/2025

Aplica-se a: SQL Server 2019 (15.x) Banco de Dados SQL do Azure Instância Gerenciada SQL do Azure

Este artigo apresenta o inlining UDF escalar, um recurso sob o Processamento inteligente de consultas em bancos de dados SQL conjunto de recursos. Esse recurso melhora o desempenho de consultas que invocam UDFs escalares no SQL Server 2019 (15.x) e versões posteriores.

Funções escalares definidas pelo usuário do T-SQL

User-Defined Funções (UDFs) que são implementadas em Transact-SQL e que retornam um único valor de dados são chamadas Funções Escalares T-SQL User-Defined. As UDFs T-SQL são uma maneira elegante de obter reutilização de código e modularidade em consultas Transact-SQL. Alguns cálculos (como regras de negócios complexas) são mais fáceis de expressar na forma UDF imperativa. Os UDFs ajudam na construção de lógicas complexas sem exigir experiência na escrita de consultas SQL complexas. Para obter mais informações sobre UDFs, consulte Criar funções definidas pelo usuário (Mecanismo de Banco de Dados).

Desempenho de UDFs escalares

As UDFs escalares normalmente acabam por ter um desempenho ruim devido aos seguintes motivos:

Invocação iterativa. As UDFs são invocadas de forma iterativa, uma vez por tupla de qualificação. Isto incorre num custo adicional devido à alternância repetida de contexto causada pela chamada de função. Especialmente, UDFs que executam consultas Transact-SQL em sua definição são severamente afetadas.
Falta de orçamento. Durante a otimização, apenas os operadores relacionais são custeados, enquanto os operadores escalares não. Antes da introdução das UDFs escalares, outros operadores escalares eram geralmente baratos e não requeriam avaliação de custo. Um pequeno custo de CPU adicionado para uma operação escalar era suficiente. Há cenários em que o custo real é significativo e, no entanto, continua a estar sub-representado.
Execução interpretada. As UDFs são avaliadas como um lote de instruções, executadas instrução por instrução. Cada instrução em si é compilada e o plano compilado é armazenado em cache. Embora essa estratégia de cache economize algum tempo, pois evita recompilações, cada instrução é executada isoladamente. Não são realizadas otimizações entre declarações.
Execução em série. O SQL Server não permite paralelismo intraconsulta em consultas que invocam UDFs.

Substituição em linha automática de UDFs escalares

O objetivo do recurso de inlining de UDF escalar é melhorar o desempenho de consultas que invocam UDFs escalares T-SQL, onde a execução da UDF é o principal gargalo.

Com esta nova funcionalidade, os UDFs escalares são automaticamente transformados em expressões escalares ou subconsultas escalares que são substituídos na consulta chamada no lugar do operador UDF. Essas expressões e subconsultas são então otimizadas. Como resultado, o plano de consulta não tem mais um operador de função definido pelo usuário, mas seus efeitos são observados no plano, como exibições ou funções com valor de tabela embutido (TVFs).

Exemplos

Os exemplos nesta seção usam o banco de dados de referência TPC-H. Para obter mais informações, consulte a página inicial TPC-H.

Um. Instrução única escalar UDF

Considere a seguinte consulta.

SELECT L_SHIPDATE,
       O_SHIPPRIORITY,
       SUM(L_EXTENDEDPRICE * (1 - L_DISCOUNT))
FROM LINEITEM
     INNER JOIN ORDERS
         ON O_ORDERKEY = L_ORDERKEY
GROUP BY L_SHIPDATE, O_SHIPPRIORITY
ORDER BY L_SHIPDATE;

Esta consulta calcula a soma dos preços com desconto para itens de linha e apresenta os resultados agrupados pela data de envio e prioridade de envio. A expressão L_EXTENDEDPRICE *(1 - L_DISCOUNT) é a fórmula para o preço com desconto para um determinado item de linha. Tais fórmulas podem ser extraídas em funções para o benefício da modularidade e reutilização.

CREATE FUNCTION dbo.discount_price
(
    @price DECIMAL (12, 2),
    @discount DECIMAL (12, 2)
)
RETURNS DECIMAL (12, 2)
AS
BEGIN
    RETURN @price * (1 - @discount);
END

Agora, a consulta pode ser modificada para invocar essa UDF.

SELECT L_SHIPDATE,
       O_SHIPPRIORITY,
       SUM(dbo.discount_price(L_EXTENDEDPRICE, L_DISCOUNT))
FROM LINEITEM
     INNER JOIN ORDERS
         ON O_ORDERKEY = L_ORDERKEY
GROUP BY L_SHIPDATE, O_SHIPPRIORITY
ORDER BY L_SHIPDATE;

A consulta com o UDF funciona mal, devido às razões descritas anteriormente. Com o inlining UDF escalar, a expressão escalar no corpo do UDF é substituída diretamente na consulta. Os resultados da execução dessa consulta são mostrados na tabela a seguir:

Consulta:	Consulta sem UDF	Consulta com UDF (sem inlining)	Consulta com incorporação de DFU escalar
Prazo de execução:	1,6 segundos	29 minutos e 11 segundos	1,6 segundos

Esses números são baseados em um banco de dados CCI de 10 GB (usando o esquema TPC-H), executado em uma máquina com processador duplo (12 núcleos), 96 GB de RAM, apoiado por SSD. Os números incluem tempo de compilação e execução com um cache de procedimentos em estado frio e pool de buffers. A configuração padrão foi usada e nenhum outro índice foi criado.

B. UDF escalar com várias instruções

UDFs escalares que são implementadas usando várias instruções T-SQL, como atribuições de variáveis e ramificação condicional, também podem ser embutidas. Considere a seguinte UDF escalar que determina a categoria de serviço para um cliente, dada a sua chave de cliente. Ele chega à categoria calculando primeiro o preço total de todos os pedidos feitos pelo cliente usando uma consulta SQL. Em seguida, ele usa uma lógica IF (...) ELSE para decidir a categoria com base no preço total.

CREATE OR ALTER FUNCTION dbo.customer_category (@ckey INT)
RETURNS CHAR (10)
AS
BEGIN
    DECLARE @total_price AS DECIMAL (18, 2);
    DECLARE @category AS CHAR (10);
    SELECT @total_price = SUM(O_TOTALPRICE)
    FROM ORDERS
    WHERE O_CUSTKEY = @ckey;
    IF @total_price < 500000
        SET @category = 'REGULAR';
    ELSE
        IF @total_price < 1000000
            SET @category = 'GOLD';
        ELSE
            SET @category = 'PLATINUM';
    RETURN @category;
END

Agora, considere uma consulta que invoque esse UDF.

SELECT C_NAME,
       dbo.customer_category(C_CUSTKEY)
FROM CUSTOMER;

O plano de execução para esta consulta no SQL Server 2017 (14.x) (nível de compatibilidade 140 e anterior) é o seguinte:

Como mostra o plano, o SQL Server adota uma estratégia simples aqui: para cada tupla na tabela CUSTOMER, invoque o UDF e produza os resultados. Esta estratégia é ingénua e ineficaz. Com o inlining, essas UDFs são transformadas em subconsultas escalares equivalentes, que são substituídas na consulta que as chama no lugar da UDF.

Para a mesma consulta, o plano com o UDF integrado tem a seguinte aparência.

Como mencionado anteriormente, o plano de consulta não tem mais um operador de função definido pelo usuário, mas seus efeitos agora são observáveis no plano, como visualizações ou TVFs embutidos. Aqui estão algumas observações-chave do plano anterior:

O SQL Server infere a junção implícita entre CUSTOMER e ORDERS e a torna explícita por meio de um operador de junção.
O SQL Server também infere o GROUP BY O_CUSTKEY on ORDERS implícito e usa o IndexSpool + StreamAggregate para implementá-lo.
O SQL Server agora está usando paralelismo em todos os operadores.

Dependendo da complexidade da lógica no UDF, o plano de consulta resultante também pode ficar maior e mais complexo. Como podemos observar, as operações dentro do UDF agora não são mais opacas e, portanto, o otimizador de consulta é capaz de avaliar o custo e otimizar essas operações. Além disso, como o UDF deixa de estar no plano, a invocação iterativa do UDF passa a ser substituída por um plano que evita completamente a sobrecarga de chamada de função.

Requisitos escalares inlineáveis UDF

Um UDF T-SQL escalar pode ser embutido se a definição de função usa construções permitidas e a função é usada em um contexto que permite o inlining:

Todas as seguintes condições da definição UDF do devem ser verdadeiras:

O UDF é escrito usando as seguintes construções:
- DECLARE, SET: Declaração de variáveis e atribuições.
- SELECT: Consulta SQL com atribuições de variáveis únicas/múltiplas ¹.
- IF / ELSE: Ramificação com níveis arbitrários de nidificação.
- RETURN: Declarações de retorno únicas ou múltiplas. A partir do SQL Server 2019 (15.x) CU5, a UDF só pode conter uma única instrução RETURN a ser considerada para o inlining ⁶.
- UDF: Chamada de função aninhada/recursiva ².
- Outros: operações relacionais como EXISTS, IS NULL.
O UDF não invoca nenhuma função intrínseca que seja dependente do tempo (como GETDATE()) ou tenha efeitos colaterais ³ (como NEWSEQUENTIALID()).
O UDF usa a cláusula EXECUTE AS CALLER (comportamento padrão se a cláusula EXECUTE AS não for especificada).
O UDF não faz referência a variáveis de tabela ou parâmetros com valor de tabela.
O UDF não é compilado nativamente (interoperabilidade é suportada).
O UDF não faz referência a tipos definidos pelo usuário.
Não há assinaturas adicionadas à UDF ⁹.
O UDF não é uma função de partição.
A UDF não contém referências às Common Table Expressions (CTEs).
O UDF não contém referências a funções intrínsecas que possam alterar os resultados quando embutidas (como @@ROWCOUNT) ⁴.
O UDF não contém funções agregadas sendo passadas como parâmetros para um UDF escalar ⁴.
A UDF não faz referência a vistas internas (como OBJECT_ID) ⁴.
O UDF não faz referência a métodos XML ⁵.
A UDF não contém um SELECT com ORDER BY sem uma cláusula TOP 1⁵.
O UDF não contém uma consulta SELECT que executa uma atribuição com a cláusula ORDER BY (como SELECT @x = @x + 1 FROM table1 ORDER BY col1) ⁵.
A UDF não contém várias instruções RETURN ⁶.
O UDF não faz referência à função STRING_AGG⁶.
O UDF não faz referência a tabelas remotas ⁷.
O UDF não faz referência a colunas criptografadas ⁸.
A UDF não contém referências a WITH XMLNAMESPACES⁸.
Se a definição da UDF tiver milhares de linhas de código, o SQL Server poderá optar por não inseri-la.

¹SELECT com acumulação/agregação variável não é suportada para inclusão em linha (como SELECT @val += col1 FROM table1).

² UDFs recursivas são integradas apenas até uma certa profundidade.

³ As funções intrínsecas cujos resultados dependem da hora atual do sistema são dependentes do tempo. Uma função intrínseca que pode atualizar algum estado global interno é um exemplo de uma função com efeitos colaterais. Tais funções retornam resultados diferentes cada vez que são chamadas, com base no estado interno.

⁴ Restrição adicionada no SQL Server 2019 (15.x) CU 2

⁵ Restrição adicionada no SQL Server 2019 (15.x) CU 4

⁶ Restrição adicionada no SQL Server 2019 (15.x) no CU 5

⁷ Restrição adicionada no SQL Server 2019 (15.x) CU 6

⁸ Restrição adicionada no SQL Server 2019 (15.x) CU 11

⁹ Como as assinaturas podem ser adicionadas e descartadas depois que um UDF é criado, a decisão de inserir ou não é feita quando a consulta que faz referência a um UDF escalar é compilada. Por exemplo, as funções do sistema são normalmente assinadas com um certificado. Você pode usar sys.crypt_properties para localizar quais objetos estão assinados.

Todos os seguintes requisitos no contexto de execução do devem ser cumpridos:

Em cláusula ORDER BY, a UDF não é usada.
A consulta que invoca uma UDF escalar não faz referência a uma chamada UDF escalar em sua cláusula GROUP BY.
A consulta que invoca uma UDF escalar na sua lista de seleção com a cláusula DISTINCT não tem uma cláusula ORDER BY.
A UDF não é chamada a partir de uma instrução de RETURN ¹.
A consulta que invoca o UDF não tem expressões de tabela comuns (CTEs) ³.
A consulta de chamada UDF não usa GROUPING SETS, CUBEou ROLLUP².
A consulta de chamada UDF não contém uma variável que é usada como um parâmetro UDF para atribuição (por exemplo, SELECT @y = 2, @x = UDF(@y)) ².
O UDF não é usado em uma coluna computada ou em uma definição de restrição de verificação.

¹ Restrição adicionada no SQL Server 2019 (15.x) CU 5

² Restrição adicionada no SQL Server 2019 (15.x), CU 6

³ Restrição adicionada no SQL Server 2019 (15.x) CU 11

Para obter informações sobre as correções mais recentes de inserção em linha de UDF escalar do T-SQL e as alterações nos cenários de elegibilidade de inserção em linha, consulte o artigo da Base de Conhecimento: CORREÇÃO: problemas de inserção em linha de UDF escalar no SQL Server 2019.

Verifique se uma UDF pode ser inserida em linha

Para cada UDF escalar do T-SQL, a vista de catálogo sys.sql_modules inclui uma propriedade chamada is_inlineable, que indica se um UDF é inlineável.

A propriedade is_inlineable é derivada das construções encontradas dentro da definição UDF. Ele não verifica se o UDF é de fato inlineável em tempo de compilação. Para obter mais informações, consulte as condições para o inlining.

Um valor de 1 indica que a UDF é inlineável e 0 indica o contrário. Esta propriedade tem um valor de 1 para todos os TVFs inline também. Para todos os outros módulos, o valor é 0.

Se uma UDF escalar é susceptível de integração, isso não implica que ela esteja sempre integrada. O SQL Server decide (com base em cada consulta e em cada UDF) se vai integrar uma UDF. Consulte as listas de requisitos anteriormente neste artigo.

SELECT b.name,
       b.type_desc,
       a.is_inlineable
FROM sys.sql_modules AS a
     INNER JOIN sys.objects AS b
         ON a.object_id = b.object_id
WHERE b.type IN ('IF', 'TF', 'FN');

Verifique se o 'inlining' ocorreu

Se todas as pré-condições forem satisfeitas e o SQL Server decidir executar o inlining, ele transformará o UDF em uma expressão relacional. A partir do plano de consulta, você pode descobrir se o inlining ocorreu:

O XML do plano não tem um nó XML <UserDefinedFunction> para um UDF embutido com êxito.
Determinados Eventos Estendidos são emitidos.

Ativar a incorporação de UDF escalar

Você pode tornar as cargas de trabalho automaticamente elegíveis para inlining UDF escalar habilitando o nível de compatibilidade 150 para o banco de dados. Você pode definir isso usando Transact-SQL. Por exemplo:

ALTER DATABASE [WideWorldImportersDW]
    SET COMPATIBILITY_LEVEL = 150;

Além desta etapa, não há outras alterações necessárias nas UDFs ou consultas para aproveitar este recurso.

Desative o inlining UDF escalar sem alterar o nível de compatibilidade

A inserção de UDF escalar pode ser desabilitada ao nível do banco de dados, declaração ou escopo de UDF enquanto mantém o nível de compatibilidade do banco de dados 150 ou superior. Para desativar a incorporação de UDF escalar no âmbito da base de dados, execute a seguinte instrução no contexto da base de dados relevante:

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = OFF;

Para reativar o inlining UDF escalar para o banco de dados, execute a seguinte instrução no contexto do banco de dados aplicável:

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = ON;

Quando ON, esta configuração aparece como ativada no sys.database_scoped_configurations.

Você também pode desativar a inserção de UDF escalar para uma consulta específica designando DISABLE_TSQL_SCALAR_UDF_INLINING como uma dica de consulta USE HINT.

Uma dica de consulta USE HINT tem precedência sobre a configuração do escopo do banco de dados ou a configuração do nível de compatibilidade.

Por exemplo:

SELECT L_SHIPDATE,
       O_SHIPPRIORITY,
       SUM(dbo.discount_price(L_EXTENDEDPRICE, L_DISCOUNT))
FROM LINEITEM
     INNER JOIN ORDERS
         ON O_ORDERKEY = L_ORDERKEY
GROUP BY L_SHIPDATE, O_SHIPPRIORITY
ORDER BY L_SHIPDATE
OPTION (USE HINT('DISABLE_TSQL_SCALAR_UDF_INLINING'));

O inlining UDF escalar também pode ser desabilitado para um UDF específico usando a cláusula INLINE na instrução CREATE FUNCTION ou ALTER FUNCTION. Por exemplo:

CREATE OR ALTER FUNCTION dbo.discount_price
(
    @price DECIMAL (12, 2),
    @discount DECIMAL (12, 2)
)
RETURNS DECIMAL (12, 2)
WITH INLINE = OFF
AS
BEGIN
    RETURN @price * (1 - @discount);
END

Depois de a declaração anterior ser executada, essa UDF nunca será incorporada em nenhuma consulta que a invoque. Para reativar o inlining para este UDF, execute a seguinte instrução:

CREATE OR ALTER FUNCTION dbo.discount_price
(
    @price DECIMAL (12, 2),
    @discount DECIMAL (12, 2)
)
RETURNS DECIMAL (12, 2)
WITH INLINE = ON
AS
BEGIN
    RETURN @price * (1 - @discount);
END

A cláusula INLINE não é obrigatória. Se a cláusula INLINE não for especificada, ela será automaticamente definida como ON/OFF com base na possibilidade de a UDF ser embutida. Se INLINE = ON for especificado, mas a UDF for considerada inelegível para inlining, um erro será gerado.

Comentários

Conforme descrito neste artigo, o inlining de UDF escalar transforma uma consulta com UDFs escalares em uma consulta que utiliza uma subconsulta escalar equivalente. Devido a essa transformação, você pode notar algumas diferenças no comportamento nos seguintes cenários:

A inserção resulta em um hash de consulta diferente para o mesmo texto de consulta.
Certos avisos em declarações dentro da UDF (como divisão por zero, etc.) que antes podiam estar ocultos podem surgir devido à incorporação.
As sugestões de junção ao nível de consulta podem não ser mais válidas, pois o inlining pode introduzir novas junções. Em vez disso, devem ser usados indicadores de junção local.
As visualizações que fazem referência a UDFs escalares embutidas não podem ser indexadas. Se você precisar criar um índice nessas exibições, desative o inlining para as UDFs referenciadas.
Pode haver algumas diferenças no comportamento de de mascaramento de dados dinâmicos com o inlining UDF.

Em certas situações (dependendo da lógica no UDF), o inlining pode ser mais conservador em relação ao mascaramento de colunas de saída. Em cenários em que as colunas referenciadas numa UDF não são colunas de saída, elas não são mascaradas.
Se um UDF fizer referência a funções internas, como SCOPE_IDENTITY(), @@ROWCOUNTou @@ERROR, o valor retornado pela função interna será alterado com o inlining. Essa mudança de comportamento ocorre porque o inlining altera o escopo das instruções dentro da UDF. A partir do SQL Server 2019 (15.x) CU2, o inlining será bloqueado se o UDF fizer referência a determinadas funções intrínsecas (por exemplo, @@ROWCOUNT).
Se uma variável for atribuída com o resultado de um UDF embutido e também for usada como index_column_name nas dicas de consulta FORCESEEK,, isso resultará no erro 8622, indicando que o processador de consultas não pôde produzir um plano de consulta devido às dicas definidas na consulta.

Partilhar via

Inlining de UDF escalar

Funções escalares definidas pelo usuário do T-SQL

Desempenho de UDFs escalares

Substituição em linha automática de UDFs escalares

Exemplos

Um. Instrução única escalar UDF

B. UDF escalar com várias instruções

Requisitos escalares inlineáveis UDF

Verifique se uma UDF pode ser inserida em linha

Verifique se o 'inlining' ocorreu

Ativar a incorporação de UDF escalar

Desative o inlining UDF escalar sem alterar o nível de compatibilidade

Comentários

Comentários

Recursos adicionais

Partilhar via

Inlining de UDF escalar

Funções escalares definidas pelo usuário do T-SQL

Desempenho de UDFs escalares

Substituição em linha automática de UDFs escalares

Exemplos

Um. Instrução única escalar UDF

B. UDF escalar com várias instruções

Requisitos escalares inlineáveis UDF

Verifique se uma UDF pode ser inserida em linha

Verifique se o 'inlining' ocorreu

Ativar a incorporação de UDF escalar

Desative o inlining UDF escalar sem alterar o nível de compatibilidade

Comentários

Conteúdo relacionado

Comentários

Recursos adicionais