Recently a thread was started with the question about the search order for

SQL Stored Procedures. This is the information they were given:

Quoted from Fredrik Hellebro in the thread Microsoft: "Don't use the prefix

sp_" found

http://www.sqlservercentral.com/forum/topic.asp?TOPIC_ID=8041&FORUM_ID=65&CAT_ID=1&Topic_Title=Microsoft%3A+%22Don%B4t+use+the+prefix+sp%5F%22&Forum_Title=Performance+Tuning

quote:

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

A few days ago I visited one of Microsoft's "The Architect Blueprint" event

in Sweden. Suddenly the "Architect Evangelist" said

"Do not preface your stored procedure names with sp_, because doing so

reduces performance. When you call a stored procedure that starts with sp_, SQL

Server always checks the master database first, even if the stored procedure is

qualified with the database name."

And today I found the statement in MSDN,

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/BOAGag.asp?frame=true

(search for "sp_").

Can this be true? I tested this :

use master

go

create procedure sp_mysp as

select 'I am the master'

go

use mydb

go

exec mydb..sp_mysp

go

create procedure sp_mysp as

select 'I am NOT the master'

go

exec mydb..sp_mysp

Result:

---------------

I am the master

-------------------

I am NOT the master

Why should SQL Server scan the master database when it decides to executes

the sp in mydb? I have logged everything in SQL Profiler and I can't find

anything strange.

/ Fredrik Hellebro

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Now if you look at SQL Books Online under the topic "Creating a Stored

Procedure" you find the following information.

System Stored Procedures

Many of your administrative activities in Microsoft® SQL Server? 2000 are

performed through a special kind of procedure known as a system stored

procedure. System stored procedures are created and stored in the master

database and have the sp_ prefix. System stored procedures can be executed from

any database without having to qualify the stored procedure name fully using the

database name master.

It is strongly recommended that you do not create any stored procedures using

sp_ as a prefix. SQL Server always looks for a stored procedure beginning with

sp_ in this order:

1. The stored procedure in the master database.
2. The stored procedure based on any qualifiers provided (database name or

   owner).

3. The stored procedure using dbo as the owner, if one is not specified.

Therefore, although the user-created stored procedure prefixed with sp_ may

exist in the current database, the master database is always checked first, even

if the stored procedure is qualified with the database name.

Important If any user-created stored procedure has the same name as a system

stored procedure, the user-created stored procedure will never be executed.

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

However, if you look back at what Fredrik submitted this cannot be correct.

If that were true the second time he submitted

exec mydb..sp_mysp

he should have gotten

I am the master

and not

I am NOT the master

There is a key issue here. Read on.

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

The issue is the fact that if you create a user defined sp with sp_ in master

and execute it from any DB it works right. But if you create a sp with sp_ with

the same name in a user db as the name given to the one in master then it will

execute the local one and not the one in master. Which based on SQL BOL

quote:

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Therefore, although the user-created stored procedure prefixed with sp_ may

exist in the current database, the master database is always checked first, even

if the stored procedure is qualified with the database name.

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

This should not be the case, the one in master should be executed. However,

if you create an item in your database named sp_help the one in master will be

executed no matter what you do. The key is in the next statement in BOL

quote:

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

If any user-created stored procedure has the same name as a system stored

procedure, the user-created stored procedure will never be executed.

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

In other words if the name is the same as a system defined stored procedure

in master and not a user one then masters copy will override the code.

Now, if you run

sp_MS_marksystemobject sp_mysp

against the Procedure you created in the master database and then execute

mydb..sp_mysp it will still run the local db copy even thou the master copy does

state type is system.

Here is the trick, restart the server now and when comes back up you will

find running mydb..sp_mysp will now execute the master copy instead.

Ultimately I believe when the server starts it creates a list in memory of

all the system procedures in master, when you call a procedure of that name the

list will trigger the master copy to be executed instead of any local copy, even

if you fully qualify.

If the item is not in the list that was created at startup then the local

copy is applied. The advantage is that an system SP in master can be accessed

quickly without needing to qualify.

Furthermore, if you test further you will find that any other first 3

characters as the procedure name will not work, even if you name the procedure

spmysp and make a system object then reboot it will still run the local copy.

So at load the server must only load procedures prefixed sp_ which does not

affect performance except at load time since it will have to compare the

procedures to find those. I also believe that unless the query manager does a

left 3 on the running sp everytime (which it may) then your procedure will be

checked against the list everytime to make sure not in the list.

Either way it would be (IMHO) safe to assume that the performance hit is the

same either way, except you don't have to worry if your object name is stepped

on by something in master.

In summation I think the search order is actually more like this.

1.  Is SP_ item matching name in master list of system procedures

   created at time of SQL Server Start.

2.  Is SP_ procedure in fully qualified path.
3.  If SP is not called via the fully qualified path does SP_ exist in

   current database.

4. If not found in any of the previous does it exist in the master DB as a

   user type or as system type (newly changed to system type after SQL Server

   startup).

I tested under SQL 7 SP4 and SQL 2000 SP2. Note: I choose to prefix ip_

(which is short individual procedure, picked this up from someone else), unless

I am creating an SP in master for code reusability.