Partilhar via

  • Artigo

Solucionando problemas de baixo de desempenho de consulta: dobra constante e avaliação de expressão durante estimativa de cardinalidade

O SQL Server avalia algumas expressões constantes antecipadamente para melhorar o desempenho de consulta. Isto é chamado de dobra constante. Uma constante é um literal Transact-SQL, como 3, 'ABC', '2005-12-31', 1.0e3 ou 0x12345678.

Expressões dobráveis

O SQL Server usa a dobra constante com os seguintes tipos de expressões:

  • Expressões aritméticas, como 1+1, 5/3*2, que contêm apenas constantes.

  • Expressões lógicas, como 1=1 e 1>2 AND 3>4, que contêm apenas constantes.

  • Funções internas consideradas dobráveis pelo SQL Server, inclusive CAST e CONVERT. Geralmente, uma função intrínseca será dobrável se for uma função de suas entradas apenas e não outras informações contextuais, como opções SET, configurações de idioma, opções de banco de dados e chaves de codificação. Funções não-determinísticas não são dobráveis. Funções internas determinísticas são dobráveis, com algumas exceções.

Há uma exceção para tipos de objeto grandes. Se o tipo de saída do processo de dobra for um tipo de objeto grande (text, image, nvarchar(max), **varchar (max)**ou varbinary(max)), então SQL Server não dobra a expressão.

Expressões não-dobráveis

Todos os outros tipos de expressão são não-dobráveis. Especificamente, os tipos seguintes de expressões não são dobráveis:

  • Expressões não-constantes, cujo resultado depende do valor de uma coluna.

  • Expressões cujos resultados dependem de um variável local ou parâmetro, como @x.

  • Funções não-determinísticas.

  • Funções definidas pelo usuário (ambos Transact-SQL e CLR).

  • Expressões cujos resultados dependem de configurações de idioma.

  • Expressões cujos resultados dependem de opções SET.

  • Expressões cujos resultados dependem de opções de configuração do servidor.

Exemplos expressões de constantes desdobráveis e não-desdobráveis

Considere a consulta a seguir.

SELECT *
FROM Sales.SalesOrderHeader s JOIN sales.SalesOrderDetail d 
ON s.SalesOrderID = d.SalesOrderID
WHERE TotalDue > 117.00 + 1000.00

Se a opção de banco de dados PARAMETERIZATION não for definida como FORCED para esta consulta (o padrão é SIMPLE), então a expressão 117.00 + 1000.00 será avaliada e substituída por seu resultado, 1117.00, antes que a consulta seja compilada. Os benefícios da dobra constante incluem o seguinte:

  • A expressão não precisa ser avaliada repetidamente em tempo de execução.

  • O valor da expressão depois de avaliado é usado pelo otimizador de consulta para estimar o tamanho do conjunto de resultados da porção da consulta TotalDue > 117.00 + 1000.00.

Por outro lado, se dbo.f for uma função escalar definida pelo usuário, a expressão dbo.f(100) não será dobrada, porque SQL Server não dobra expressões que envolvem funções definidas pelo usuário, mesmo se forem determinísticas.

Avaliação da expressão em tempo de compilação para estimativa da cardinalidade

Além disso, algumas expressões que não são constantes desdobráveis, mas cujos argumentos são conhecidos no tempo de compilação, sejam esses argumentos parâmetros ou constantes, são avaliadas pelo avaliador de tamanho do conjunto de resultados (cardinalidade) que é parte do otimizador durante a otimização.

Especificamente, serão avaliados as seguintes funções internas e operadores especiais em tempo de compilação se todas as suas entradas forem conhecidas: UPPER, LOWER, RTRIM, DATEPART (apenas YY), GETDATE, CAST e CONVERT.

Os seguintes operadores também serão avaliados em tempo de compilação se todas as suas entradas forem conhecidas:

  • Operadores aritméticos: +, -, *, /, unary -,

  • Operadores lógicos: AND, OR, NOT

  • Operadores de comparação: <, >, <=, >=, <>, LIKE, IS NULL, IS NOT NULL

Nenhuma outra função ou operador será avaliada pelo otimizador durante a estimativa de cardinalidade.

Exemplos avaliação de expressão em tempo de compilação

Por exemplo, considere este procedimento armazenado Transact-SQL:

USE AdventureWorks
GO
CREATE PROCEDURE MyProc( @d datetime )
AS
SELECT COUNT(*)
FROM Sales.SalesOrderHeader
WHERE OrderDate > @d+1

Durante a otimização da instrução SELECT no procedimento, o otimizador tenta avaliar a cardinalidade esperada do conjunto de resultados para a condição OrderDate > @d+1. A expressão @d+1 não é uma constante dobrável, porque @d é um parâmetro. Entretanto, no momento da otimização, o valor do parâmetro é conhecido. Isso permite que o otimizador calcule precisamente o tamanho do conjunto de resultados, o que o ajuda a selecionar um bom plano de consulta.

Agora considere um exemplo semelhante ao anterior, exceto pelo fato de que a variável local @d2 substitui @d+1 na consulta e a expressão é avaliada em uma instrução SET, em vez de na consulta.

USE AdventureWorks
GO
CREATE PROCEDURE MyProc2( @d datetime )
AS
BEGIN
DECLARE @d2 datetime
SET @d2 = @d+1
SELECT COUNT(*)
FROM Sales.SalesOrderHeader
WHERE OrderDate > @d2
END

Quando a instrução SELECT em MyProc2 é otimizada no SQL Server, o valor de @d2 não é conhecido. Portanto, o otimizador usa uma estimativa padrão para a seletividade de OrderDate > @d2 (nesse caso, 30 por cento).

Práticas recomendadas: Usando dobra constante e avaliação de expressão em tempo de compilação para gerar planos de consulta ideais

Para ter certeza de gerar planos de consulta ideais, é bom projetar consultas, procedimentos armazenados e lotes de forma que o otimizador de consulta possa calcular com precisão a seletividade das condições em sua consulta, baseado em estatísticas sobre a distribuição de dados. Caso contrário, o otimizador deve usar uma estimativa padrão ao calcular a seletividade (como 30 por cento no exemplo anterior).

Para ter certeza que o avaliador de cardinalidade do otimizador fornece boas estimativas, você deve primeiramente ter certeza de que as opções SET AUTO_CREATE_STATISTICS e AUTO_UPDATE_STATISTICS do banco de dados estão ON (a configuração padrão), ou que você criou estatísticas manualmente em todas as colunas mencionadas na condição da consulta. Então, quando você estiver projetando as condições de suas consultas, faça o seguinte quando possível:

  • Evite o uso de variáveis locais em consultas. Em vez disso, use parâmetros, literais ou expressões na consulta.

  • Limite o uso de operadores e funções incorporadas em uma consulta que contém um parâmetro para aqueles listados em Avaliação de expressão em tempo de compilação para estimativa de cardinalidade.

  • Certifique-se de que as expressões apenas constante na condição de sua consulta são constantes desdobráveis ou que podem ser avaliadas em tempo de compilação.

  • Se você tiver que usar um variável local para avaliar uma expressão a ser usada em uma consulta, considere avaliá-la em um escopo diferente da consulta. Por exemplo, pode ser útil executar uma das seguintes ações:

    • Transfira o valor da variável para um procedimento armazenado que contém a consulta você quer avaliar e faça a consulta usar o parâmetro de procedimento em vez de uma variável local.

    • Crie uma cadeia de caracteres contendo uma consulta baseada em parte do valor da variável local e, então, execute a cadeia de caracteres usando SQL dinâmico (EXEC ou sp_executesql).

    • Atribua parâmetros à consulta e execute-a usando sp_executesqle transfira o valor da variável como um parâmetro para a consulta.