Embutimento de UDF escalar

Aplica-se a: SQL Server 2019 (15.x) Banco de Dados SQL do AzureInstância Gerenciada de SQL do AzurePonto de extremidade de análise de SQL no Microsoft FabricArmazém no Microsoft FabricBanco de Dados SQL no Microsoft Fabric

Este artigo apresenta o embutimento de UDF escalar, um recurso sob o conjunto de recursos de Processamento de consulta inteligente em bancos de dados SQL. Esse recurso aprimora 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

UDFs (funções definidas pelo usuário) que são implementadas em Transact-SQL e retornam um único valor de dados são conhecidas como funções escalares T-SQL definidas pelo usuário. 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 UDF imperativo. As UDFs ajudam você a criar essa lógica sem exigir experiência na gravação de consultas SQL. Para saber mais sobre UDFs, consulte Criar funções definidas pelo usuário (mecanismo de banco de dados).

Desempenho de UDFs escalares

UDFs escalares normalmente apresentam desempenho ruim pelas seguintes razões:

• Invocação iterativa. O SQL Mecanismo de Banco de Dados chama UDFs iterativamente, uma vez para cada tupla qualificada. Esse processo adiciona um custo extra devido à alternância de contexto repetida devido à invocação de função. As UDFs que executam consultas Transact-SQL em sua definição são severamente afetadas.

• Falta de avaliação de custo. Durante a otimização, o mecanismo do banco de dados estima o custo apenas dos operadores relacionais, mas não estima o custo dos operadores escalares. Antes da introdução de UDFs escalares, outros operadores escalares eram geralmente baratos e não exigiam custos. Um pequeno custo de CPU adicionado para uma operação de escalar foi suficiente. Há cenários em que o custo real é significativo, mas o otimizador ainda o sub-representa.

• Execução interpretada. O mecanismo de banco de dados avalia UDFs como um lote de instruções e executa-as instrução por instrução. Cada instrução é compilada e o plano compilado é armazenado em cache. Embora essa estratégia de cache economize algum tempo evitando recompilações, cada instrução é executada isoladamente. O mecanismo de banco de dados não realiza otimizações entre instruções.

• Execução serial. O SQL Server não permite paralelismo dentro da consulta em consultas que invocam UDFs.

Embutimento automático de UDFs escalares

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

Ao usar o recurso de embutimento de UDF, o mecanismo do banco de dados transforma automaticamente UDFs escalares em expressões escalares ou subconsultas escalares. O mecanismo de banco de dados substitui essas expressões ou subconsultas na consulta de chamada no lugar do operador UDF. O otimizador de consulta otimiza essas expressões e subconsultas. Como resultado, o plano de consulta não tem mais um operador de função definido pelo usuário, mas você pode observar seus efeitos no plano, como exibições ou TVFs (funções com valor de tabela embutida).

Embutimento automático de UDFs escalares no Data Warehouse do Microsoft Fabric

No Data Warehouse do Microsoft Fabric, as UDFs escalares (atualmente em versão preliminar) são automaticamente substituídas no momento de compilação quando o corpo da função e a consulta de chamada atendem aos requisitos para embutimento. Para obter mais informações, consulte CREATE FUNCTION e o sublinhado da UDF escalar.

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 Homepage do TPC-H.

A. 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;

A consulta com o UDF tem um desempenho insatisfatório devido aos motivos descritos anteriormente. Com o embutimento de 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 seguinte tabela:

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

Você também pode colocar UDFs escalares em linha com várias instruções T-SQL, como atribuições de variáveis e desvios condicionais. Considere a seguinte UDF escalar que, dada uma chave de cliente, determina a categoria de serviço para esse cliente. Ele chega à categoria calculando 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 a seguinte estratégia básica: para cada tupla na tabela CUSTOMER, chame a UDF e gere os resultados. Essa estratégia é ingênua e ineficiente. Ao usar inline, você pode transformar essas UDFs em subconsultas escalares equivalentes, que a consulta chamadora substitui no lugar da UDF.

No caso da mesma consulta, o plano com o UDF embutido fica assim:

Conforme mencionado anteriormente, o plano de consulta não tem mais um operador de função definido pelo usuário, mas agora você pode ver seus efeitos no plano, como exibições ou TVFs embutidas. Veja a seguir algumas observações importantes 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 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 pode ficar maior e mais complexo. Como você pode ver, as operações dentro da UDF deixaram de ser opacas, de modo que o otimizador de consulta pode estimar 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

Um UDF T-SQL escalar pode ser embutido 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 ao 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).
• O 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

⁹ Como assinaturas podem ser adicionadas e removidas após a criação de um UDF, a decisão de embutir ou não é tomada quando a consulta que faz referência a um 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 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 a partir de uma instrução RETURN ¹.
• A consulta que invoca o UDF não tem expressões de tabelas comuns (CTEs) ³.
• A consulta que chama a UDF não usa GROUPING SETS, CUBE ou ROLLUP².
• A consulta de chamada UDF não contém uma variável que você usa como um parâmetro UDF para atribuição (por exemplo, SELECT @y = 2, @x = UDF(@y)) ².
• Você não deve usar a UDF em uma coluna calculada nem em uma definição de restrição CHECK.

¹ 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 e alterações mais recentes relacionadas ao inlining de UDF escalar de T-SQL e aos cenários de elegibilidade para inlining, consulte o artigo da Base de Conhecimento: CORREÇÃO: problemas de inlining de UDF escalar no SQL Server 2019.

Verificar se um UDF pode ser embutido

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

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 o UDF pode ser embutido 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 um UDF escalar puder ser embutido, isso não significará que ele sempre será embutido. O SQL Server decide (por consulta e por UDF) se um UDF deve ser embutido. Consulte as listas de requisitos anteriores 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');

Verificar se ocorreu o embutimento

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 um UDF que está embutido com sucesso.
• Determinados 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

Você pode desabilitar o sublinhado escalar UDF no escopo de banco de dados, instrução ou UDF, mantendo ainda o nível de compatibilidade do banco de dados 150 e superior. Para desabilitar o sublinhado de UDF escalar no escopo do 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 = OFF;

Para reativar o inlining de UDF escalar no 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 você define essa opção como ON, ela aparece como habilitada no 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'));

Você também pode desativar o inlining de UDF escalar 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 de executar a instrução anterior, essa UDF nunca será embutida em nenhuma consulta que a invoque. Para reativar o inline para esta 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 você não especificar a cláusula INLINE, ela será automaticamente definida como ON ou OFF, dependendo de a UDF poder ser embutida. Se você especificar INLINE = ON, mas a UDF for considerada inelegível para inlining, será emitido um erro.

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 declarações dentro da UDF (como divisão por zero), que antes podiam ficar ocultos, podem aparecer devido ao inline.

• 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, você deve usar dicas locais de junção.

• Não é possível indexar exibições que fazem referência a UDFs escalares embutidas. Se você precisar criar um índice nessas exibições, desabilite embutimento para UDFs referenciadas.

• Pode haver algumas diferenças no comportamento de Máscara de dados dinâmicos 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 um UDF não são colunas de saída, elas não serão mascaradas.

• Se um 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 você atribuir uma variável com o resultado de uma UDF embutida e também usá-la como index_column_name em FORCESEEKDicas de Consulta (Transact-SQL), ocorrerá o erro 8622. Esse erro indica que o processador de consultas não pôde produzir um plano de consulta devido às dicas definidas na consulta.

Conteúdo relacionado

• Criar funções definidas pelo usuário (mecanismo de banco de dados)
• Central de desempenho do Mecanismo de Banco de Dados do SQL Server e do Banco de Dados SQL do Azure
• Guia de arquitetura de processamento de consultas
• Referência de operadores lógicos e físicos do plano de execução
• Junções (SQL Server)
• Demonstrar o processamento de consulta inteligente
• CORREÇÃO: problemas com embutimento de UDF escalar no SQL Server 2019