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

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

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

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

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

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

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.

Consulte também

Conceitos

Outros recursos