Inlining de UDF escalar

Aplica-se a: SQL Server 2019 (15.x) Base de Dados SQL do Azure Azure SQL Managed Instanceendpoint de Análises SQL no Microsoft FabricArmazém no Microsoft FabricBase de dados SQL no Microsoft Fabric

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

As funções definidas pelo utilizador (UDFs) que são implementadas em Transact-SQL e devolvem um único valor de dado são designadas por funções escalares T-SQL definidas pelo utilizador. 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ócio complexas, são mais fáceis de expressar em forma imperativa de UDF. Os UDFs ajudam-no a construir essa lógica sem precisar de experiência na escrita de consultas SQL. 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 apresentam, regra geral, um fraco desempenho pelas seguintes razões:

• Invocação iterativa. O SQL Database Engine invoca os UDFs iterativamente, uma vez por tuple qualificador. Este processo acrescenta custos adicionais devido à troca repetida de contexto devido à invocação de funções. As UDFs que executam consultas Transact-SQL na sua definição são gravemente afetadas.

• Falta de orçamento. Durante a otimização, o motor da base de dados calcula apenas o custo dos operadores relacionais, mas não calcula o custo dos operadores escalares. 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. Existem cenários em que o custo real é significativo, mas o otimizador continua a subestimá-lo.

• Execução interpretada. O motor da base de dados avalia os UDFs como um lote de instruções e executa-as declaração a instrução. Cada instrução é compilada e o plano compilado é armazenado em cache. Embora esta estratégia de cache poupe algum tempo ao evitar recompilações, cada instrução é executada isoladamente. O motor da base de dados não realiza otimizações cruzadas.

• 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 da funcionalidade escalar de inlining UDF é melhorar o desempenho das consultas que invocam UDFs escalares T-SQL, onde a execução UDF é o principal gargalo.

Ao utilizar a funcionalidade de inlining UDF, o motor de base de dados transforma automaticamente UDFs escalares em expressões escalares ou subconsultas escalares. O motor da base de dados substitui estas expressões ou subconsultas na consulta de chamada em substituição do operador UDF. O otimizador de consultas então otimiza essas expressões e subconsultas. Como resultado, o plano de consulta já não inclui um operador de função definida pelo utilizador, mas é possível observar os seus efeitos no plano, tal como nas vistas ou nas funções de tabela em linha (TVFs).

Inserção automática de UDFs escalares no Microsoft Fabric Data Warehouse

No Microsoft Fabric Data Warehouse, UDFs escalares (atualmente em visualização) são automaticamente integrados no momento da compilação quando o corpo da função e a consulta de chamada atendem aos requisitos para integração. Para mais informações, veja CREATE FUNCTION e inlining de UDF escalares.

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:

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

Também podes inlinear UDFs escalares com múltiplas instruções T-SQL, como atribuições de variáveis e ramificações condicionais. Considere a seguinte UDF escalar que determina a categoria de serviço para um cliente, dada a sua chave de cliente. Chega à categoria calculando primeiro o preço total de todas as encomendas feitas pelo cliente através de 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 o plano mostra, o SQL Server adota a seguinte estratégia básica: para cada tupla na CUSTOMER tabela, invoca o UDF e gera os resultados. Esta estratégia é ingénua e ineficaz. Ao usar a técnica de inlining, pode transformar essas UDFs em subconsultas escalares equivalentes, que a consulta chamadora substitui em substituição da UDF.

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

Como mencionado anteriormente, o plano de consulta deixou de ter um operador de função definida pelo utilizador, mas agora pode ver os efeitos dessa função no plano, tal como acontece com as vistas ou as TVFs inline. 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 tornar-se maior e mais complexo. Como pode ver, as operações dentro do UDF já não são opacas, pelo que o otimizador de consultas pode calcular custos 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 use como parâmetro UDF para atribuição (por exemplo, SELECT @y = 2, @x = UDF(@y)) ².
• Não se usa o UDF numa coluna calculada nem numa 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 informações sobre as correções mais recentes ao inline de UDFs escalares em T-SQL e sobre as alterações aos cenários elegíveis para inline, consulte o artigo da Knowledge Base: CORREÇÃO: problemas de inline de UDFs escalares 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

Pode desativar o inline de UDFs escalares ao nível da base de dados, da instrução ou da UDF, mantendo o nível de compatibilidade da base de dados em 150 ou superior. Para desativar a expansão inline de UDF escalar ao nível da base de dados, execute a seguinte instrução no contexto da base de dados aplicável:

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = OFF;

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

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = ON;

Quando defines esta opção para ON, 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'));

Também pode desativar o inline escalar de UDF para uma UDF específica, utilizando 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, este UDF nunca é inserido em qualquer consulta que o 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 não especificares a INLINE cláusula, ela é automaticamente definida como ON ou OFF com base em se o UDF pode ser inlineado. Se especificar INLINE = ON, mas o UDF for considerado inelegível para inline, é gerado um erro.

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 instruções dentro do UDF (como dividir por zero) que poderiam estar anteriormente ocultas podem aparecer devido ao inlining.

• 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. Deves usar dicas de entrada local em vez disso.

• Não podes indexar vistas que fazem referência a UDFs escalares em linha. 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 atribuir uma variável com o resultado de um UDF em linha e também a usar como index_column_name nas FORCESEEKdicas de consulta (Transact-SQL), resulta no erro 8622. Este erro indica que o processador de consulta não conseguiu 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)
• Performance Center para Motor de Base de Dados do SQL Server e Base de Dados SQL do Azure
• Guia de arquitetura de processamento de consultas
• Referência lógica e física do operador do showplan
• Junções (SQL Server)
• Demonstrando o processamento inteligente de consultas
• CORREÇÃO: problemas de inserção linear de UDF no SQL Server 2019