Início / dba / Perguntas / 25348
Accepted
user5147
Asked: 2012-10-04 03:33:34 +0800 CST

Ainda errado para iniciar o nome de um procedimento armazenado do usuário com sp_?

  • 772

Um dos meus colegas de trabalho nomeou um procedimento armazenado em nosso banco de dados SQL Server 2008 R2 sp_something. Quando vi isso, pensei imediatamente: "Isso está ERRADO!" e comecei a procurar em meus favoritos este artigo on-line que explica por que está errado, para que eu pudesse fornecer uma explicação ao meu colega de trabalho.

No artigo (por Brian Moran ) é explicado que dar ao procedimento armazenado um prefixo sp_ faz com que o SQL Server procure no banco de dados mestre um plano compilado. Como o sp_sprocnão reside lá, o SQL Server irá recompilar o procedimento (e precisa de um lock de compilação exclusivo para isso, causando problemas de performance).

O exemplo a seguir é dado no artigo para mostrar a diferença entre dois procedimentos: 

USE tempdb;
GO

CREATE PROCEDURE dbo.Select1 AS SELECT 1;
GO

CREATE PROCEDURE dbo.sp_Select1 AS SELECT 1;
GO

EXEC dbo.sp_Select1;
GO

EXEC dbo.Select1;
GO

Você executa isso, abre o Profiler (adicione os procedimentos armazenados -> SP:CacheMissevento) e execute os procedimentos armazenados novamente. Você deve ver uma diferença entre os dois procedimentos armazenados: o sp_Select1procedimento armazenado gerará um SP:CacheMissevento a mais do que o Select1procedimento armazenado (o artigo faz referência a SQL Server 7.0 e SQL Server 2000 ).

Quando executo o exemplo em meu ambiente SQL Server 2008 R2, obtenho a mesma quantidade de SP:CacheMisseventos para ambos os procedimentos (tanto no tempdb quanto em outro banco de dados de teste).

Então eu me pergunto:

  • Posso ter feito algo errado na minha execução do exemplo?
  • O adágio 'não nomeie um usuário sproc sp_something' ainda é válido em versões mais recentes do SQL Server?
  • Em caso afirmativo, existe um bom exemplo que mostre sua validade no SQL Server 2008 R2?

Muito obrigado por seus pensamentos sobre isso!

EDITAR

Encontrei Criando Procedimentos Armazenados (Mecanismo de Banco de Dados) no msdn para SQL Server 2008 R2, que responde à minha segunda pergunta:

Recomendamos que você não crie nenhum procedimento armazenado usando sp_ como prefixo. O SQL Server usa o prefixo sp_ para designar procedimentos armazenados do sistema. O nome escolhido pode entrar em conflito com algum procedimento futuro do sistema. [...]

Nada é mencionado lá sobre problemas de desempenho causados ​​pelo uso do sp_prefixo. Eu adoraria saber se ainda é o caso ou se eles consertaram depois do SQL Server 2000.

stored-procedures sql-server-2008-r2

4 respostas

  1. Best Answer

    Isso é bastante fácil de testar a si mesmo. Vamos criar dois procedimentos bem simples:

    CREATE PROCEDURE dbo.sp_mystuff
AS
  SELECT 'x';
GO
CREATE PROCEDURE dbo.mystuff
AS
  SELECT 'x';
GO

    Agora vamos construir um wrapper que os execute várias vezes, com e sem o prefixo do esquema:

    CREATE PROCEDURE dbo.wrapper_sp1
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @i INT = 1;
    WHILE @i <= 1000
    BEGIN
      EXEC sp_mystuff;
      SET @i += 1;
    END
END
GO
CREATE PROCEDURE dbo.wrapper_1
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @i INT = 1;
    WHILE @i <= 1000
    BEGIN
      EXEC mystuff;
      SET @i += 1;
    END
END
GO
CREATE PROCEDURE dbo.wrapper_sp2
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @i INT = 1;
    WHILE @i <= 1000
    BEGIN
      EXEC dbo.sp_mystuff;
      SET @i += 1;
    END
END
GO
CREATE PROCEDURE dbo.wrapper_2
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @i INT = 1;
    WHILE @i <= 1000
    BEGIN
      EXEC dbo.mystuff;
      SET @i += 1;
    END
END
GO

    Resultados:

    insira a descrição da imagem aqui

    Conclusões:

    • usar o prefixo sp_ é mais lento
    • deixar de fora o prefixo do esquema é mais lento

    A pergunta mais importante: por que você deseja usar o prefixo sp_? O que seus colegas de trabalho esperam ganhar com isso? Isso não deveria ser sobre você ter que provar que isso é pior, deveria ser sobre eles justificarem a adição do mesmo prefixo de três letras a cada procedimento armazenado no sistema. Não consigo ver o benefício.

    Além disso, realizei alguns testes bastante extensos desse padrão na seguinte postagem do blog:

    http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix

    • 34

  2. Recomendamos que você não crie nenhum procedimento armazenado usando sp_ como prefixo. O SQL Server usa o prefixo sp_ para designar procedimentos armazenados do sistema. O nome escolhido pode entrar em conflito com algum procedimento futuro do sistema. [...]

    Nada é mencionado lá sobre problemas de desempenho causados ​​pelo uso do prefixo sp_. Eu adoraria saber se ainda é o caso ou se eles consertaram depois do SQL Server 2000.

    Como mostra o simples comentário de Martin Smith - sim, se você tiver um procedimento armazenado com um sp_prefixo - o executor de consulta do SQL Server sempre verificará o masterbanco de dados primeiro para ver se existe um procedimento armazenado (marcado como um procedimento armazenado do sistema) com esse nome.

    E se existir, aquele procedimento armazenado do sistema do banco de masterdados sempre prevalecerá e será executado em vez do seu.

    Então sim - ainda permanece: não use o sp_prefixo.

    • 14

  3. Um teste melhor é escrever uma consulta que exija otimização total, pois provavelmente é um reflexo melhor do que o procedimento que você está escrevendo está fazendo. Envolvi a seguinte consulta em um SP e repeti seu teste e obtive os mesmos resultados.

    select * from Person.BusinessEntity b
inner join Person.BusinessEntityAddress ba on b.BusinessEntityID = ba.BusinessEntityID
inner join Person.Address a on ba.AddressID = a.AddressID

    Obtive o mesmo número de eventos de ocorrência e falha de cache em ambos os casos e, em ambos os casos, o plano foi adicionado ao cache. Também executei os dois procedimentos várias vezes e não houve diferença consistente no tempo de CPU ou no tempo decorrido relatado por dm_exec_query_stats.

    A outra preocupação é que, como os procs "sp_" podem ser executados a partir do mestre, você pode obter uma cópia do proc que foi executado no mestre em vez do banco de dados em que está trabalhando, mas um teste rápido mostrará que não é o caso. No entanto, se o procedimento for descartado do banco de dados em que você está trabalhando e existir uma cópia no mestre, ele será executado, o que pode ser um problema se for uma versão antiga. Se isso for uma preocupação, eu não usaria "sp_" para nomear o proc.

    • 2

  4. Acredito que o problema tenha a ver quando você não especifica o nome do objeto totalmente qualificado. Portanto, "EXEC sp_something" verificará o master primeiro, mas "EXEC dbname.dbo.sp_something" nunca irá para o master primeiro.

    A lição, se bem me lembro, é sempre usar o nome totalmente qualificado.

    • 1

relate perguntas