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
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?