我的一位同事在我们的SQL Server 2008 R2数据库sp_something中命​​名了一个存储过程。当我看到此消息时，我立即想到：“那是错误的！”并开始在我的书签中搜索该在线文章以解释其错误原因，因此我可以向我的同事提供解释。

（Brian Moran的文章中）解释了存储过程使用sp_前缀使SQL Server可以在master数据库中查看已编译的计划。由于sp_sproc不在此处，因此SQL Server将重新编译该过程（并为此需要一个排他的编译锁，从而导致性能问题）。

本文中给出了以下示例，以显示不同之处在两个过程之间：

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


运行此程序，然后打开Profiler（添加存储过程-> SP:CacheMiss事件）并再次运行存储过程。您应该看到这两个存储过程之间的区别：sp_Select1存储过程将比SP:CacheMiss存储过程生成一个更多的Select1事件（本文引用了SQL Server 7.0和SQL Server2000。）

当我在SQL Server 2008 R2环境中运行该示例时，两个过程（在tempdb和另一个测试数据库中）都得到相同数量的SP:CacheMiss事件。

所以我想知道：
/>

执行示例时我可以做错什么吗？
在新版本的SQL Server中，“不命名用户sproc sp_something” adagium仍然有效吗？ >如果是这样，是否有一个很好的示例可以证明它在SQL Server 2008 R2中的有效性？

非常感谢您对此的想法！我发现在msdn上为SQL Server 2008 R2创建存储过程（数据库引擎），这回答了我的第二个问题：


我们建议您不要使用usi创建任何存储过程ng sp_ as
前缀。 SQL Server使用sp_前缀来指定系统存储的
程序。您选择的名称可能与将来的某些系统
步骤冲突。 [...]


虽然没有提及由于使用sp_前缀而导致的性能问题。我很想知道这种情况是否仍然存在，或者他们是否在SQL Server 2000之后进行了修复。

评论

我之前确实看过这个，发现性能差异可以忽略不计，我将其归结为解析sp_版本的开销稍大（需要检入master和user数据库，因为它优先于master的系统proc-> user DB的proc->主系统中的非系统proc）
使用sp_为存储过程添加前缀会看到什么好处？这和用tbl前缀表一样有用。为什么要让系统搜索母版（即使它的性能可以忽略不计或没有性能差异）才能让您使用这种无意义的命名约定？
@AaronBertrand：说实话，我发现在sproc前面加sp_根本没有好处，只有缺点，而且我自己也不会这样。但我希望我能说服我的同事也不要这么做。
是的，tbl没用，但我仍然喜欢使用它。必须是我的强迫症（OCD）踢进来。现在就离开草坪。
同样，@ Josien，您的同事应该提出使命名方案更加复杂的论点。让他们解释为什么dbo.sp_Author_Rename比dbo.Author_Rename更好。我想不出一件有意义的事。

#1 楼

这很容易测试自己。让我们创建两个非常简单的过程：

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


现在，我们来构建一个包装器，该包装器在执行和不执行模式前缀的情况下可以多次执行它们： /> 
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


结果：



结论：


使用sp_前缀较慢
删除模式前缀会更慢

更重要的问题：为什么要使用sp_前缀？您的同事希望从中获得什么？这不应该与您不得不证明这种情况更糟有关，而应该与他们证明在系统中的每个存储过程中添加相同的三个字母前缀有关。我看不到好处。

我还在以下博客文章中对此模式进行了相当广泛的测试：

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

评论

请注意，这些结果是在SQL Server 2012上得出的。但是您可以在环境中执行相同的测试。

–亚伦·伯特兰（Aaron Bertrand）
2012年10月3日15:03
“您的合作期望从中获得什么”，另请参见匈牙利符号。基本上，这是90年代的事情。另外，在我过去的工作中，标准是在每个存储过程中都以sp_作为前缀，以便可以将它们与其他事物区分开，并且不会出现名称冲突……我不知道这个性能问题是否存在。

–埃尔兹
2012年10月3日在18:17
很好的例子，谢谢亚伦。我仍在2008 R2上对其进行测试（并且可能以错误的方式对其进行了测试，导致“ dbo.wrapper_sp1”和“ dbo.wrapper_sp2”似乎比现在的其他两个快得多）。

–user5147
2012年10月5日，9：55

#2 楼


我们建议您不要使用sp_作为前缀创建任何存储过程。 SQL Server使用sp_前缀来指定系统存储过程。您选择的名称可能与将来的某些系统过程冲突。 [...]
这里没有提及使用sp_前缀引起的性能问题。我想知道在SQL Server 2000之后是否仍然存在，或者是否已将其修复。正如Martin Smith的简单注释所示-是的，如果您有一个带有sp_前缀的存储过程- SQL Server查询执行程序将始终总是首先检查master数据库，以查看是否存在具有该名称的存储过程（标记为系统存储过程）。
如果存在，则始终以master数据库中的系统存储过程为准并且将被执行，而不是您自己执行。
是的-它仍然存在：请勿使用sp_前缀。

评论

测试简单。创建PROC dbo.sp_helptext AS SELECT 1，然后尝试EXEC dbo.sp_helptext

–马丁·史密斯
2012年10月3日14:23


感谢您的回答，这对master sp的流行非常有用。

–user5147
2012年10月5日9:59

#3 楼

更好的测试是编写一个需要完全优化的查询，因为这可能更好地反映了您正在编写的proc在做什么。我在SP中包装了以下查询，并重复了测试，并获得了相同的结果。

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


在两种情况下，我都得到了相同数量的缓存未命中和命中事件在两种情况下，都将计划添加到缓存中。我还两次运行了两个proc，并且dm_exec_query_stats报告的CPU时间或运行时间没有一致的差异。

另一个问题是，由于可以从master执行“ sp_” proc，因此您可能会得到运行到master而不是您正在使用的DB的proc副本，但快速测试将显示情况并非如此。但是，如果proc从您正在使用的数据库中删除，并且master中存在一个副本，则将执行该副本，如果它是旧版本，则可能会出现问题。如果这是一个问题，我不会使用“ sp_”来命名该过程。

评论

有趣的发现，谢谢！我将您的示例与Aaron的示例结合使用，以运行更多测试。

–user5147
2012年10月5日，12：23

#4 楼

我相信当您不指定完全限定的对象名称时，就必须解决此问题。因此，“ EXEC sp_something”将首先检查主服务器，但“ EXEC dbname.dbo.sp_something”将永远不会首先进入主服务器。

如果我记得，该课程是始终使用完全限定的名称。

评论

不要以为有什么区别。即使用户数据库中有一个，EXEC MyDB.dbo.sp_helptext'sp_helptext'仍使用master中的一个。 AFAIK会检查这两个位置，如果存在并标记为系统对象，则将使用主数据库中的一个。

–马丁·史密斯
2012年10月3日14:18


@MartinSmith在2012年，我无法强制执行要执行的主版本（尽管那里的测试确实表明正在发生问题），除非我删除了本地副本（在这种情况下，MyDB.dbo.sp_foo仍然执行了主版本） 。我现在没有2008/2008 R2来确认此行为已更改。

–亚伦·伯特兰（Aaron Bertrand）
2012年10月3日14:59
@AaronBertrand-啊，有趣的是我在2008 R2上做了测试。

–马丁·史密斯
2012年10月3日15:00
还要注意，如果找不到本地过程，而在master中找到了本地过程，则将执行后者，并且不需要将其标记为系统对象。而且至少在2012年，无论主副本是否被标记为系统对象，都不会更改行为-带有或不带有本地db / schema前缀的本地副本始终执行，除非不存在。

–亚伦·伯特兰（Aaron Bertrand）
2012年10月3日在16:34
糟糕，我应该澄清一下，我的评论针对的是建议的答案。 SQLRockstar的评论“ EXEC dbname.dbo.sp_something永远不会先成为master。”是不正确的。

–格林斯通·沃克
13年7月4日在4:50