sql-server-2008-r2 – 使用sp_启动用户存储过程的名称仍然错误

我的一位同事在我们的SQL Server 2008 R2数据库sp_something中命名了一个存储过程.当我看到这个时,我立刻想到：“那是错的！”并开始搜索我的书签 this online article,这解释了为什么它是错误的,所以我可以向我的同事提供一个解释.

在文章(Brian Moran)中,解释了为存储过程提供sp_前缀使SQL Server查看主数据库中的已编译计划.因为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存储过程将生成比Select1存储过程多一个SP：CacheMiss事件(该文章引用SQL Server 7.0和SQL Server 2000.)

当我在SQL Server 2008 R2环境中运行该示例时,我获得了两个过程(在tempdb和另一个测试数据库中)相同数量的SP：CacheMiss事件.

所以我想知道：

>我可以在执行示例时做错了吗？
>在新版本的SQL Server中,’不要命名用户sproc sp_something’adagium仍然有效吗？
>如果是这样,是否有一个很好的例子显示它在SQL Server 2008 R2中的有效性？

非常感谢您对此的看法！

编辑

我在msdn上为SQL Server 2008 R2找到了Creating Stored Procedures (Database Engine),它回答了我的第二个问题：

We recommend that you do not create any stored procedures using sp_ as
a prefix. SQL Server uses the sp_ prefix to designate system stored
procedures. The name you choose may conflict with some future system
procedure. […]

这里没有提到使用sp_前缀导致的性能问题.我想知道是否仍然如此,或者他们是否在SQL Server 2000之后修复它.

解决方法

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

（编辑：天瑞地安资讯网_保定站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!