Campo data não usa o índice

Question

Bom dia a todos.

 

Tenho uma dúvida. Tenho um select que está filtrando por data. Nessa coluna tem um índice non-clustered.

 

declare @data_inicial as datetime

declare @data_final as datetime

 

set @data_inicial = '16/1/2009'

set @data_final = '16/1/2009'

 

SELECT * FROM View

WHERE Data >= dateadd(day,0, @data_inicial) AND Data < dateadd(day,1,@data_final)

 

No caso acima ele não está usando o índice. Porém quando passo a data fixa, ele usa o índice.

SELECT * FROM View

WHERE Data >= dateadd(day,0, '16/1/2009') AND Data < dateadd(day,1,'16/1/2009')

 

Porque será?

 

Obrigado.

 

Abs.

 

Answer 1

Patricio,

 

Provavelmente o SQL Server esta descartando o índice, por você estar utilizando funções que estão realizando tratamento das informações.

Answer 2

Olá Junior.

 

Não entendi muito bem, você diz funções dentro da view? Não tenho nada.

 

Obrigado.

Answer 3

Isso é uma Stored Procedure?

 

A grosso modo:

 

Como os valores estão numa variável, o SQL Server não conhece o valor das variáveis e portanto, não sabe quantos registros deverão ser retornados. Uma forma de garantir isso é usando Stored procedures.

 

Abraços

 

Answer 4

Patrício,

 

O que eu quiz dizer é o seguinte:

 

Você esta utilizando dentro das sua view, variáveis e funções do tipo data, sendo que, ao utilizar uma varíavel que possui informações o SQL Server terá que ler o conteúdo desta variável para definir os dados que deveram ser retornados, neste momento o SQL Server não consegui definir qual seria a melhor estratégia, pois os dados estão armazenados na variável e tratados pelas funções.

 

Na segunda opção, você já esta passando os valores diretamente no Select, com isso o plano de execução do SQL Server em conjunto com o Query Optmizer, durante o processo de leitura da sua query, vai conseguir identificar mais facilmente qual será a faixa de valores, após isso chama a função para tratar estes dados, sobre a coluna indexada ao qual os dados pertence.

 

Quando se utiliza variáveis no SQL Server, na maioria das situações se a variável esta sendo declarada como parâmetro para claúsula where, o índice é descartado.

 

Answer 5

Júnior,

 

Vale lembrar que usando Stored Procedures ao invés de consultas Ad-Hoc, o SQL Server conhece o valor das variáveis.

 

Abraços

 

Answer 6

Olá Patrício,

 

As respostas dos colegas estão corretas mas gostaria de complementar. Esse problema existe já há algumas versões do SQL Server e ele também possui um nome (é conhecido como Parameter Sniffing).

 

A explicação é que ao utilizar uma variável, o SQL Server não tem condições de estipular uma boa estratégia já que o valor da variável influencia fortemente essa escolha. Dependendo do valor ele poderia optar por um índice. Dependendo do valor ele poderia optar por um Scan.

 

Quando o SQL Server encontra uma construção desse tipo, ele normalmente utilizará uma estimativa interna (é hardcode no SQL Server) de 30% de retorno dos dados. Isso naturalmente irá levar a um SCAN na maioria dos casos ou na melhor das hipóteses um Cover Index Scan.

 

Você poderia passar os parâmetros de forma fixa e facilitar a compilação de um plano, mas naturalmente que a possibilidade de dinamizar sua query seria descartada. Para que o índice seja utilizado você tem três alternativas:

 

Utilizar uma Stored Procedure

Se você utilizar uma Stored Procedure e definir os parâmetros da query como parâmetros da Stored Procedure, já na primeira compilação o plano será gerado e reaproveitado. É preciso que os parâmetros da consulta sejam parâmetros de entrada da SP. Se você colocar um DECLARE dentro da SP, o resultado será o mesmo que não utilizá-la.

 

Forçar o uso do Índice

Você pode utilizar um HINT para forçar o uso do índice. Assim ao invés de optar por um SCAN o índice será forçado a ser utilizado. Nesse caso, estaríamos em uma das raras situações em que nossa avaliação é superior à do otimizador, visto que sabemos que a utilização do índice será melhor

 

Utilizar a construção OPTIMIZE FOR

Nesse caso, você pode utilizar um valor genérico para que o SQL Server avalie o plano de execução e escolha naturalmente pelo índice. Ex:

 

Code Snippet

declare @data_inicial as datetime

declare @data_final as datetime

 

set @data_inicial = '17/1/2009'

set @data_final = '17/1/2009'

 

SELECT * FROM View

WHERE Data >= dateadd(day,0, @data_inicial) AND Data < dateadd(day,1,@data_final)

OPTION(OPTIMIZE FOR(@data_inicial = '16/1/2009', @data_final = '16/1/2009'));

 

No caso acima, a data 16/01/2009 serviu de referência para compilar o plano, mas a data utilizada será o valor da variável (no caso o dia 17).

 

[ ]s,

 

Gustavo Maia Aguiar

http://gustavomaiaaguiar.spaces.live.com

 

Answer 7

Quando você utiliza variáveis ao invés de valores fixos, o Query Optimizer não consegue determinar quais os valores de seus parâmetros.

 

Uma simples solução seria utilizar a clausula OPTION(RECOMPILE) no final da consulta.

 

Quando o Query Optimizer não consegue determinar qual é o valor da variável, ele utiliza uma regra baseado na densidade dos valores de sua tabela para tentar gerar o melhor plano de execução possível e estimar quantas linhas serão retornadas.

SELECT * FROM View

WHERE Data >= dateadd(day,0, @data_inicial) AND Data < dateadd(day,1,@data_final)

OPTION(RECOMPILE)

O Maia, falou sobre Densidade aqui.

http://forums.microsoft.com/MSDN-BR/ShowPost.aspx?PostID=3766858&SiteID=21

 

Já faz algum tempo que eu escrevi sobre isso aqui.

http://fabianosqlserver.spaces.live.com/Blog/cns!52EFF7477E74CAA6!230.entry

 

Eu estava para postar isso, quando dei um refresh e vi a Resposta do Gustavo.

 

Gustavo, falando com um arquiteto de desenvolvimento do Query Processor, peguei a seguinte informação.

 

“When the optimizer does not have a parameter value to sniff, it uses the density information for parameters in the context of whatever scalar operation is being performed.  In most of the examples, we do simple equality comparisons.”

 

Portanto creio que seja esta regra que você mencionou,mas não creio que o valor de 30% seja sempre 30%, mas ele depende da densidade dos dados na tabela.

 

Abraço.

Answer 8

Fabiano,

 

O que sei sobre densidade, é que a mesma esta relacionada com volume de dados existentes na table.

Answer 9

Olá Fabiano,

 

Muito interessante essa informação. Assim como a fonte da sua informação é certamente de valor, também tirei de um lugar a se considerar. Esse percentual está no livro Inside SQL Server 2005 - T-SQL Programming.

 

Em todo caso, ao ler a afirmação desse arquiteto, acho que se trata de uma informação mais apurada e sendo ele um dos arquitetos possivelmente trata-se de algo mais "correto". Pode ser que os 30% sejam uma média ou número que tenha forte ocorrência, mas não deve ser fixo, pois, não seria muito "eficiente".

 

Grato pela informação.

 

[ ]s,

 

Gustavo Maia Aguiar

http://gustavomaiaaguiar.spaces.live.com

 

Answer 10

Boa tarde a todos.

 

Muito obrigado pelas respostas / comentários, ajudaram bastante e esclareceram algumas coisas que eu não conhecia.

 

Abs