Is the sp_ prefix still a no-no?

In the SQL Server world, there are two types of people: those who like all of their objects to be prefixed, and those who don't. The former group is further divided into two categories: those who prefix stored procedures with sp_, and those who choose other prefixes (such as usp_ or proc_). A long-standing recommendation has been to avoid the sp_ prefix, both for performance reasons, and to avoid ambiguity or collisions if you happen to choose a name that already exists in the system catalog. Collisions are certainly still an issue, but assuming you've vetted your object name, is it still a performance issue?

TL;DR version: YES.

The sp_ prefix is still a no-no. But in this post I will explain why, how SQL Server 2012 might lead you to believe that this cautionary advice no longer applies, and some other potential side effects of choosing this naming convention.

What is the issue with sp_?

The sp_ prefix does not mean what you think it does: most people think sp stands for "stored procedure" when in fact it means "special." Stored procedures (as well as tables and views) stored in master with an sp_ prefix are accessible from any database without a proper reference (assuming a local version does not exist). If the procedure is marked as a system object (using sp_MS_marksystemobject (an undocumented and unsupported system procedure that sets is_ms_shipped to 1), then the procedure in master will execute in the context of the calling database. Let's look at a simple example:

CREATE DATABASE sp_test;
GO
USE sp_test;
GO
CREATE TABLE dbo.foo(id INT);
GO
USE master;
GO
CREATE PROCEDURE dbo.sp_checktable
AS
  SELECT DB_NAME(), name 
    FROM sys.tables WHERE name = N'foo';
GO
USE sp_test;
GO
EXEC dbo.sp_checktable; -- runs but returns 0 results
GO
EXEC master..sp_MS_marksystemobject N'dbo.sp_checktable';
GO
EXEC dbo.sp_checktable; -- runs and returns results
GO

Results:

(0 row(s) affected)

sp_test    foo

(1 row(s) affected)

The performance issue comes from the fact that master might be checked for an equivalent stored procedure, depending on whether there is a local version of the procedure, and whether there is in fact an equivalent object in master. This can lead to extra metadata overhead as well as an additional SP:CacheMiss event. The question is whether this overhead is tangible.

So let's consider a very simple procedure in a test database:

CREATE DATABASE sp_prefix;
GO
USE sp_prefix;
GO
CREATE PROCEDURE dbo.sp_something
AS
BEGIN
  SELECT 'sp_prefix', DB_NAME();
END
GO

And equivalent procedures in master:

USE master;
GO
CREATE PROCEDURE dbo.sp_something
AS
BEGIN
  SELECT 'master', DB_NAME();
END
GO
EXEC sp_MS_marksystemobject N'sp_something';

CacheMiss : Fact or Fiction?

If we run a quick test from our test database, we see that executing these stored procedures will never actually invoke the versions from master, regardless of whether we properly database- or schema-qualify the procedure (a common misconception) or if we mark the master version as a system object:

USE sp_prefix;
GO
EXEC sp_prefix.dbo.sp_something;
GO
EXEC dbo.sp_something;
GO
EXEC sp_something;

Results:

sp_prefix    sp_prefix
sp_prefix    sp_prefix
sp_prefix    sp_prefix

Let's also run a Quick Trace® using SQL Sentry to observe whether there are any SP:CacheMiss events:

We see CacheMiss events for the ad hoc batch that calls the stored procedure (since SQL Server generally won't bother caching a batch that consists primarily of procedure calls), but not for the stored procedure itself. Both with and without the sp_something procedure existing in master (and when it exists, both with and without it being marked as a system object), the calls to sp_something in the user database never "accidentally" call the procedure in master, and never generate any CacheMiss events for the procedure.

This was on SQL Server 2012. I repeated the same tests above on SQL Server 2008 R2, and found slightly different results:

So on SQL Server 2008 R2 we see an additional CacheMiss event that does not occur in SQL Server 2012. This occurs in all scenarios (no equivalent object master, an object in master marked as a system object, and an object in master not marked as a system object). Immediately I was curious whether this additional event would have any noticeable impact on performance.

Performance Issue: Fact or Fiction?

I made an additional procedure without the sp_ prefix to compare raw performance, CacheMiss aside:

USE sp_prefix;
GO
CREATE PROCEDURE dbo.proc_something
AS
BEGIN
  SELECT 'sp_prefix', DB_NAME();
END
GO

So the only difference between sp_something and proc_something. I then created wrapper procedures to execute them 1000 times each, using EXEC sp_prefix.dbo.<procname>, EXEC dbo.<procname> and EXEC <procname> syntax, with equivalent stored procedures living in master and marked as a system object, living in master but not marked as a system object, and not living in master at all.

USE sp_prefix;
GO
CREATE PROCEDURE dbo.wrap_sp_3part
AS
BEGIN
  DECLARE @i INT = 1;
  WHILE @i <= 1000
  BEGIN
    EXEC sp_prefix.dbo.sp_something;
    SET @i += 1;
  END
END
GO
CREATE PROCEDURE dbo.wrap_sp_2part
AS
BEGIN
  DECLARE @i INT = 1;
  WHILE @i <= 1000
  BEGIN
    EXEC dbo.sp_something;
    SET @i += 1;
  END
END
GO
CREATE PROCEDURE dbo.wrap_sp_1part
AS
BEGIN
  DECLARE @i INT = 1;
  WHILE @i <= 1000
  BEGIN
    EXEC sp_something;
    SET @i += 1;
  END
END
GO
-- repeat for proc_something

Measuring runtime duration of each wrapper procedure with SQL Sentry Plan Explorer, the results show that using the sp_ prefix has a significant impact on average duration in almost all cases (and certainly on average):

We also see that the performance of SQL Server 2012 trends much better than the performance on SQL Sevrer 2008 R2 - no other variables are different. Both instances are on the same host, and neither is under memory or other pressure of any kind. This could be a combination of the additional CacheMiss event and those transparent improvements you get from enhancements made to the database engine between versions.

Another side effect : Ambiguity

If you create a stored procedure that references an object you created, say dbo.sp_helptext, and you didn't realize (or didn't care) that this name collides with a system procedure name, then there is potential ambiguity when someone is reviewing your stored procedure. They will most likely assume you meant the system procedure, not a different procedure you created that happens to share its name.

Another interesting thing happens when you create a stored procedure that references a stored procedure prefixed with sp_ that just happens to also exist in master. Let's pick an existing procedure that you might not be immediately familiar with (and therefore might be a more likely representative of the scenario I'm describing): sp_resyncuniquetable.

CREATE PROCEDURE dbo.test1
AS
BEGIN
  EXEC dbo.sp_resyncuniquetable;
END
GO

In Management Studio, IntelliSense doesn't underline the stored procedure name as invalid, because there is a valid procedure with that name in master. So without seeing a squiggly line underneath, you might assume the procedure is already there (and assuming the procedure in master can be executed without error, this might pass QA/testing as well). If you choose a different name for your resync procedure, let's say proc_resyncuniquetable, there is absolutely no chance for this ambiguity (unless someone manually created that procedure in master, which I guess could happen). If the procedure doesn't exist yet, the caller will still be created successfully (due to deferred name resolution), but you will receive this warning:

The module 'test1' depends on the missing object 'dbo.proc_resyncuniquetable'. The module will 
still be created; however, it cannot run successfully until the object exists.

One more source of ambiguity can occur in this scenario. The following sequence of events is entirely plausible:

1. You create the initial version of a procedure, say, sp_foo.
2. The deployer accidentally creates a version in master (and maybe notices, or maybe doesn't, but in either case doesn't clean up).
3. The deployer (or someone else) creates the procedure, this time in the right database.
4. Over time, you make multiple modifications to your_database.dbo.sp_foo.
5. You replace sp_foo with sp_superfoo, and delete sp_foo from the user database.
6. When updating the application(s) to reference the new stored procedure, you might miss a replacement or two for various reasons.

So in this scenario, the application is still calling sp_foo, and it's not failing - even though you've deleted the local copy - since it finds what it thinks is an equivalent in master. Not only is this stored procedure in master not equivalent to sp_superfoo, it's not even equivalent to the latest version of sp_foo.

"Procedure not found" is a much easier problem to troubleshoot than "Procedure doesn't exist - but code calling it works, and doesn't quite return the expected results."

Conclusion

I still think that, even though the behavior has changed slightly in SQL Server 2012, you shouldn't be using the sp_ prefix at any time, unless your intention is to create a stored procedure in master *and* mark it as a system object. Otherwise you are exposed to these performance issues as well as potential ambiguity on multiple fronts.

And personally, I don't think stored procedures need to have any prefix at all - but I have less tangible evidence to convince you of that, other than asking you what other type of object could it possible be? You can't execute a view, or a function, or a table...

As I suggest often, I don't really care what your naming convention is, as long as you're consistent. But I think you should avoid potentially harmful prefixes like sp_.