Sql-server – Still wrong to start the name of a user stored procedure with sp_

sql-server-2008-r2stored-procedures

One of my co-workers named a stored procedure in our SQL Server 2008 R2 database sp_something. When I saw this, I immediately thought: "That is WRONG!" and started searching my bookmarks for this online article that explains why it is wrong, so I could provide my co-worker with an explanation.

In the article (by Brian Moran) it is explained that giving the stored procedure an sp_ prefix makes SQL Server look at the master database for a compiled plan. Because the sp_sproc doesn't reside there, SQL Server will recompile the procedure (and needs an exclusive compile lock for that, causing performance problems).

The following example is given in the article to show the difference between two procedures: 

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

You run this, then open the Profiler (add the Stored Procedures -> SP:CacheMiss event) and run the stored procedures again. You're supposed to see a difference between the two stored procedures: the sp_Select1 stored procedure will generate one more SP:CacheMiss event than the Select1 stored procedure (the article references SQL Server 7.0 and SQL Server 2000.)

When I run the example in my SQL Server 2008 R2 environment, I get the same amount of SP:CacheMiss events for both procedures (both in tempdb and in another test database).

So I am wondering:

  • Can I have done something wrong in my execution of the example?
  • Is the 'do not name a user sproc sp_something' adagium still valid in newer versions of SQL Server?
  • If so, is there a good example that shows its validity in SQL Server 2008 R2?

Thanks a lot for your thoughts on this!

EDIT

I found Creating Stored Procedures (Database Engine) on msdn for SQL Server 2008 R2, which answers my second question:

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. […]

Nothing is mentioned there about performance problems caused by using the sp_ prefix though. I'd love to know if that's still the case or if they fixed it after SQL Server 2000.

Best Answer

This is fairly easy to test yourself. Let's create two very simple procedures:

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

Now let's build a wrapper that executes them a number of times, with and without the schema prefix:

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

Results:

enter image description here

Conclusions:

  • using sp_ prefix is slower
  • leaving out schema prefix is slower

The more important question: why would you want to use the sp_ prefix? What do your co-workers expect to gain from doing so? This shouldn't be about you having to prove that this is worse, it should be about them justifying adding the same three-letter prefix to every single stored procedure in the system. I fail to see the benefit.

Also I performed some pretty extensive testing of this pattern in the following blog post:

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

Related Question