Há algum tempo, por ingenuidade, alguns programadores assumem que usar stored procedures é uma coisa ruim. Muitos assumem que há risco de “vazar” o domínio do código aplicação para o banco de dados. Entretanto, quando isso ocorre, a causa é o abuso da tecnologia e não da tecnologia em si.

Utilizar stored procedures para consultar o banco de dados tem uma série de benefícios comparado com consultas ad hoc: são mais rápidas, reduzem o tráfego na rede, são mais seguras e podem encapsular código reutilizável. Além de tudo isso, são armazenadas de forma ótima pelo SQL Server.

Toda vez que uma consulta é submetida ao SQL Server, ela precisa ser interpretada (query parser e algebrizer), otimizada (optimizer) e, finalmente, executada. Se forem stored procedures, porém, a interpretação e otimização só ocorrem na primeira execução, nas demais, a execução ocorre de maneira direta. Entretanto, é necessário estar atento para identificar se o banco “entende” a consulta da forma adequada.

Consideremos, por exemplo, a seguinte stored procedure para a base AdventureWorks2019.

CREATE OR ALTER PROCEDURE dbo.usp_GetPerson 
( 
    @BusinessEntityID	INT = NULL, 
    @StateProvinceID	INT = NULL, 
    @CountryRegionCode	NVARCHAR(3) = NULL 
) 
AS 
BEGIN
	
	SET NOCOUNT ON

	SELECT
		A.FirstName,
		A.LastName
	 FROM Person.Person as A
	 LEFT OUTER JOIN Person.BusinessEntityAddress as B
	   ON A.BusinessEntityID = B.BusinessEntityID
	 LEFT OUTER JOIN Person.Address as C
	   ON B.AddressID = C.AddressID 
	 LEFT OUTER JOIN Person.StateProvince as D
	   ON C.StateProvinceID = D.StateProvinceID 
	 LEFT OUTER JOIN Person.CountryRegion as E
	   ON D.CountryRegionCode = E.CountryRegionCode
	WHERE (@BusinessEntityID IS NULL OR A.BusinessEntityID = @BusinessEntityID) 
	  AND (@StateProvinceID IS NULL OR C.StateProvinceID = @StateProvinceID) 
	  AND (@CountryRegionCode IS NULL OR D.CountryRegionCode = @CountryRegionCode)

END

De maneira simples, queremos retornar um conjunto de registros filtrando com base em um conjunto de parâmetros opcionais.

É muito comum encontramos esse padrão de escrita de procedures para resolver consultas que pode ter vários parâmetros opcionais como possíveis filtros. Infelizmente, apesar desse código ser rápido e prático de escrever, existem alguns problemas nessa abordagem. Eles ficam evidentes na análise do plano de execução para consultas simples.

Na imagem, o primeiro ponto de atenção é a complexidade do plano de execução determinado pelo SQL Server. A consulta proposta é simples (buscar a pessoa cujo BusinessEntityID = 2).  Repare que, mesmo não utilizando as tabelas de endereço, há uma junção para elas. Outro aspecto importante é que a base dispensou o melhor índice efetuando um index scan no índice AK_person_rowguid ao invés de um index seek no índice PK_Person_BusinessEntityID.

O mesmo plano é seguido para outras configurações de parâmetros.

Na imagem, constatamos que o mesmo plano de execução é reutilizado para a stored procedure, mesmo com parâmetros que levam a uma configuração diferente.Trata-se de um problema bem conhecido: Parameter Sniffing. [tweet]Para quem não sabe o que faz, um benefício de uma ferramenta converte-se, rapidamente, em um pesadelo. [/tweet]  

Existem algumas soluções paleativas para resolver o problema em questão quando o mesmo estiver ocorrendo em produção e não se tem muito tempo para apagar o incêndio no servidor. A mais simples delas é identificar o plano de execução que está no cache e utilizar o comando DBCC FREEPROCCACHE <PLANO DE EXECUÇÃO> para remover ele, forçando com isso que na próxima execução da procedure monte um plano de execução melhor. É preciso ter cuidado pois caso seja executado essa instrução sem informar parâmetros, irá remover TODOS os planos de execução do seu cache de planos, fazendo o SQL Server ter mais trabalho para recompilar os mesmos, quando as consultas forem executadas novamente. Isso consome bastante recursos do servidor. 

DBCC FREEPROCCACHE (0x060006000C99302500499EFC8801000001000000000000000000000000000000000000000000000000000000);

Outra possível solução para o problema em questão, é ao final da instrução select da sua procedure utilizar a opção OPTION (RECOMPILE). Embora essa solução resolva o problema de forma mais persistente e efetiva do que a remoção do plano do cache, irá fazer com que o SQL Server descarte qualquer plano de execução salvo para a consulta e crie um novo plano de execução a cada vez que a procedure for chamada, podendo ocasionar um consumo alto de recursos no seu servidor, caso a mesma seja executada com frequência, ou essa técnica também seja utilizada em várias outras procedures. 

CREATE OR ALTER PROCEDURE dbo.usp_GetPerson
( 
    @BusinessEntityID  INT = NULL,
    @StateProvinceID   INT = NULL,
    @CountryRegionCode NVARCHAR(3) = NULL
)
AS
BEGIN

	SET NOCOUNT ON 

	SELECT
		A.FirstName, 
		A.LastName
	 FROM Person.Person as A 
	 LEFT OUTER JOIN Person.BusinessEntityAddress as B 
       ON A.BusinessEntityID = B.BusinessEntityID 
	 LEFT OUTER JOIN Person.Address as C 
	   ON B.AddressID = C.AddressID 
	 LEFT OUTER JOIN Person.StateProvince as D 
	   ON C.StateProvinceID = D.StateProvinceID 
	 LEFT OUTER JOIN Person.CountryRegion as E 
	   ON D.CountryRegionCode = E.CountryRegionCode 
	WHERE (@BusinessEntityID IS NULL OR A.BusinessEntityID = @BusinessEntityID) 
	  AND (@StateProvinceID IS NULL OR C.StateProvinceID = @StateProvinceID) 
	  AND (@CountryRegionCode IS NULL OR D.CountryRegionCode = @CountryRegionCode) 
	OPTION (RECOMPILE)

END 

Ao chamar novamente a procedure um plano de execução mais performático foi escolhido:

Usando Dynamic Queries para otimizar suas Stored Procedures

Apesar da escrita ser mais trabalhosa (e um pouco menos legível), utilizar dynamic queries (consultas dinâmicas) para implementar a procedure acima, pode resolver os problemas de otimização de código e parameter sniffing de uma forma permanente e mais eficaz. 

CREATE   PROCEDURE [dbo].[usp_GetPersonOptimized]
(
	@BusinessEntityID	INT = NULL,
	@StateProvinceID	INT = NULL,
	@CountryRegionCode	NVARCHAR(3) = NULL
)
AS
BEGIN

	SET NOCOUNT ON

	declare @query nvarchar(max) = '
	SELECT
		A.FirstName,
		A.LastName
	FROM Person.Person as A ' +
	IIF(@StateProvinceID IS NULL AND @CountryRegionCode IS NULL, '', '
	INNER JOIN Person.BusinessEntityAddress as B ON A.BusinessEntityID = B.BusinessEntityID 
	INNER JOIN Person.Address as C ON B.AddressID = C.AddressID
	INNER JOIN Person.StateProvince as D ON C.StateProvinceID = D.StateProvinceID ') +
	IIF(@CountryRegionCode IS NULL, '', '
	INNER JOIN Person.CountryRegion as E ON D.CountryRegionCode = E.CountryRegionCode ') + '
	WHERE 1 = 1 ' +
	IIF (@BusinessEntityID IS NULL, '', '
	  AND A.BusinessEntityID = @BusinessEntityID ') +
	IIF (@StateProvinceID IS NULL, '', '
	  AND C.StateProvinceID = @StateProvinceID ') +
	IIF (@CountryRegionCode IS NULL, '', '
	  AND D.CountryRegionCode = @CountryRegionCode ')

	EXEC sp_executesql
		@stmt			= @query,
		@params			= N'@BusinessEntityID INT, @StateProvinceID INT, @CountryRegionCode NVARCHAR(3)',
		@BusinessEntityID	= @BusinessEntityID,				
		@StateProvinceID	= @StateProvinceID,					
		@CountryRegionCode	= @CountryRegionCode

END
GO

[explanation]

O uso do filtro WHERE 1 = 1, é apenas um facilitador para a escrita dos demais filtros da consulta. Dessa forma não se faz necessário ficar testando se algum filtro já foi adicionado anteriormente a consulta. Na hora de avaliar os predicados da consulta o compilador ignora esse filtro ao montar o plano de execução.

[/sc]

Existem diversas vantagens em escrever dynamic queries nas procedures dessa forma: 

• Monta o plano de execução ótimo de acordo com os parâmetros passados. 
• O uso da sp_executesql faz com que o plano de execução seja colocado em cache e reutilizado pelo SQL Server posteriormente, sempre que uma consulta semelhante vier a ser executada. 
• Faz com que somente os objetos realmente necessários pela sua consulta sejam utilizados, otimizando a performance dela. 
• Com a alteração da procedure foi possível trocar o LEFT OUTER JOIN por INNER JOIN, outro pequeno ganho de performance. 
• Evita sql injection visto que os parâmetros da sua procedure são repassados como parâmetros também para sua consulta dinâmica. 

Ao efetuar a chamada da procedure simultaneamente, porém passando parâmetros diferentes, nota-se que os planos de execução mudaram para cada caso, tendo o SQL Server optado pelo plano ideal para ambos casos: 

Existe, porém, uma desvantagem ao utilizar essa abordagem de dynamic queries. Eventualmente poderão ser criados muitos planos de execução para essa mesma stored procedure que serão armazenados no cache de planos. Entretanto o ganho de performance com essa solução irá mais que compensar por isso.