Você já ouviu falar de Parameter Sniffing?
Imagine que você desenvolveu uma procedure que recebe parâmetros. Quando essa procedure for executada pela primeira vez, o SQL Server compilará e armazenará um plano de execução. Este plano de execução será otimizado baseado no parâmetro que a procedure recebeu. Mas o que acontece se o plano armazenado não for o melhor para outros parâmetros que podem ser utilizados? Esse é o efeito chamado de “Parameter Sniffing”.
Esse efeito se torna um problema quando o plano de execução ótimo (ou seja, o melhor possível) varia bastante na execução de uma procedure de acordo com os parâmetros utilizados. Neste cenário, a consulta quando é executada individualmente termina rodando mais rápido do que quando executada dentro da procedure.
Vamos simular aqui essa situação. Para isso utilizaremos a base AdventureWorks2014.
Imagine a seguinte procedure:
CREATE OR ALTER PROC dbo.PR_ListAddressByStateProvinceId (@StateProvinceId INT)
AS
BEGIN
SELECT AddressId, City, StateProvinceId, ModifiedDate
FROM Person.Address WHERE StateProvinceID = @StateProvinceId;
END;
GO
É uma procedure bastante simples, que realiza o filtro da tabela Person.Address em um determinado valor da coluna StateProvinceID. Vamos executá-la e observar como o plano de execução se comporta.
Primeira execução:
EXEC dbo.PR_ListAddressByStateProvinceId @StateProvinceId = 32;
Vamos chamar esse plano de execução de A.
Segunda execução:
EXEC dbo.PR_ListAddressByStateProvinceId @StateProvinceId = 20;
Um detalhe importante. Observem que a operação de Index Seek na primeira execução retornou uma única linha, já na segunda execução retornou 308 linhas. Será que essa operação é mesmo a ideal para o parâmetro passado na segunda execução?
Vamos utilizar o sp_recompile para forçar que a procedure seja recompilada na próxima execução.
EXEC sp_recompile 'PR_ListAddressByStateProvinceId';Agora vamos inverter a ordem das execuções.
Primeira execução:
EXEC dbo.PR_ListAddressByStateProvinceId @StateProvinceId = 20;
Vamos chamar esse plano de execução de B.
Segunda execução:
EXEC dbo.PR_ListAddressByStateProvinceId @StateProvinceId = 32;
Certo, mas qual impacto de usar o plano de execução não ideal?
Parâmetro 20:
A
B
Parâmetro 32:
A
B
Note que a utilização do plano de consulta errado nos dois casos gera um aumento na quantidade de leitura lógica realizada pelo SQL Server. Nossa tabela de exemplo é bem pequena, apenas 19.614 registros. Imagine o que pode acontecer com tabelas maiores.
Mas como podemos evitar esse tipo de situação?
O primeiro passo é saber que isso pode acontecer. Um dos cenários mais comuns é quando há uma diferença muito grande na quantidade de registros em determinado filtro. No nosso exemplo, podemos perceber que um deles havia apenas um registro enquanto o outro retornava 308.
Para evitar esse problema, mostraremos algumas abordagens possíveis:
1 – Usar a cláusula RECOMPILE
CREATE OR ALTER PROC dbo.PR_ListAddressByStateProvinceId (@StateProvinceId INT) WITH RECOMPILE
AS
BEGIN
SELECT AddressId, City, StateProvinceId, ModifiedDate
FROM Person.Address WHERE StateProvinceID = @StateProvinceId;
END;
GO
Usar RECOMPILE evitará o parameter sniffing, porém trará um impacto negativo pois a procedure será compilada em cada execução.
2- Produzir procedures ou planos diferentes para cada situação identificada
CREATE OR ALTER PROC dbo.PR_ListAddressByStateProvinceId (@StateProvinceId INT)
AS
BEGIN
DECLARE @cmd NVARCHAR(MAX) = 'SELECT AddressId, City, StateProvinceId, ModifiedDate FROM Person.Address WHERE StateProvinceID = @StateProvinceId ';
IF(@StateProvinceId = 32)
SET @cmd = @cmd + ' AND 1 = 1';
ELSE
SET @cmd = @cmd + ' AND 2 = 2';
EXEC sp_executesql @cmd, @params = N'@StateProvinceId INT', @StateProvinceId = @StateProvinceId;
END;
GO
Esse tipo de solução pode ser aplicado quando sabemos exatamente em que situações o plano de execução pode mudar, forçando que sejam criados planos de execuções diferentes de acordo com o parâmetro.
Tem alguma dúvida sobre esse problema? Deixe um comentário.
