Então por que não devemos nomear todas nossas procedures como SP_ ?
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. Assim sendo, 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. Ou seja : o SQL Server busca a procedure no cache, se não achar, gera o lock. E dai ?
Quando a procedure tem o prefixo SP_ ela é primeiramente procurada no Master e não no banco em que o usuário está. 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 estar gerando o compilation lock e prejudicando 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
Fonte: TechNet