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.
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".
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:
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.
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:
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.
You also get the same behavior if you go through this exercise with a table:
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.
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:
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.
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.