Dicas de T-SQL – Parameter Sniffing

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.

Deixe uma resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *