I've inherited a database where all the user defined stored procedures have all been prefixed with
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
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:
Here are some claims I have read about the effect of the prefix:
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?
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.
The prefix is a bad idea because:
a) A system stored procedure is a procedure that is specifically marked as such. It's not the
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
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.
answered Nov 16, 2009 at 09:03 AM
Matt Whitfield ♦♦
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.
answered Nov 16, 2009 at 01:55 PM
Grant Fritchey ♦♦
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.
answered Nov 16, 2009 at 11:23 AM
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.