Filtered index, query hints e QO error 8622

Para ler o PDF e baixar o script utilizado, acesse o skydrive do Luti.

Já não foi a primeira vez que eu esbarro no erro 8622 e ontem aconteceu novamente, então aproveito para escrever um pequeno post sobre o assunto.

Para quem nunca viu o erro seu detalhamento é: “Msg 8622, Level 16, State 1, Line 3 - Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.”

A mensagem é bem clara, estamos utilizando uma hint que impede o query optimizer produzir um plano de execução, ou seja, nossa hint é algo que viola as regras do QO para geração do plano, por ser uma contradição ou uma dica que se seguida pode gerar resultados incorretos.

Para gerar um cenário semelhante ao que encontrei o problema basta utilizar o script 01, guardadas as devidas proporções, pois no meu caso eram 100 milhões de registros.

Script 01 – Criando a tabela RegistroProblema

USE Internals

GO

IF OBJECT_ID('dbo.RegistroProblema', 'U') IS NOT NULL

      DROP TABLE dbo.RegistroProblema

GO

CREATE TABLE dbo.RegistroProblema (

      ID INT IDENTITY NOT NULL PRIMARY KEY

      , Nome VARCHAR(100) NOT NULL DEFAULT ('Sr. Nimbus')

      , DataRegistro DATETIME2 NOT NULL DEFAULT(SYSDATETIME())

      , Problema VARCHAR(100) NULL

, Resolvido BIT NULL DEFAULT (0) 

)

GO

INSERT INTO dbo.RegistroProblema DEFAULT VALUES

GO 100000

UPDATE dbo.RegistroProblema

      SET

            Problema = 'Problema' + CAST((ID % 13) AS VARCHAR)

            , Resolvido = IIF((ID % 73) < 72, 1, 0)

go

Continuando com o detalhamento do cenário, imagine que você está fazendo um tratamento pontual para o problema 01 e com o intuito de suportar a consulta decide criar um índice filtrado exatamente como a cláusula where que é utilizada.

Script 02 – Utilizando o índice com filtro

SELECT *

FROM dbo.RegistroProblema

WHERE Problema = 'Problema1'

      AND Resolvido = 0

ORDER BY DataRegistro ASC

go

CREATE NONCLUSTERED INDEX idxNCL_RegistroProblema_FiltroProblema1

ON dbo.RegistroProblema (DataRegistro)

WHERE Resolvido = 0

      AND Problema = 'Problema1'

GO

SELECT *

FROM dbo.RegistroProblema

WHERE Problema = 'Problema1'

      AND Resolvido = 0

ORDER BY DataRegistro ASC

go

No segundo plano de execução já podemos ver que o índice é utilizado, então ao invés de um cluster index scan temos um non-cluster index scan, conforme esperado.

Porém no procedimento em que eu estava utilizando essa consulta duas vezes, o código original utilizava a variável @NomeProblema para organizar o código T-SQL, só que essa abordagem faz com que o QO não consiga garantir o valor da variável e consequentemente não possa utilizar o índice filtrado, voltando ao índice cluster. Isso gera um plano bem mais caro (73% vs. 27%) mesmo para uma massa de dados menor, em produção a diferença era bem mais sensível.

Script 03 – Comparando planos de execução

DECLARE @NomeProblema VARCHAR(100) = 'Problema1'

SELECT *

FROM dbo.RegistroProblema

WHERE Problema = @NomeProblema

      AND Resolvido = 0

ORDER BY DataRegistro ASC

go

SELECT *

FROM dbo.RegistroProblema

WHERE Problema = 'Problema1'

      AND Resolvido = 0

ORDER BY DataRegistro ASC

go

Então para resolver o problema do plano de execução você poderia tentar forçar a utilização de um índice (script 04). Só que essa ação vai levar ao erro 8622, pois o SQL Server não pode utilizar o índice criado, pois o valor da variável não é conhecido e caso a pesquisa seja pelo “Problema2” o índice não retornaria nenhum registro, então o QO não pode utilizar essa hint para gerar o plano.

Script 04 – Recebendo o erro 8622

DECLARE @NomeProblema VARCHAR(100) = 'Problema1'

SELECT *

FROM dbo.RegistroProblema WITH(INDEX(idxNCL_RegistroProblema_FiltroProblema1))

WHERE Problema = @NomeProblema

      AND Resolvido = 0

ORDER BY DataRegistro ASC

go

Nesse caso não adianta trabalharmos com a hint OPTIMIZE FOR ou plan guides, o problema é o mesmo é persiste. Mas vamos então trabalhar com uma stored procedure e para evitar o SQL Server não saber o valor da variável em tempo de compilação, vou criar o parâmetro @NomeProblema e utilizar o mesmo diretamente na consulta, isto é, o SQL Server consegue fazer o sniff e sabe que o valor passado é o “Problema1”.

Script 05 – Encapsulando em uma SP

CREATE PROCEDURE proc_TesteSniffing @NomeProblema VARCHAR(100)

AS

      SELECT *

      FROM dbo.RegistroProblema

      WHERE Problema = @NomeProblema

            AND Resolvido = 0

      ORDER BY DataRegistro ASC

go

EXEC proc_TesteSniffing @NomeProblema = 'Problema1'

Ao executar o procedimento qual plano de execução você espera? Scan no cluster ou não-cluster? Dica: ao analisar o plano de execução conseguimos ver que o QO sabe do valor passado ParameterCompiledValue = "'Problema1'".

Se você respondeu scan no índice cluster, acertou! O SQL Server não pode colocar em cache um plano que não possa gerar resultados incorretos para outras chamadas, então ele não pode utilizar o índice com filtro. Se fizermos um outro teste (script 06) com o OPTION RECOMPILE, podemos ver que como a instrução é recompilada a toda chamada, o SQL Server pode efetivamente utilizar o índice filtrado quando o problema 1 é especificado.

Script 06 – OPTION RECOMPILE

ALTER PROCEDURE proc_TesteSniffing @NomeProblema VARCHAR(100)

AS

SELECT *

FROM dbo.RegistroProblema

WHERE Problema = @NomeProblema

      AND Resolvido = 0

ORDER BY DataRegistro ASC

SELECT *

FROM dbo.RegistroProblema

WHERE Problema = @NomeProblema

      AND Resolvido = 0

ORDER BY DataRegistro ASC

OPTION (RECOMPILE)

go

EXEC proc_TesteSniffing 'Problema1'

EXEC proc_TesteSniffing 'Problema2'

Neste pequeno artigo nós falamos um pouco sobre o erro 8622 que pode acontecer em diversos cenários e também resvalamos em outro detalhe muito interessante, que é a geração de planos de execução não ótimos dentro de procedures devido à reutilização do plano.

Abraços,

Luciano [Luti] Caixeta Moreira

luciano.moreira@srnimbus.com.br

http://luticm.blogspot.com/

http://www.twitter.com/luticm

sr. Nimbus Serviços em Tecnologia - www.srnimbus.com.br