Still wrong to start the name of a user stored procedure with sp_?

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:

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

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.

As Martin Smith's simple comment shows - yes, if you have a stored procedure with an sp_ prefix - the SQL Server query executor will always check in the master database first to see if a stored procedure (marked as a system stored procedure) by that name exists.

And if it exists, that system stored procedure from the master database always prevails and will be executed instead of your own.

So yes - it still stands: don't use the sp_ prefix.

A better test is to write a query that requires full optimization since that is likely a better reflection of what the proc you're writing is doing. I wrapped the following query in an SP and repeated your test and got the same results.

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

I got the same number of cache miss and hit events in both cases and in both cases the plan was added to the cache. I also ran both procs several times and there was no consistent difference in CPU time or elapsed time reported by dm_exec_query_stats.

The other concern is that since "sp_" procs can be executed from master that you may get a copy of the proc that was run into master instead of the DB you're working in but a quick test will show that's not the case. However, if the proc gets dropped from the DB you're working in and a copy exists in master then it will be executed which could be a problem if it's an old version. If this is a concern I wouldn't use "sp_" to name the proc.