Embutimento de UDF escalar

Artigo
10/31/2024

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

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

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

Funções Definidas pelo Usuário implementadas no Transact-SQL e que retornam um valor de dados único são chamadas de Funções Definidas pelo Usuário Escalares T-SQL. UDFs T-SQL são uma maneira elegante de obter reutilização e modularidade de código em consultas Transact-SQL. Alguns cálculos (como regras de negócios complexas) são mais fáceis de expressar no formulário de UDF imperativa. UDFs ajudam na criação de uma lógica complexa sem exigir experiência em escrever consultas SQL complexas. Para saber mais sobre UDFs, consulte Criar funções definidas pelo usuário (mecanismo de banco de dados).

Desempenho de UDFs escalares

Normalmente, UDFs escalares acabam tendo um desempenho ruim devido aos seguintes motivos:

Invocação iterativa. UDFs são invocados de maneira iterativa, uma vez a cada tupla qualificada. Isso resulta em custos adicionais com a repetida comutação de contexto devido à invocação de função. Especialmente, UDFs que executam consultas Transact-SQL em sua definição são gravemente afetadas.
Falta de avaliação de custo. Durante a otimização, somente operadores relacionais terão o custo calculado, enquanto os operadores escalares não terão. Antes da introdução de UDFs escalares, outros operadores escalares geralmente eram baratos e não exigiam avaliação de custo. Um pequeno custo de CPU adicionado para uma operação de escalar foi suficiente. Há cenários em que o custo real é significativo e ainda assim permanece sub-representado.
Execução interpretada. UDFs são avaliados como um lote de instruções, executados instrução a instrução. Cada instrução em si é compilada e o plano compilado é armazenado em cache. Embora essa estratégia de armazenamento em cache economize algum tempo, pois evita recompilações, cada instrução é executada em isolamento. Nenhuma otimização entre instruções é executada.
Execução serial. O SQL Server não permite paralelismo dentro da consulta em consultas que invocam UDFs.

Embutimento automático de UDFs escalares

A meta do recurso de embutimento de UDF escalar é melhorar o desempenho de consultas que invocam UDFs escalares do T-SQL, em que a execução da UDF é o principal gargalo.

Com esse novo recurso, os UDFs escalares são automaticamente transformados em expressões escalares ou subconsultas escalares substituídas na consulta responsável pela chamada, em vez do operador UDF. Essas expressões e subconsultas então sã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 TVFs (funções embutidas com valor de tabela).

Exemplos

Os exemplos nesta seção usam o banco de dados de parâmetro de comparação TPC-H. Para obter mais informações, consulte a home page do TPC-H.

R. UDF escalar de instrução única

Considere a consulta a seguir.

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;

Essa consulta calcula a soma dos preços com desconto para itens de linha e apresenta os resultados agrupados por 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. Essas fórmulas podem ser extraídas em funções para o benefício de modularidade e da 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;

Devido aos motivos indicado anteriormente, o desempenho da consulta com UDF não é satisfatório. Com o embutimento de UDF escalar, a expressão escalar no corpo da UDF é substituída diretamente na consulta. Os resultados da execução dessa consulta são mostrados na seguinte tabela:

Consulta:	Consulta sem UDF	Consultar com UDF (sem embutimento)	Consultar com embutimento de UDF escalar
Tempo de execução:	1,6 segundo	29 minutos e 11 segundos	1,6 segundo

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

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

UDFs escalares implementadas usando várias instruções T-SQL, como atribuições de variáveis e ramificação condicional, também podem ser embutidos. Considere a seguinte UDF escalar que, dada uma chave de cliente, determina a categoria de serviço para esse cliente. Ele chega na categoria computando primeiro o preço total de todos os pedidos feitos pelo cliente usando uma consulta SQL. Então, ela 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 essa UDF.

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

O plano de execução para essa 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, invoca a UDF e produz os resultados. Essa estratégia é ingênua e ineficiente. Com embutimento, essas UDFs são transformadas em subconsultas escalares equivalentes, que são substituídas na consulta responsável pela chamada no lugar da UDF.

Para a mesma consulta, o plano com a UDF embutida se parece com o abaixo.

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

O SQL Server infere a junção implícita entre CUSTOMER e ORDERS 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 IndexSpool + StreamAggregate para implementá-lo.
O SQL Server agora está usando o paralelismo em todos os operadores.

Dependendo da complexidade da lógica na UDF, o plano de consulta resultante também poderá ficar maior e mais complexo. Como podemos ver, as operações dentro da UDF agora não são mais uma caixa opaca e, portanto, o otimizador de consulta é capaz de calcular o custo e otimizar essas operações. Além disso, uma vez que a UDF não está mais no plano, invocação da UDF iterativa é substituída por um plano que evita completamente a sobrecarga de chamada de função.

Requisitos de UDFs escalares que podem ser embutidas

Uma UDF T-SQL escalar pode ser embutida se a definição da função usar construções permitidas e a função for usada em um contexto que permite o embutimento:

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

A UDF é escrita usando as seguintes construções:
- DECLARE, SET: declaração de variável e atribuições.
- SELECT: consulta SQL com atribuições de variáveis simples/múltiplas ¹.
- IF/ELSE: ramificação com níveis arbitrários de aninhamento.
- RETURN: instruções de retorno únicas ou múltiplas. A partir do SQL Server 2019 (15.x) CU5, a UDF só pode conter uma instrução RETURN para ser considerada para embutimento ⁶.
- UDF: Chamadas de funções aninhadas/recursivas ².
- Outros: operações relacionais, como EXISTS, IS NULL.
A UDF não invoca uma função intrínseca que seja dependente do tempo (como GETDATE()) ou que tenha efeitos colaterais ³ (como NEWSEQUENTIALID()).
A UDF usa a cláusula EXECUTE AS CALLER (comportamento padrão se a cláusula EXECUTE AS não for especificada).
A UDF não faz referência a variáveis de tabela ou a parâmetros com valores de tabelas.
A UDF não é compilado nativamente (há suporte para interoperabilidade).
A UDF não faz referência a tipos definidos pelo usuário.
Não há assinaturas adicionadas à UDF ⁹.
A UDF não é uma função de partição.
A UDF não contém referências a CTEs (Expressões de Tabelas Comuns).
A UDF não contém referências a funções intrínsecas que possam alterar os resultados quando incorporadas (como @@ROWCOUNT) . ⁴.
A UDF não contém funções agregadas que estão sendo passadas como parâmetros para uma UDF escalar ⁴.
A UDF não faz referência a exibições internas (como OBJECT_ID) ⁴.
A UDF não faz referência a métodos XML ⁵.
A UDF não contém SELECT com ORDER BY sem uma cláusula TOP 1 ⁵.
A UDF não contém uma consulta SELECT que execute 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 ⁶.
A UDF não faz referência à função STRING_AGG ⁶.
A UDF não faz referência a tabelas remotas ⁷.
A 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 for executada em milhares de linhas de código, o SQL Server poderá optar por não a embutir.

¹ SELECT com acumulação/agregação de variáveis não é compatível para embutimento (como SELECT @val += col1 FROM table1).

² UDFs recursivos são embutidos em uma profundidade determinada apenas.

³ Funções intrínsecas cujos resultados dependem da hora do sistema atual são dependente de hora. Uma função intrínseca que pode atualizar algum estado global interno é um exemplo de uma função com efeitos colaterais. Essas funções retornam resultados diferentes todas as vezes 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) CU 5

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

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

9 Como as assinaturas podem ser adicionadas e descartadas após a criação de uma UDF, a decisão de inline é feita quando a consulta que faz referência a uma UDF escalar é compilada. Por exemplo, as funções do sistema normalmente são assinadas com um certificado. Você pode usar sys. crypt_properties para localizar quais objetos são assinados.

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

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

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

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

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

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

Verifique se uma UDF pode ser embutida

Para cada UDF escalar T-SQL, a exibição do catálogo sys.sql_modules inclui uma propriedade chamada is_inlineable, que indica se uma UDF é inlineable.

A propriedade is_inlineable é derivada dos constructos encontrados na definição da UDF. Ela não verifica se a UDF é de fato embutível em tempo de compilação. Para obter mais informações, confira as condições de inlining.

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

Se uma UDF escalar for embutível, isso não significa que ela sempre será embutida. O SQL Server decide (por consulta e por UDF) se uma UDF deve ser embutida. Consulte as listas de requisitos anteriores neste artigo.

SELECT *
FROM sys.crypt_properties AS cp
     INNER JOIN sys.objects AS o
         ON cp.major_id = o.object_id;

Verifique se o inlining aconteceu

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

O XML do plano não tem um nó XML <UserDefinedFunction> para uma UDF que está embutida com sucesso.
Certos Eventos Estendidos são emitidos.

Habilitar o embutimento de UDF escalar

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

ALTER DATABASE [WideWorldImportersDW]
    SET COMPATIBILITY_LEVEL = 150;

Além dessa etapa, não há nenhuma outra alteração que precise ser feita para consultas ou UDFs para aproveitar esse recurso.

Desabilitar o embutimento de UDF escalar sem alterar o nível de compatibilidade

O embutimento de UDF escalar pode ser desabilitado no escopo da UDF, da instrução ou do banco de dados enquanto ainda mantém o nível de compatibilidade do banco de dados 150 e superior. Para desabilitar o embutimento de UDF escalar no escopo do banco de dados, execute a seguinte instrução dentro do contexto do banco de dados aplicável:

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = OFF;

Para habilitar novamente o embutimento de UDF escalar para o banco de dados, execute a seguinte instrução dentro do contexto do banco de dados aplicável:

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = ON;

Quando ON, essa configuração é exibida como habilitada em sys.database_scoped_configurations.

Você também pode desabilitar o embutimento de UDF escalar de 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 no escopo do banco de dados ou a configuração de 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 embutimento de UDF escalar também pode ser desabilitado para uma UDF específica 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 que a instrução anterior é executada, essa UDF nunca será embutida em nenhuma consulta que a invoque. Para habilitar novamente o embutimento para essa 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 no fato de a UDF poder ou não ser embutida. Se INLINE = ON for especificado, mas a UDF for considerada não qualificada para embutimento, um erro será gerado.

Comentários

Conforme descrito neste artigo, o embutimento de UDF escalar transforma uma consulta com UDFs escalares em uma consulta com uma subconsulta escalar equivalente. Devido a essa transformação, os usuários podem observar algumas diferenças no comportamento nos seguintes cenários:

O embutimento resulta em um hash de consulta diferente para o mesmo texto da consulta.
Determinados avisos em instruções dentro da UDF (como divisão por zero etc.) que podem ter sido ocultados anteriormente, podem aparecer devido ao embutimento.
Dicas de junção no nível da consulta talvez não sejam válidas, pois o embutimento pode introduzir novas junções. Em vez disso, é necessário usar dicas de junção local.
Exibições que fazem referência a UDFs escalares embutidas não podem ser indexadas. Se você precisar criar um índice nessas exibições, desabilite embutimento para UDFs referenciadas.
Pode haver algumas diferenças no comportamento de Máscara Dinâmica de Dados com embutimento de UDF.

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

Compartilhar via

Embutimento de UDF escalar

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

Desempenho de UDFs escalares

Embutimento automático de UDFs escalares

Exemplos

R. UDF escalar de instrução única

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

Requisitos de UDFs escalares que podem ser embutidas

Verifique se uma UDF pode ser embutida

Verifique se o inlining aconteceu

Habilitar o embutimento de UDF escalar

Desabilitar o embutimento de UDF escalar sem alterar o nível de compatibilidade

Comentários

Comentários

Recursos adicionais

Compartilhar via

Embutimento de UDF escalar

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

Desempenho de UDFs escalares

Embutimento automático de UDFs escalares

Exemplos

R. UDF escalar de instrução única

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

Requisitos de UDFs escalares que podem ser embutidas

Verifique se uma UDF pode ser embutida

Verifique se o inlining aconteceu

Habilitar o embutimento de UDF escalar

Desabilitar o embutimento de UDF escalar sem alterar o nível de compatibilidade

Comentários

Conteúdo relacionado

Comentários

Recursos adicionais