• 08/04/2020
• 12 minutos para ler

>li>>ul>>li> s

• M
• M
• p
• j
• +6

/li> /li>

p> aplica a: SQL Server (todas as versões suportadas)

Este artigo introduz o Scalar UDF Inlining, uma característica do conjunto de características do Intelligent Query Processing. Esta funcionalidade melhora o desempenho das consultas que invocam UDFs escalares no SQL Server (começando com SQL Server 2019 (15.x)).

T-SQL scalar User-Defined Functions

User-Defined Functions (UDFs) que são implementadas no Transact-SQL e retornam um único valor de dados são referidas como T-SQL Scalar User-Defined Functions. Os T-SQL UDFs são uma forma elegante de conseguir a reutilização do código e a modularidade em todas as consultas Transact-SQL. Alguns cálculos (tais como regras comerciais complexas) são mais fáceis de expressar em forma UDF imperativa. Os UDFs ajudam a construir uma lógica complexa sem exigir perícia na escrita de consultas SQL complexas. Para mais informações sobre UDFs, ver Criar Funções Definidas pelo Utilizador (Database Engine).

Desempenho dos UDFs escalares

UDFs escalares acabam tipicamente por ter um mau desempenho devido às seguintes razões:

• Invocação iterativa: Os UDFs são invocados de uma forma iterativa, uma vez por tuple qualificador. Isto incorre em custos adicionais de mudança repetida de contexto devido à invocação de função. Especialmente, os UDFs que executam consultas Transact-SQL na sua definição são severamente afectados.

• Falta de cálculo de custos: Durante a optimização, apenas os operadores relacionais são avaliados, enquanto que os operadores escalares não o são. Antes da introdução dos UDFs escalares, outros operadores escalares eram geralmente baratos e não exigiam o cálculo de custos. Um pequeno custo de CPU adicionado para uma operação escalar era suficiente. Há cenários em que o custo real é significativo, e ainda assim permanece subrepresentado.

• p>Execução interpretada: Os UDFs são avaliados como um lote de declarações, declarações-executadas-executadas-em-exercício. Cada declaração em si é compilada, e o plano compilado é colocado em cache. Embora esta estratégia de cache poupe algum tempo, uma vez que evita as recompilações, cada declaração é executada isoladamente. Não são efectuadas optimizações de declarações cruzadas.

• Execução em série: O SQL Server não permite paralelismo intra-query em consultas que invoquem UDFs.

Automatic inlining of scalar UDFs

O objectivo da funcionalidade de inlining UDF escalar é melhorar o desempenho das consultas que invoquem UDFs escalar T-SQL, onde a execução UDF é o principal ponto de estrangulamento.

Com esta nova funcionalidade, os UDFs escalares são automaticamente transformados em expressões escalares ou subconsultas escalares que são substituídas na consulta de chamada no lugar do operador UDF. Estas expressões e subconsultas são então optimizadas. Como resultado, o plano de consulta já não terá um operador de função definido pelo utilizador, mas os seus efeitos serão observados no plano, como vistas ou TVFs em linha.

Exemplo 1 – Declaração única UDF escalar

Considerar a seguinte consulta.

SELECT L_SHIPDATE, O_SHIPPRIORITY, SUM (L_EXTENDEDPRICE *(1 - L_DISCOUNT)) FROM LINEITEMINNER 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 os itens da linha e apresenta os resultados agrupados pela data de expedição e prioridade de expedição. A expressão L_EXTENDEDPRICE *(1 - L_DISCOUNT) é a fórmula para o preço com desconto para um determinado artigo. Tais fórmulas podem ser extraídas em funções para benefício da modularidade e reutilização.

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

Agora a consulta pode ser modificada para invocar este UDF.

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

P>Dev>Dev>Dev>As razões anteriormente delineadas, a consulta com o UDF tem um mau desempenho. Agora, com o UDF em linha escalar, a expressão escalar no corpo do UDF é substituída directamente na consulta. Os resultados da execução desta consulta são mostrados na tabela abaixo:

>query without UDF

>query with scalar UDF inlining

Query:	Query with UDF (without inlining)
Tempo de execução:	1.6 segundos	29 minutos 11 segundos	1,6 segundos

Estes números são baseados numa base de dados CCI de 10-GB (utilizando o esquema TPC-H), funcionando numa máquina com duplo processador (12 núcleo), 96-GB RAM, suportado por SSD. Os números incluem a compilação e o tempo de execução com um cache de procedimentos frios e um buffer pool. A configuração padrão foi utilizada, e nenhum outro índice foi criado.

Exemplo 2 – Multi-statement scalar UDF

Scalar UDFs que são implementados usando múltiplas declarações T-SQL, tais como atribuições de variáveis e ramificações condicionais, também podem ser simplificadas. Considere-se o seguinte UDF escalar que, dada uma chave de cliente, determina a categoria de serviço para esse cliente. Chega à categoria através do primeiro cálculo do preço total de todas as encomendas colocadas pelo cliente utilizando uma consulta SQL. Depois, utiliza uma IF (...) ELSE lógica para decidir a categoria com base no preço total.

CREATE OR ALTER FUNCTION dbo.customer_category(@ckey INT) RETURNS CHAR(10) ASBEGIN 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 esta 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 adopta aqui uma estratégia simples: para cada tuple na tabela CUSTOMER, invocar o UDF e emitir os resultados. Esta estratégia é ingénua e ineficiente. Com a inlining, tais UDFs são transformados em subconsultas escalares equivalentes, que são substituídas na consulta de chamada em vez do UDF.

Para a mesma consulta, o plano com o UDF inlined tem o aspecto abaixo.

Como mencionado anteriormente, o plano de consulta já não tem um operador de função definido pelo utilizador, mas os seus efeitos são agora observáveis no plano, como vistas ou TVFs em linha. Aqui estão algumas observações chave do plano acima mencionado:

• SQL Server inferiu a junção implícita entre CUSTOMER e ORDERS e tornou isso explícito através de um operador de junção.
• SQL Server também inferiu o implícito GROUP BY O_CUSTKEY on ORDERS e utilizou o IndexSpool + StreamAggregate para o implementar.
• SQL Server está agora a utilizar paralelismo em todos os operadores.

Dependente da complexidade da lógica no UDF, o plano de consulta resultante pode também tornar-se maior e mais complexo. Como podemos ver, as operações dentro do UDF já não são uma caixa opaca e, por conseguinte, o optimizador de consultas é capaz de calcular os custos e optimizar essas operações. Além disso, uma vez que o UDF já não está no plano, a invocação iterativa do UDF é substituída por um plano que evita completamente a sobrecarga da chamada de funções.

Requisitos do UDF escalar em linha

Um UDF escalar T-SQL UDF pode ser alinhado se todas as seguintes condições forem verdadeiras:

• O UDF é escrito usando as seguintes construções:
  • DECLARESET: Declaração de variáveis e atribuições.
  • SELECT: Consulta SQL com atribuições de variáveis simples/múltiplas 1.
  • IFELSE: Ramificação com níveis arbitrários de nidificação.
  • RETURN: Declarações de retorno simples ou múltiplas.
  • UDF: Chamadas de função aninhadas/recursivas 2.
  • Outros: Operações relacionais tais como EXISTSISNULL.
• O UDF não invoca qualquer função intrínseca que seja dependente do tempo (tal como GETDATE()) ou que tenha efeitos secundários 3 (tal 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 valorizados em tabela.
• A consulta invocando um UDF escalar não faz referência a uma chamada UDF escalar no seu GROUP BY cláusula.
• A consulta que invoca uma UDF escalar na sua lista seleccionada com DISTINCT cláusula não tem ORDER BY cláusula.
• O UDF não é utilizado em ORDER BY clause.
• O UDF não é compilado nativamente (interop é suportado).
• O UDF não é utilizado numa coluna computorizada ou numa definição de restrição de verificação.
• O UDF não faz referência a tipos definidos pelo utilizador.
• Não há assinaturas adicionadas ao UDF.
• O UDF não é uma função de partição.
• O UDF não contém referências a Expressões de Tabela Comum (CTEs).
• O UDF não contém referências a funções intrínsecas que possam alterar os resultados quando alinhadas (tais como @@ROWCOUNT) 4.
• O UDF não contém funções agregadas a serem passadas como parâmetros para um UDF escalar 4.
• O UDF não faz referência a vistas embutidas (tais como OBJECT_ID) 4.
• O UDF não faz referência a métodos XML 5.
• O UDF não contém um SELECT com ORDER BY sem um TOP 1 cláusula 5.
• O UDF não contém uma consulta SELECT que realiza uma atribuição em conjunto com a cláusula ORDER BY (tal como SELECT @x = @x + 1 FROM table1 ORDER BY col1) 5.
• O UDF não contém múltiplas declarações RETURN 6.
• O UDF não é chamado a partir de uma declaração RETURN 6.
• O UDF não faz referência ao STRING_AGG função 6.
• O UDF não faz referência às tabelas remotas 7.
• A consulta de chamada UDF não utiliza GROUPING SETSCUBE, ou ROLLUP
• A consulta de chamada UDF não contém uma variável que é usada como parâmetro UDF para atribuição (por exemplo, SELECT @y = 2@x = UDF(@y))7.

1 SELECT com acumulação/agregação variável não é suportado para inlining (tal como SELECT @val += col1 FROM table1).

2 UDFs recursivos serão inlined a uma certa profundidade apenas.

3 Funções intrínsecas cujos resultados dependem do tempo actual do sistema. Uma função intrínseca que pode actualizar algum estado global interno é um exemplo de uma função com efeitos secundários. Tais funções retornam resultados diferentes de cada vez que são chamadas, com base no estado interno.

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

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

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

7 Restrição adicionada no SQL Server 2019 (15.x) CU6

Verificando se um UDF pode ou não ser inlined

Para cada T-SQL UDF escalar, o sistema.sql_modules catalog view inclui uma propriedade chamada is_inlineable, que indica se um UDF é ou não inlineable.

Um valor de 1 indica que é inlineable, e 0 indica o contrário. Esta propriedade terá um valor de 1 para todas as TVFs em linha também. Para todos os outros módulos, o valor será 0,

Se um UDF escalar for inlineable, não implica que será sempre inlineable. O SQL Server decidirá (numa base de per-query, por-UDF) se vai ou não inlinear um UDF. Alguns exemplos de quando um UDF pode não ser alinhado incluem:

• se a definição UDF correr para milhares de linhas de código, o SQL Server pode optar por não o alinhar.

• p>uma invocação UDF numa cláusula GROUP BY não será alinhada. Esta decisão é tomada quando a consulta referenciando um UDF escalar é compilada.

• se o UDF for assinado com um certificado. Uma vez que as assinaturas podem ser adicionadas e retiradas após a criação de um UDF, a decisão de inline ou não é tomada quando a consulta de referência a um UDF escalar é compilada. Por exemplo, as funções do sistema são tipicamente assinadas com um certificado. Pode utilizar sys.crypt_properties para encontrar quais os objectos que são assinados.

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

Verificando se o inlining aconteceu ou não

Se todas as condições prévias forem satisfeitas e o SQL Server decidir executar o inlining, transforma o UDF numa expressão relacional. A partir do plano de consulta, é fácil descobrir se o inlining aconteceu ou não:

• O plano xml não terá um <UserDefinedFunction> nó xml para um UDF que tenha sido inlined com sucesso.
• Certos XEvents são emitidos.

Enabling Scalar UDF Inlining

P>Pode tornar as cargas de trabalho automaticamente elegíveis para o Scalar UDF Inlining ao permitir o nível de compatibilidade 150 para a base de dados. Pode definir isto usando Transact-SQL. Por exemplo:

ALTER DATABASE SET COMPATIBILITY_LEVEL = 150;

Parte disto, não são necessárias outras alterações aos UDFs ou consultas para tirar partido desta funcionalidade.

Desactivar o Scalar UDF Inlining sem alterar o nível de compatibilidade

Scalar UDF Inlining pode ser desactivado na base de dados, declaração, ou âmbito UDF mantendo o nível de compatibilidade da base de dados 150 e superior. Para desactivar o UDF inlining escalar no âmbito da base de dados, executar a seguinte declaração dentro do contexto da base de dados aplicável:

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = OFF;

Para voltar a activar o UDF Inlining escalar para a base de dados, executar a seguinte declaração no contexto da base de dados aplicável:

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = ON;

Quando ON, esta configuração aparecerá como activada em sys.database_scoped_configurations.Também pode desactivar o Scalar UDF Inlining para uma consulta específica designando DISABLE_TSQL_SCALAR_UDF_INLINING como USE HINT dica de consulta. Por exemplo:

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

Scalar UDF Inlining também pode ser desactivado para um UDF específico utilizando a cláusula INLINE na declaraçã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 = OFFASBEGIN RETURN @price * (1 - @discount);END;

Após a declaração acima ter sido executada, esta UDF nunca será incluída em qualquer consulta que a invoque. Para voltar a activar o sublinhado para este UDF, executar a seguinte declaração:

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

Notas Importantes

Como descrito neste artigo, o inlining do UDF escalar transforma uma consulta com UDFs escalares numa consulta com uma subconsulta escalar equivalente. Devido a esta transformação, os utilizadores podem notar algumas diferenças de comportamento nos seguintes cenários:

1. Inlining resultará num hash de consulta diferente para o mesmo texto de consulta.
2. alguns avisos em declarações dentro do UDF (tais como dividir por zero, etc.) que podem ter sido escondidos anteriormente, podem aparecer devido ao inlining.
3. As dicas de junção de nível de consulta podem já não ser válidas, uma vez que o inlining pode introduzir novas junções. As dicas de junção locais terão de ser utilizadas em vez disso.
4. Vistas que os UDFs escalares de referência em linha não podem ser indexados. Se for necessário criar um índice em tais vistas, desactivar o inlining para os UDFs referenciados.
5. Tal poderá haver algumas diferenças no comportamento do mascaramento de Dados Dinâmicos com inlining UDF.Em certas situações (dependendo da lógica no UDF), o inlining poderá ser mais conservador w.r.t mascarando colunas de saída. Em cenários em que as colunas referenciadas num UDF não são colunas de saída, não serão mascaradas.
6. se um UDF referir funções incorporadas tais como SCOPE_IDENTITY()@@ROWCOUNT, ou @@ERROR, o valor retornado pela função incorporada será alterado com o inlining. Esta alteração de comportamento deve-se ao facto de o inlining alterar o âmbito das declarações dentro do UDF. A partir do SQL Server 2019 (15.x) CU2, o inlining é bloqueado se o UDF fizer referência a certas funções intrínsecas (por exemplo @@ROWCOUNT).

See Also