Otimizando Stored Procedures

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.

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

INSIGHTS EXIMIACO

Arquitetura de Dados

Otimização de banco de dados para aplicações mais velozes e estáveis.

NOVOS HORIZONTES PARA O SEU NEGÓCIO

Nosso time está preparado para superar junto com você grandes desafios tecnológicos.

Entre em contato e vamos juntos utilizar a tecnologia do jeito certo para gerar mais resultados.

Insights EximiaCo

Confira os conteúdos de negócios e tecnologia desenvolvidos pelos nossos consultores:

Arquitetura de Dados

Insights de um DBA na análise de um plano de execução

Especialista em performance de Bancos de Dados de larga escala
Arquitetura de Dados

A realidade do Arquiteto de Dados dentro das organizações

Especialista em performance de Bancos de Dados de larga escala
Arquitetura de Dados

LGPD: Protegendo as informações no Banco de Dados

Especialista em performance de Bancos de Dados de larga escala
EximiaCo 2024 - Todos os direitos reservados
0
Queremos saber a sua opinião, deixe seu comentáriox
()
x

Muito obrigado!

Deu tudo certo com seu envio!
Logo entraremos em contato

Otimizando Stored Procedures

Para se candidatar nesta turma aberta, preencha o formulário a seguir:

Otimizando Stored Procedures

Para se candidatar nesta turma aberta, preencha o formulário a seguir:

Condição especial de pré-venda: R$ 14.000,00 - contratando a mentoria até até 31/01/2023 e R$ 15.000,00 - contratando a mentoria a partir de 01/02/2023, em até 12x com taxas.

Tenho interesse nessa capacitação

Para solicitar mais informações sobre essa capacitação para a sua empresa, preencha o formulário a seguir:

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.

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ê:

Tenho interesse nessa solução

Se você está procurando este tipo de solução para o seu negócio, preencha este formulário que um de nossos consultores entrará em contato com você:

Tenho interesse neste serviço

Se você está procurando este tipo de solução para o seu negócio, preencha este formulário que um de nossos consultores entrará em contato com você:

× Precisa de ajuda?