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,
