Usuário com melhor resposta
Campo data não usa o índice

Pergunta
-
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 datetimedeclare @data_final as datetime
set @data_inicial = '16/1/2009'
set
@data_final = '16/1/2009'SELECT
* FROM ViewWHERE
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.
Respostas
-
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
-
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.
-
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 Snippetdeclare
@data_inicial as datetimedeclare
@data_final as datetimeset @data_inicial = '17/1/2009'
set
@data_final = '17/1/2009'SELECT
* FROM ViewWHERE
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
-
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.
Todas as Respostas
-
-
-
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
-
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.
-
-
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 Snippetdeclare
@data_inicial as datetimedeclare
@data_final as datetimeset @data_inicial = '17/1/2009'
set
@data_final = '17/1/2009'SELECT
* FROM ViewWHERE
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
-
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.
-
-
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
-