Fabio Cabral Rosa Teixeira - Blog Pessoal de Assuntos Diversos: Dica SQL - Performance em Banco de Dados - Stored Procedure
Dica SQL - Performance em Banco de Dados - Stored Procedure
Algumas aplicações podem ter perda de performance na execução de procedures do SQL Server. Uma dica bem simples e importante, nunca utilize o SP_ nas stored procedures que você escreveu. Nunca!
Por exemplo, jamais criei uma Procedure com o nome SP_Cadastro_Clientes.
Quando uma procedure é precedida por SP_ o SQL Server busca a procedure no banco Master. Isso permite que sejam criadas procedures globais para administrar o servidor. Essas procedures podem ficar no Master e serem chamadas a partir de qualquer banco que o SQL Server as encontra.
O SQL Server mantém um cache de planos de execução das procedures. Assim sendo, quando uma aplicação executa uma procedure, primeiramente ele procura esta procedure no cache, se estiver lá utiliza estas informações, caso contrário terá que recompilar. A versão 7 e superiores mantém apenas 1 cópia de cada procedure no cache de procedures.
Para garantir isso o SQL Server precisa ter certeza que, recebendo 2 pedidos simultâneos para a mesma procedure, não tentará compila-la 2 vezes. Sendo assim, durante o processo de compilação o SQL Server gera um lock, chamado de compilation lock, que impede que outras aplicações compilem a procedure ao mesmo tempo. Ocorre que o compilation lock é gerado imediatamente após a procura no cache, e isso faz com que o SQL Server busque a procedure no cache, e se não achar, gera o lock.
Quando a procedure tem o prefixo SP_ ela é primeiramente procurada no Master e não no banco em que o usuário está trabalhando. Quando a procedure não é encontrada no Master é gerado o compilation lock. Posteriormente, antes de compilar a procedure, o SQL Server acaba descobrindo que ela já está no cache de procedures do banco de dados do usuário e reaproveita o cache, sem recompilar a procedure. Porém TODAS as chamadas desta procedure vão gerar o compilation lock e prejudicar seriamente o tempo de execução da procedure. Devido a isso, evite utilizar o prefixo SP_ nas suas procedures, utilize apenas quando necessário, para procedures administrativas.