Início / dba / Perguntas / 144853
Accepted
Kenneth Fisher
Asked: 2016-07-26 09:17:51 +0800 CST

Por que esse índice filtrado não apenas não é usado, mas também apresenta erros quando tento forçá-lo [duplicado]

  • 772

Estou tentando ajustar uma consulta bastante irritante em que várias tabelas têm algo parecido com:

date = '9999-12-31 23:59:59.9999999'

Pelo menos algumas das tabelas estão na faixa de 300 mil linhas e, quando filtro isso, acabo na faixa de 2 mil. Parecia razoável tentar um índice filtrado.

CREATE TABLE test (col1 int PRIMARY KEY, col2 int, col3 varchar(50), col4 datetime2(7), col5 int);
CREATE INDEX ix_filtered ON test(col2,col3) INCLUDE (col4) 
       WHERE col4 = '9999-12-31 23:59:59.9999999' ;
GO

SELECT col2,col3 FROM test 
WHERE col4 = '9999-12-31 23:59:59.9999999';
GO

No entanto, quando verifiquei o plano de consulta, ele não usaria o índice. Ele apenas fez uma varredura de índice clusterizado e uma pesquisa de chave. Obviamente, sem nenhum dado, isso faz sentido, mas mesmo com todos os meus dados, aconteceu a mesma coisa. A razão pela qual não me preocupei em fornecer nenhum dado é esse segundo problema que ocorre de qualquer maneira.

Quando tentei forçar o índice para ver como ficaria o plano de consulta:

SELECT col2,col3 FROM test 
WITH (index (ix_filtered))
WHERE col4 = '9999-12-31 23:59:59.9999999'

Eu recebo este erro:

Msg 8622, Nível 16, Estado 1, Linha 52 O processador de consulta não pôde produzir um plano de consulta devido às dicas definidas nesta consulta. Reenvie a consulta sem especificar nenhuma dica e sem usar SET FORCEPLAN.

Estou tentando descobrir por que estou recebendo o erro com a dica de consulta. Meu palpite é que a resposta para isso também me dirá por que o índice filtrado não está sendo usado.

sql-server-2008-r2 filtered-index

1 respostas

  1. Best Answer

    Descobrimos que esse erro ocorreu porque a parametrização forçada estava ativada no nível do banco de dados. Para outros leitores que desejam reproduzi-lo, use o código de exemplo de Kenneth em um banco de dados com esta configuração:

    create database filterme;
GO

alter database filterme set parameterization forced;
GO

    Para consultas parametrizadas, o SQL Server não deseja armazenar em cache um plano de execução com um índice filtrado que pode não funcionar para outros valores de parâmetro.

    Em versões posteriores do SQL Server, o plano de consulta para o plano "não sugerido" mostraria (espero) um aviso de "índices não correspondentes" para alertá-lo melhor sobre isso. Isso simplesmente não existia no SQL Server 2008R2. (Observação: acabei de testar no SQL 2016 e não estou vendo o aviso de índices incompatíveis, preciso investigar mais por que ele não está aparecendo com este código de exemplo.)

    Para que a consulta funcione sem alterar a configuração do banco de dados, você pode usar uma dica "Option recompile" nas consultas. Isso funciona porque instrui o SQL Server a não armazenar em cache o plano para reutilização.

    • 7

relate perguntas