Home » Blog » The Mysterious “sp_” System Procedure Prefix

The Mysterious “sp_” System Procedure Prefix

2012-05-13 - General, Performance, SQL Server Internals

Introduction

If you have been working with SQL Server for a while you probably know that you should not select names for your stored procedures that start with the three characters "sp_".

Today I would like to take a closer look at all the myths and facts around this prefix. This will include a series of examples, all of which have been tested on SQL 2008R2 and on SQL 2012.

Special Objects

The first myth I would like to bust is that the "sp_" prefix stands for "system procedure".

While the BOL entry for Creating Stored Procedures uses language that could easily be interpreted like that, it actually never says anything about the etymology of this prefix. In fact, the "sp_" prefix causes a special path to be taken in the resolution of the object name. This works for stored procedures as well as for other object types. Later on this article contains an example with a table.

As "sp_" designates more objects than just stored procedures as special, it clearly does not mean "System Procedure", even though it currently is only used for system procedures. Instead it just stands for "special".

Resource Database

The idea for this article came from a tweet in a discussion about this prefix started by @ericstephani. In the tweet in question @SirSQL suggested to check if master was indeed the database checked first, as everyone in the discussion had assumed before, or if it rather is the resource database. This makes a lot of sense, as most of the system objects live in the resource database. So I decided to check this out by running a series of tests.

If you want to follow along with these tests you will need two databases: One with the name test and one that is called OtherDb.

For the first test I created a procedure with the name sp_executesql in master as well as in test and then executed it from both places:

[sql] USE master;
GO
CREATE PROCEDURE dbo.sp_executesql
@statement NVARCHAR(MAX)
AS
BEGIN
SELECT DB_NAME() [called from], 'master' [sp_executesql];
END
GO
EXEC dbo.sp_executesql @statement = N'SELECT DB_NAME() [called from], ''system'' [sp_executesql];';
GO
DROP PROCEDURE dbo.sp_executesql;
GO
------------------------------------------------
GO
USE test;
GO
CREATE PROCEDURE dbo.sp_executesql
@statement NVARCHAR(MAX)
AS
BEGIN
SELECT DB_NAME() [called from], 'test' [sp_executesql];
END
GO
EXEC dbo.sp_executesql @statement = N'SELECT DB_NAME() [called from], ''system'' [sp_executesql];';
GO
DROP PROCEDURE dbo.sp_executesql;
GO
[/sql]

The sp_executesql version that gets shipped with SQL Server lives in the resource database and so we would expect this version to get executed in all cases. This is indeed correct, as you can see in the result:

The above test shows that the resource database is checked first when an object name with the "sp_" prefix is encountered. That means that if you create any object with that name prefix you are running the risk that it will not be accessible anymore after the next service pack install if Microsoft decided to create an object with the same name in the resource database. This is even true when the resource database object is of a different type than your object. This you can quickly confirm by creating a table with the name sp_executesql in any database and trying to insert a row into it.

Master Piece

The fact that the resource database is the one checked first to resolve "sp_" object names raises the question where the master database fits in. Read on, as the results might surprise you.

The second test involves a procedure that is not (yet) part of the resource database: sp_MyOwnProc

I again created this procedure in master and in test:

[sql] GO
USE master;
GO
CREATE PROCEDURE sp_MyOwnProc
AS
BEGIN
SELECT DB_NAME() [called from], 'master' [sp_MyOwnProc];
END
GO
USE test;
GO
CREATE PROCEDURE sp_MyOwnProc
AS
BEGIN
SELECT DB_NAME() [called from], 'test' [sp_MyOwnProc];
END
GO
EXEC sp_MyOwnProc;
GO
USE OtherDb;
GO
EXEC sp_MyOwnProc;
GO
USE test;
GO
DROP PROCEDURE sp_MyOwnProc;
GO
USE master;
GO
DROP PROCEDURE sp_MyOwnProc;
GO
[/sql]

The above code creates the procedure in both places. It then executes the EXEC sp_MyOwnProc; statement, first from the test database and afterwards from the OtherDb database. The result is shown below:

As you can see, master is not checked first for an "sp_" object. Instead the object in the current database is used. Only if the current database does not contain the object in question master is looked at to see if the object exists in there.

The same behavior can be observed when all references to the sp_MyOwnProc in the above script are schema-qualified with dbo.

Other Object Types

You also get the same behavior if you go through this exercise with a table:

[sql] USE master;
GO
CREATE TABLE sp_MyOwnTable(
sp_MyOwnTable NVARCHAR(MAX)
);
INSERT INTO sp_MyOwnTable SELECT DB_NAME();
GO
USE test;
GO
CREATE TABLE sp_MyOwnTable(
sp_MyOwnTable NVARCHAR(MAX)
);
INSERT INTO sp_MyOwnTable SELECT DB_NAME();
GO
SELECT DB_NAME() [called from], * FROM sp_MyOwnTable;
GO
USE OtherDb;
GO
SELECT DB_NAME() [called from], * FROM sp_MyOwnTable;
GO
USE test;
GO
DROP TABLE sp_MyOwnTable;
GO
USE master;
GO
DROP TABLE sp_MyOwnTable;
GO
[/sql]

This script creates a table with the name sp_MyOwnTable in the master as well as in the test database and then executes a select against this name executing in test as well as in OtherDb. The result is shown here:

This shows that the name resolution works the same for tables as it does for stored procedures. It also works views, but not for functions or user defined types.

Performance Impact

Now we know that SQL Server tries to find an "sp_" object in the resource database first and only if it does not exist there the search is continued in the current database. So there should be a measurable performance impact showing this extra work.

To measure the impact I used this script:

[sql] USE test;
GO
IF OBJECT_ID('dbo.sp_MyOwnProc2') IS NOT NULL DROP PROCEDURE dbo.sp_MyOwnProc2;
GO
CREATE PROCEDURE dbo.sp_MyOwnProc2
AS
RETURN 0;
GO
IF OBJECT_ID('dbo.MyOwnProc2') IS NOT NULL DROP PROCEDURE dbo.MyOwnProc2;
GO
CREATE PROCEDURE dbo.MyOwnProc2
AS
RETURN 0;
GO
------------------------------------
GO
DECLARE @StartTime DATETIME2 = SYSDATETIME();
DECLARE @EndTime DATETIME2 = SYSDATETIME();
DECLARE @Counter INT = 0;
DECLARE @CmdA NVARCHAR(100) = 'EXEC dbo.MyOwnProc2;--';
DECLARE @CmdB NVARCHAR(100) = 'EXEC dbo.sp_MyOwnProc2;--';
DECLARE @TimeA BIGINT=0;
DECLARE @TimeB BIGINT=0;
DECLARE @Cmd2 NVARCHAR(100);
WHILE(@Counter<10000000)
BEGIN
SET @Cmd2 = @CmdA + CAST(@Counter AS NVARCHAR(20));
SET @StartTime = SYSDATETIME();
EXEC(@Cmd2);
SET @EndTime = SYSDATETIME();
SET @TimeA += DATEDIFF(microsecond,@StartTime,@EndTime)
SET @Cmd2 = @CmdB + CAST(@Counter AS NVARCHAR(20));
SET @StartTime = SYSDATETIME();
EXEC(@Cmd2);
SET @EndTime = SYSDATETIME();
SET @TimeB += DATEDIFF(microsecond,@StartTime,@EndTime)
SET @Counter += 1;
END
SELECT @Counter Counter,@TimeA [MyOwnProc2], @TimeB [sp_MyOwnProc2];
GO
[/sql]

It first creates two identical stored procedures that do nothing but return a 0. The first one is called sp_MyOwnProc2, the second one carries the name MyOwnProc2 without the "sp_" prefix. To measure the performance impact, the script calls both procedures alternating in a loop and records their execution times. Each call gets executed as dynamic sql with the current loop count being part of the sql string. This prevents any possible attempts to cache the plan for the batch. It does however not prevent caching the plan for the procedure itself. That is okay, as we are after the name resolution piece of the execution. Because both procedures get called alternatingly, any background noise caused by other processes on the test system should affect both evenly.

On my system I got these results:

After 10 million executions of each of the two procedures you can see, that there is a performance impact. With less than 2 percent, however, it is very small compared to the time it takes to just call the procedure. Remember, that the procedures in this test did not actually do any work, so all the time recorded by this test was spent on identifying the procedure and the overhead of calling it.

Conclusion

The "sp_" object name prefix causes SQL Server to take a special route when resolving the name of this object: First SQL Server checks if the object exists in the hidden resource database. Second it tries to find the object in the current database and if it does not exist there SQL Server goes on to check if the object exists in the master database.

If you name your own objects using this prefix, you have to be aware of two possible consequences:
First there is a small but measurable impact on performance. Second it can cause your application to suddenly break, if Microsoft decides to add an object with the same name to the resource database.

Because of that it is a best practice to not use the "sp_" name prefix anywhere in your code.

There is one exception however: If you are creating an object that you want to be accessible from all databases, you can use this prefix and place the object in the master database. However, because of the database precedence there are now two possibilities for those objects to get eclipsed by another object. So, if you go this route, make sure to regularly check that the object you are trying to execute is actually the one executing.