Você deveria usar stored procedures?

Há algum tempo, por ingenuidade, alguns programadores assumem que o uso de stored procedures é sinônimo de problemas em desempenho e, em alguns casos, “vazamento” do domínio, o qual deveria estar no código da aplicação. Entretanto, quando analisamos cenários em que o uso de stored procedures é relacionada a gargalo no banco de dados ou vazamento de domínio, percebemos que o gatilho comum é referente ao abuso do recurso e não da tecnologia em si. Utilizar stored procedures para consultar o banco de dados pode trazer uma série de benefícios comparado com consultas ad hoc.

Stored Procedures são mais rápidas pois reduzem o tráfego na rede, são seguras e podem encapsular código reutilizável. Além de tudo isso, possuem a vantagem de ter um plano de execução, criado na primeira execução e armazenado no plan cache, evitando futuras recompilações.

Stored procedures são recursos importantes e poderosos que podem melhorar significativamente o desempenho das aplicações. Não utilizá-las é sinal de despreparo ou de falta de cuidado disfarçado de preciosismo.

O Problema

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

Recorrentemente vemos procedures que recebem uma série de parâmetros, representando diferentes filtros na aplicação. Considere como exemplo, a procedure abaixo, criada no banco “AdventureWorks”:

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

Embora seja uma consulta simples, a mesma gera um plano de execução de certa forma complexo, como pode ser observado na imagem abaixo:

Cabe destacar que, mesmo não utilizando as tabelas de endereço, de acordo com os parâmetros da chamada, é processada a junção das mesmas. Outro aspecto importante é que, para a consulta acima, o Optimizer acaba dispensando o melhor índice, percorrendo todos registros da tabela (index scan no índice AK_person_rowguid) ao invés de uma busca direcionada, através de um index seek (índice PK_Person_BusinessEntityID).

Mas o que acontece se alterarmos os parâmetros passados para a procedure?

Constata-se que o plano de execução foi reutilizado, mesmo com parâmetros que levariam a uma consulta diferente. Trata-se de um problema bem conhecido: Parameter Sniffing. Para quem não sabe o que faz, o benefício de uma ferramenta converte-se, rapidamente, em um pesadelo.

Parameter Sniffing

Quando a procedure é compilada, o valor do parâmetro é avaliado na criação do plano de execução, que será armazenado no cache de planos. Uma vez que o processo de compilar consultas é oneroso, o SQL Server, sempre que possível, tenta reutilizar os planos de execução já criados. O problema ocorre quando o resultado das consultas difere muito e foi utilizado o plano de execução da consulta mais simples (que foi executada primeiro), para resolver a consulta mais complexa (executada posteriormente), e assim acaba-se optando por um plano não ideal. Apesar de eventualmente poder causar algum problema de performance, esse é um comportamento normal do banco de dados quando você está utilizando parâmetros nas suas stored procedures.

Existem soluções paliativas para resolver o problema  do Parameter Sniffing  em produção, quando não possuímos tempo para uma solução definitiva.

Uma destas soluções  é identificar o plano de execução que está no cache e utilizar o comando DBCC FREEPROCCACHE <PLANO DE EXECUÇÃO> para removê-lo. Isto irá forçar que a próxima execução da procedure gere um novo plano de execução. CUIDADO, ao executar essa instrução sem informar parâmetros, serão removidos TODOS os planos de execução do seu plan cache, fazendo com que o SQL Server tenha mais trabalho, visto que irá recompilar todos os planos de execução quando as consultas forem novamente executadas. Isso pode consumir bastante recursos do servidor!

DBCC FREEPROCCACHE (0x060006000C99302500499EFC8801000001000000000000000000000000000000000000000000000000000000);

Dica

Um script que pode ser útil para encontrar qual plano de execução precisa ser removido é a sp_BlitzCache, desenvolvida por Brent Ozar e disponível para download gratuito em seu site. Nos resultados desta procedure, há uma coluna exatamente com o comando para remoção do plano de execução do cache, além de outras análises interessantes.

Outra possível solução para o problema em questão, é utilizar o hint OPTION (RECOMPILE) ao final da instrução SELECT. 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 sempre que a procedure for chamada. Isso pode ocasionar um consumo alto de recursos no seu servidor, caso a stored procedure seja executada com muita frequência:

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) 
<strong>OPTION (RECOMPILE)</strong>
 
END

Ao chamar novamente a procedure, com parâmetros diferentes, um plano de execução mais performático é gerado:

A Solução

Dynamic SQL são consultas que são construídas sob demanda antes de serem executadas, sendo que o funcionamento da consulta pode variar de acordo com os parâmetros de entrada informados. Utilizando Dynamic SQL pode-se resolver os problemas de otimização de código e parameter sniffing de uma forma 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

As vantagens em utilizar Dynamic SQL nas procedures para resolver esse tipo de problema, são:

  • Utiliza um plano de execução ideal 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 para sua consulta (de acordo com os parâmetros passados), 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, evitando assim utilizar a concatenação dos valores

Para ilustrar os benefícios desse recurso, ao efetuar a chamada da procedure simultaneamente, porém passando parâmetros diferentes, percebe-se que os planos de execução mudaram para cada consulta, tendo o SQL Server optado pelo plano ideal para ambos os casos:

Conclusão

Embora utilizar essa abordagem tenha a desvantagem de serem criados e armazenados em cache mais planos de execução para a mesma procedure (a cada configuração diferente de parâmetros deverá ser criado um novo plano em cache), o ganho de performance frequentemente compensa a sua utilização, uma vez que os planos de execução gerados pelo SQL Server serão planos ideais.

A ideia de que utilizar stored procedure não é uma boa prática no desenvolvimento de software, é uma visão simplista, que limita a solução ideal. Quando utilizamos esse recurso de maneira adequada, podemos proporcionar ganhos significativos de performance em comparação a consultas AD HOC.

Em resumo

Fato
Utilizar stored procedures é a forma mais rápida e segura para acessar seus dados no SQL Server, além de proporcionar a reutilização do código e redução do tráfego na rede. Porém é preciso entender como o SQL Server trabalha com procedures parametrizadas, para que o código não venha a apresentar problemas futuros de performance, quando seus dados não são distribuídos de forma homogênea, ocasionando eventuais gargalos no seu servidor de banco de dados.
Insight
Com algumas alterações na forma da escrita das stored procedures, fazendo uso de Dynamic SQL, é possível otimizarmos nosso código para evitar problemas que podem vir a acontecer por baixo dos panos nos ambientes de produção e cujo a solução nem sempre é trivial e perceptível. Além disso, podemos escrever códigos “genéricos”, mas com ganhos de performance, apenas utilizando somente o que é realmente necessário para resolver as consultas.

Compartilhe este insight:

Comentários

Participe deixando seu comentário sobre este artigo a seguir:

Subscribe
Notify of
guest
0 Comentários
Inline Feedbacks
View all comments

AUTOR

Marcelo Michelon
Mais de 15 anos como Especialista na modelagem, manutenção e otimização de bases de dados e na gestão de servidores com SQL Server

SOLUÇÕES EXIMIACO

Arquitetura de Dados

ESTRATÉGIA & EXECUÇÃO EM TI

Simplificamos, potencializamos 
aceleramos resultados usando a tecnologia do jeito certo.

INSIGHTS EXIMIACO

Confira outros insights de nossos consultores relacionados a esta solução de negócio:

12/03/2021
12/03
2021
19/02/2021
19/02
2021

COMO PODEMOS LHE AJUDAR?

Vamos marcar uma conversa para que possamos entender melhor sua situação e juntos avaliar de que forma a tecnologia pode trazer mais resultados para o seu negócio.

COMO PODEMOS LHE AJUDAR?

Vamos marcar uma conversa para que possamos entender melhor sua situação e juntos avaliar de que forma a tecnologia pode trazer mais resultados para o seu negócio.

+55 51 3049-7890
[email protected]

+55 51 3049-7890 |  [email protected]

0
Queremos saber a sua opinião, deixe seu comentáriox
()
x

Tenho interesse em conversar

Se você está querendo gerar resultados através da tecnologia, preencha este formulário que um de nossos consultores entrará em contato com você:

O seu insight foi excluído com sucesso!

O seu insight foi excluído e não está mais disponível.

O seu insight foi salvo com sucesso!

Ele está na fila de espera, aguardando ser revisado para ter sua publicação programada.