Embutimento de UDF escalar

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

Este artigo apresenta o embutimento de UDF escalar, um recurso sob o conjunto de recursos de Processamento de Consulta Inteligente. Esse recurso aprimora o desempenho das consultas que invocam UDFs escalares em SQL Server (começando com SQL Server 2019 (15.x)).

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

User-Defined Functions (UDFs) que são implementados no Transact-SQL e retornam um único valor de dados são conhecidos como T-SQL Scalar User-Defined Functions. 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, confira 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 repetido de comutação de contexto repetida devido à invocação de função. Especialmente, as UDFs que executam consultas Transact-SQL em sua definição são severamente afetadas.

• Falta de custo. Durante a otimização, apenas os operadores relacionais são custeados, enquanto os operadores escalares não são. Antes da introdução de UDFs escalares, outros operadores escalares eram geralmente baratos e não exigiam 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 em série. SQL Server não permite paralelismo intra-consulta em consultas que invocam UDFs.

Embutimento automático de UDFs escalares

O objetivo do recurso de inlining do UDF Escalar é melhorar o desempenho de consultas que invocam UDFs escalares T-SQL, em que a execução de 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 terá mais um operador de função definido pelo usuário, mas seus efeitos serão observados no plano, como modos de exibição ou TVFs embutidos.

Exemplo 1 – 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 a motivos descritos anteriormente, a consulta com a UDF tem um mau desempenho. Agora, com o Dimensionamento de UDF, a expressão escalar no corpo da UDF é substituída diretamente na consulta. Os resultados da execução dessa consulta são mostrados na tabela abaixo:

Consulta:	Consulta sem UDF	Consultar com UDF (sem embutimento)	Consulta com inlining 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.

Exemplo 2 – 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 DECIMAL(18,2);
  DECLARE @category 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 de acima:

• O SQL Server inferiu a junção implícita entre CUSTOMER e ORDERS e tornou isso explícito por meio de um operador de junção.
• O SQL Server também inferiu o GROUP BY O_CUSTKEY on ORDERS implícito e usou 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 opacas e, portanto, o otimizador de consulta é capaz de custar 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 escalonáveis de UDF escalonáveis

Uma UDF T-SQL escalar poderá ser embutida se todas as seguintes condições forem 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 variáveis únicas/múltiplas ¹.
  • IF/ELSE: Ramificação com níveis arbitrários de aninhamento.
  • RETURN: Instruções de retorno únicas ou múltiplas. Do SQL Server 2019 (15.x) CU5 em diante, a UDF pode conter apenas uma instrução RETURN a ser considerada para o inlining ⁶.
  • UDF: Chamadas de função aninhadas/recursivas ².
  • Outros: Operações relacionais, como EXISTS, ISNULL.
• A 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 EXECUTE AS CALLER cláusula (comportamento padrão se a EXECUTE AS cláusula não for especificada).
• O UDF não faz referência a variáveis de tabela ou parâmetros com valor de tabela.
• A consulta que invoca uma UDF escalar não faz referência a uma chamada escalar UDF em sua GROUP BY cláusula.
• A consulta que invoca um UDF escalar em sua lista de seleção com DISTINCT cláusula não tem ORDER BY cláusula.
• A UDF não é usada na ORDER BY cláusula.
• O UDF não é compilado nativamente (há suporte para interoperabilidade).
• A UDF não é usada em uma coluna computada ou em uma definição de restrição de verificação.
• 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 Tabela Comuns).
• A UDF não contém referências a funções intrínsecas que podem alterar os resultados quando embutidas (como @@ROWCOUNT) ⁴.
• A UDF não contém funções de agregação que estão sendo passadas como parâmetros para um UDF ⁴ escalar.
• O UDF não faz referência a exibições internas (como OBJECT_ID) ⁴.
• A UDF não faz referência aos métodos XML ⁵.
• O UDF não contém um SELECT sem ORDER BY uma TOP 1 cláusula ⁵.
• A UDF não contém uma consulta SELECT que executa uma atribuição com a ORDER BY cláusula (como SELECT @x = @x + 1 FROM table1 ORDER BY col1) ⁵.
• O UDF não contém várias instruções RETURN ⁶.
• A UDF não é chamada de uma instrução RETURN ⁶.
• A UDF não faz referência à STRING_AGG função ⁶.
• A UDF não faz referência às tabelas remotas ⁷.
• 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)⁷.
• A UDF não faz referência às colunas criptografadas ⁸.
• A UDF não contém referências a WITH XMLNAMESPACES⁸.
• A consulta que invoca o UDF não tem CTEs (Expressões de Tabela Comuns) ⁸.

¹SELECT com acumulação/agregação variável não tem suporte para inlining (por SELECT @val += col1 FROM table1exemplo).

² UDFs recursivos serã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 cada vez que são chamadas, com base no estado interno.

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

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

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

⁷ Restrição adicionada ao SQL Server 2019 (15.x) CU6

⁸ Restrição adicionada à CU11 do SQL Server 2019 (15.x)

Para obter informações sobre os consertos (fixes) de Embutimento de UDF Escalar do T-SQL mais recentes e alterações nos cenários de qualificação de embutimento, confira o artigo da Base de Dados de Conhecimento: CORREÇÃO: Problemas de Embutimento de UDF Escalar no SQL Server 2019.

Verifique se um UDF pode ou não ser inlined

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 uma UDF pode ser embutida ou não.

A propriedade is_inlineable é derivada dos constructos encontrados na definição da UDF. Ele não verifica se a UDF está de fato embutida no momento da compilação. Para obter mais informações, confira as condições de inlining.

Um valor de 1 indica que ele é pode ser embutido e 0 indica o contrário. Essa propriedade terá um valor de 1 para todos as TVFs embutidos também. Para todos os outros módulos, o valor será 0.

Se um UDF escalar for embutido, isso não implica que ele sempre será embutido. O SQL Server decidirá qual (por consulta, por UDF) se embutirá uma UDF ou não. Alguns exemplos de quando um UDF não pode ser embutido incluem:

• Se a definição da UDF for executada em milhares de linhas de código, o SQL Server poderá optar por não embuti-la.

• Uma invocação UDF em uma GROUP BY cláusula não será embutida. Essa decisão é tomada quando a consulta que referencia uma UDF escalar é compilada.

• Se a UDF for assinada com um certificado. Como as assinaturas podem ser adicionadas e descartadas após a criação de uma UDF, a decisão de ficar embutida ou não é feita quando a consulta que referencia 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.

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

Verifique se a inlining aconteceu ou não

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, é fácil descobrir se a inlining aconteceu ou não:

• O plano xml não terá um <UserDefinedFunction> nó xml para um UDF que foi inlinado com êxito.
• Determinados XEvents são emitidos.

Habilitar o inlining do UDF escalar

Você pode qualificar automaticamente as cargas de trabalho 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 disso, não há nenhuma outra alteração que precise ser feita para consultas ou UDFs para aproveitar esse recurso.

Desabilitar o inlining do UDF Escalar sem alterar o nível de compatibilidade

O dimensionamento de UDF pode ser desabilitado no escopo de banco de dados, instrução ou UDF, mantendo o nível de compatibilidade do banco de dados 150 e superior. Para desabilitar o inlining do 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 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 LIGADA, essa configuração aparecerá 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 declaração acima é 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 INLINE cláusula não é obrigatória. Se INLINE a cláusula não for especificada, ela será definida automaticamente com ON/OFF base em se a UDF pode ser inlined. Se INLINE = ON for especificado, mas a UDF for considerada não qualificada para embutimento, um erro será gerado.

Observações importantes

Conforme descrito neste artigo, a Inlining escalar UDF 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:

1. O embutimento resultará em um hash de consulta diferente para o mesmo texto da consulta.

2. Determinados avisos em instruções dentro da UDF (como divisão por zero etc.) que podem ter sido ocultados anteriormente, pode aparecer devido ao embutimento.

3. Dicas de junção no nível da consulta talvez não sejam válidas, pois o embutimento pode introduzir novas junções. Dicas de junção local precisarão ser usadas em vez disso.

4. As 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.

5. 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 na UDF), o sublinhado pode ser mais conservador em relação ao mascaramento de colunas de saída. Em cenários em que as colunas referenciadas em uma UDF não são colunas de saída, elas não serão mascaradas.

6. Se uma UDF referenciar funções internas, como SCOPE_IDENTITY(), @@ROWCOUNT ou @@ERROR, o valor retornado pela função interna será alterado com o inlining. Essa alteração no comportamento ocorre porque o embutimento altera o escopo das instruções dentro da UDF. Começando no SQL Server 2019 (15.x) CU2, embutimento foi bloqueado quando a UDF faz referência a determinadas funções intrínsecas (por exemplo, @@ROWCOUNT).

7. Se uma variável for atribuída com o resultado de um UDF embutido e também usada como index_column_name na dica de consulta FORCESEEK, isso resultará em erro Msg 8622 indicando que o processador de consulta não pôde produzir um plano de consulta devido às dicas definidas na consulta.

Confira também

• 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 físicos e lógicos de plano de execução
• Junções
• Demonstrar o processamento de consulta inteligente
• CORREÇÃO: Problemas de Embutimento de UDF Escalar no SQL Server 2019