x

Stored procs with sp_ prefix

I've inherited a database where all the user defined stored procedures have all been prefixed with sp_. Does this really make any difference these days?

I read that there's a performance overhead because this causes the engine to look for that proc in master before going back to the current database. So I looked in the Profiler and, sure enough, there's a CacheMiss, CacheHit sequence that supports this. So I copied a proc and prefixed it with prc instead but it looks exactly the same in the Profiler. The first call shows CacheMiss, CacheInsert and subsequent calls show CacheMiss, CacheHit. Just the same as with the sp_ prefix.

Incidentally, I included the SP:Recompile event but I'm not seeing this at all, which is a bit odd. I thought it should fire on the first call but it doesn't.

All Books Online says is:

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. If your application uses nonschema qualified name references and your own procedure name conflicts with a system procedure name, your application will break because the name binds to the system procedure, not your own.

A user-defined stored procedure that has the same name as a system stored procedure and is either nonqualified or is in the dbo schema will never be executed; the system stored procedure will always execute instead.

Here are some claims I have read about the effect of the prefix:

  • These procs are recompiled every time they are called. Really? I see a clear difference between the first and subsequent calls and the execution time is dramatically slower for the first call of a complex query.
  • Your own proc will be called instead of a system proc with the same name as long as you qualify it with a schema. This doesn't work if the schema is dbo, which is what I use so I didn't bother testing any further.
  • After failing to find the proc in master, the engine applies a compile lock because it assumes it will have to compile a plan and this blocks other procedure compilations. This sounds feasible but I'm still a bit dubious. Surely the obvious optimisation would be to check the cache for a plan first and decide that a compilation is really needed and then apply the lock.
  • I've even read a blog where the guy insisted that the sp_ prefix automagically makes it a system stored proc that becomes available in every database just like the Microsoft ones. Not on my server it doesn't!

So I'm a bit confused. I can't justify the work to fix this if I can't demonstrate any difference. Does anyone know what's going on here please and, perhaps more importantly, can you back it up with authoritative references?

more ▼

asked Nov 16, 2009 at 08:57 AM in Default

David Wimbush gravatar image

David Wimbush
4.9k 28 30 33

(comments are locked)
10|1200 characters needed characters left

6 answers: sort voted first

Thanks for all your views. OK, in the interests of clarity, I'm going to have a go at summarising everyone's points into one clear answer. Please comment if you think I've still got something wrong and we'll sort out what's right and I'll correct this answer if necessary.

Here goes.

The prefix is a bad idea because:

  1. SQL Server expects that this is a system stored proc so it looks in master first. This is a small overhead but it's worth avoiding if you can.

  2. If you use the same name as a system stored proc SQL Server will always execute the system proc and not yours, no matter how clearly you qualify the call.

Notes:

a) A system stored procedure is a procedure that is specifically marked as such. It's not the sp_ on the front that makes it a system procedure. That's just a naming convention.

b) If there are non-system procedures with identical names in master and a user database, the usual context rules apply when the engine decides which one to call. If you're in the user database and you want to execute the proc in master, you'll have to clear qualify it with the 3 part name.

c) If you are in a user database and you call a proc that starts with sp_ without qualifying it with a database name, it will check both that database and master. If it finds a local proc it will execute that. If there's no local proc but it finds one in master, it will execute that.

more ▼

answered Nov 18, 2009 at 04:25 PM

David Wimbush gravatar image

David Wimbush
4.9k 28 30 33

Just want to clarify that (c) For non_system procedures beginning with sp_, the system will look for a copy of the procedure in the master db if it doesn't first find a copy in the user db.
Nov 18, 2009 at 11:13 PM Bob Hovious
Good point, Bob. Thanks. I was so busy looking at it the other way round I missed that. Note c) added.
Nov 19, 2009 at 06:01 AM David Wimbush
(comments are locked)
10|1200 characters needed characters left

Ok, a couple of pointers.

First of all, the cache miss/compile lock scenario you're seeing is partially explained in my answer to this question. There's a couple of reference links in there. It sounds to me like the behaviour you're seeing is due to non-schema qualified references, rather than the fact that your procs are named sp_...

The guy saying that sp_ automagically makes it a system proc is talking rubbish. You can create your own procedures in the master database which can then be accessed from all DBs, but they are still not system procedures. I call them 'user system procedures'. System procedures have elevated privileges, such as access to internal tables that you wouldn't even realise are there unless you scripted out some existing system procedures and had a look at the source.

Hope they help.

more ▼

answered Nov 16, 2009 at 09:03 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

Thanks Matt, but when I test it I'm not observing any difference between calling dbo. and , or between a proc that starts with sp_ and an identical one that doesn't. I ran the tests from the article at http://www.sqlservercentral.com/articles/T-SQL/62061/ and the results are the same as with my own procs. After the initial call to get a plan into cache the pattern I see in the Profiler is always: SP:CacheMiss SQL:BatchStarting SQL:StmtStarting SP:CacheHit SP:Starting SP:StmtStarting SP:StmtCompleted SP:Completed SQL:StmtCompleted SQL:BatchCompleted
Nov 16, 2009 at 10:52 AM David Wimbush
I would imagine that the SP you're calling also contains non-qualified references - but, like Grant says, if you're struggling to justify it, then maybe it's not actually worth it?
Nov 16, 2009 at 04:53 PM Matt Whitfield ♦♦
@David FWIW I looked at this a few weeks ago and found an average hit of only about 1 µs per call (presumably due to the lookup in master first). There was no difference in cache miss events or compile locks as this article suggests - but the article is old and presumably refers to SQL Server 2000. To avoid the spurious cache miss events you need to make sure that you are calling it as an RPC event. See here for details on this.
Jun 22, 2011 at 11:22 AM Martin 1
(comments are locked)
10|1200 characters needed characters left

The performance hit for this sort of thing is exceedingly small. If you're building a system from scratch, avoid the hit and don't code that way. If you've got an existing system, you're paying the hit, but usually there are much more important things you need to be working on in your system. This should absolutely be avoided because, after all, why pay the hit if you don't have to, but it's not a showstopper (under most circumstances) so I wouldn't spend a lot of time worrying about it.

more ▼

answered Nov 16, 2009 at 01:55 PM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
98.5k 19 21 74

(comments are locked)
10|1200 characters needed characters left

I think the key point is what your BOL quote states. I have heard, I have not tested (I'm not even sure how to), that SQL Server will first look in master for any procedures prefixed with "sp_" which will cause a small performance hit, just like when you do not schema-qualify an object the users default schema is checked first, then dbo.

more ▼

answered Nov 16, 2009 at 11:23 AM

Jack Corbett gravatar image

Jack Corbett
1.1k 2 2 3

(comments are locked)
10|1200 characters needed characters left

In SQL 2008, if a user created stored procedure begins with sp_ , the local database is searched first and THEN the master database. This behavior can be verified using the following code, substituting your local database of choice for my sandcastle db. However, system procs will not be overriden by local procs with the same name. (See discussion in comments below.)

However, I still think using SP_ is a questionable practice, as it creates the possibility that you might have two different versions of the same procedure, and one might get called by mistake. In the example below, if I intended to call sandcastle.dbo.sp_testproc from the model database but forgot to qualify the object name with the dbname, I'm still going to get output. It just may not be the result set I expected. If the schema of the result set isn't changed, but logic has changed in the sandcastle version, confusion may ensue.

Use SandCastle_BobH; go

select @@version

use master; go CREATE PROCEDURE dbo.sp_testProc AS BEGIN SET NOCOUNT ON; select 'Run from MASTER database' END GO

use sandcastle_BobH; exec sp_testproc go

Use SandCastle_BobH; go

CREATE PROCEDURE dbo.sp_testProc AS BEGIN SET NOCOUNT ON; select 'Run from local database' END GO

use sandcastle_BobH; exec sp_testproc go

use model; exec sp_testproc go

use master; drop procedure dbo.sp_testProc go

use sandcastle_Bobh drop procedure dbo.sp_testproc go
more ▼

answered Nov 16, 2009 at 06:55 PM

Bob Hovious gravatar image

Bob Hovious
1.6k 5 6 9

Thanks, Bob. I guess the key point is that sp_ suggests it's a system proc, it doesn't mean it is one. This is demonstrated by the following code, which will run the system proc whatever you try:

use sandcastle_BobH; go

create proc dbo.sp_help as select 'This is my own sp_help' go

exec sp_help; exec dbo.sp_help; exec sandcastle_BobH.dbo.sp_help;
Nov 17, 2009 at 08:01 AM David Wimbush

David, the 'sp_' prefix is handy for creating "universal" procs in the master db. The prefix even works for other db objects like functions and table. But because 'sp_' is associated with those characteristics (and the master db), I would avoid using it unless you deliberately intend for an object to live in the master db and have those characteristics.

----------- use master; go

create table dbo.sp_universal (id int) insert into dbo.sp_universal select 999 go

use sandcastle_bhovious; select * from sp_universal go

drop table master.dbo.sp_universal
Nov 17, 2009 at 12:50 PM Bob Hovious
One last comment: When you think about it, it makes perfect sense that they wouldn't allow you to override the functionality of system procs. My statement about the local database being searched first is probably incorrect. It seems that both local and master dbs are searched and then prioritized by (1) system procs (2) local procs (3) non-system procs in the master db.
Nov 17, 2009 at 12:55 PM Bob Hovious
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1945
x408

asked: Nov 16, 2009 at 08:57 AM

Seen: 5356 times

Last Updated: Nov 16, 2009 at 09:05 AM