Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

One of my co-workers named a stored procedure in our SQL Server 2008 R2 database 'sp_something'. When I saw this, I immediately thought: "That is WRONG!" and started searching my bookmarks for this online article that explains why it is wrong, so I could provide my co-worker with an explanation.

In the article (by Brian Moran) it is explained that giving the stored procedure an sp_ prefix makes SQL Server look at the master database for a compiled plan. Because the sp_sproc doesn't reside there, SQL Server will recompile the procedure (and needs an exclusive compile lock for that, causing performance problems).

The following example is given in the article to show the difference between two procedures:

You run this, then open the Profiler (add the Stored Procedures -> SP:CacheMiss event) and run the stored procedures again. You're supposed to see a difference between the two sprocs: the sp_Select1 sproc will generate one more SP:CacheMiss event than the Select1 sproc. (The article references SQL Server 7.0 and SQL Server 2000.)

When I run the example in my SQL Server 2008 R2 environment, I get the same amount of SP:CacheMiss events for both procedures (both in tempdb and in another test database).

So I am wondering:

Can I have done something wrong in my execution of the example?

Is the 'do not name a user sproc sp_something' adagium still valid
in newer versions of SQL Server?

If so, is there a good example that shows its validity in SQL Server
2008 R2?

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. [...]

Nothing is mentioned there about performance problems caused by using the sp_ prefix though. I'd love to know if that's still the case or if they fixed it after SQL Server 2000.

I did look at this before and found a negligible performance difference which I put down to slightly greater overhead of resolving the sp_ versions (needs to check in both master and user databases because it prioritises system procs in master -> procs in user DB -> non system procs in master)
–
Martin SmithOct 3 '12 at 13:39

3

What benefit do you see for prefixing a stored procedure with sp_? This is about as useful as prefixing a table with tbl. Why make the system search master first (even if it's at negligible or no performance difference) to allow you to use this meaningless naming convention?
–
Aaron Bertrand♦Oct 3 '12 at 14:05

1

@AaronBertrand: to be honest, I see no benefit at all in prefixing sprocs with sp_, only disadvantages, and I would never prefix them this way myself. But I want all the arguments I can get to convince my co-workers to not do it either.
–
JosienOct 3 '12 at 14:10

1

Yeah, tbl is useless, but I still love using it. Must be my OCD kicking in. Now get off my lawn.
–
SQLRockstarOct 3 '12 at 14:11

1

In my experience, first one there gets the set these kind of conventions. In reality it almost certainly won't cause problems to have these prefixes, even though it makes no sense and gives no benefit. But if the corporate standard has been set - even if only by one person - it's pretty hard to shake it off.
–
Kirk BroadhurstOct 8 '12 at 23:33

The more important question: why would you want to use the sp_ prefix? What do your co-workers expect to gain from doing so? This shouldn't be about you having to prove that this is worse, it should be about them justifying adding the same three-letter prefix to every single stored procedure in the system. I fail to see the benefit.

Also I performed some pretty extensive testing of this pattern in the following blog post:

Note, these results are on SQL Server 2012. But you can perform the same tests in your environment.
–
Aaron Bertrand♦Oct 3 '12 at 15:03

1

"What do your co-works expect to gain from doing so" see also Hungarian Notation. Basically, it's a 90s thing for the most part. Also, at my past job the standard was to prefix every stored procedure with sp_ so that they could be differentiated from other things and no name conflicts... I had no idea this performance problem existed with it.
–
EarlzOct 3 '12 at 18:17

Great example, thanks Aaron. I'm still testing it on 2008 R2 (and probably testing it the wrong way, cause 'dbo.wrapper_sp1' and 'dbo.wrapper_sp2' seem significantly faster than the other two right now).
–
JosienOct 5 '12 at 9:55

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. [...]

Nothing is mentioned there about performance problems caused by using the sp_ prefix though. I'd love to know if that's still the case or if they fixed it after SQL Server 2000.

As Martin Smith's simple comment shows - yes, if you have a stored procedure with an sp_ prefix - the SQL Server query executor will always check in the master database first to see if a stored procedure (marked as a system stored procedure) by that name exists.

And if it exists, that system stored procedure from the master database always prevails and will be executed instead of your own.

A better test is to write a query that requires full optimization since that is likely a better reflection of what the proc you're writing is doing. I wrapped the following query in an SP and repeated your test and got the same results.

I got the same number of cache miss and hit events in both cases and in both cases the plan was added to the cache. I also ran both procs several times and there was no consistent difference in CPU time or elapsed time reported by dm_exec_query_stats.

The other concern is that since "sp_" procs can be executed from master that you may get a copy of the proc that was run into master instead of the DB you're working in but a quick test will show that's not the case. However, if the proc gets dropped from the DB you're working in and a copy exists in master then it will be executed which could be a problem if it's an old version. If this is a concern I wouldn't use "sp_" to name the proc.

I believe the issue has to do when you do not specify the fully qualified object name. So, "EXEC sp_something" will check master first, but "EXEC dbname.dbo.sp_something" will never go to master first.

Don't think that makes any difference. EXEC MyDB.dbo.sp_helptext 'sp_helptext' still uses the one from master even if there is one in the user database. AFAIK it checks both locations and will use the one from master if it exists and is marked as a system object.
–
Martin SmithOct 3 '12 at 14:18

1

@MartinSmith on 2012 I could not coerce the master version to be executed (though my tests there did show that there is something going on), unless I dropped the local copy (in which case MyDB.dbo.sp_foo still executed the master version). I don't have 2008/2008 R2 right now to confirm where this behavior changed.
–
Aaron Bertrand♦Oct 3 '12 at 14:59

Also note that if a local procedure is not found and one is found in master, the latter will be executed, and does not need to be marked as a system object for this to happen. And in 2012 at least, whether or not the master copy is marked as a system object, does not change the behavior - with or without local db/schema prefix, the local copy is always executed unless it doesn't exist.
–
Aaron Bertrand♦Oct 3 '12 at 16:34

If you create a procedure in your database with the same name as a system stored procedure, you will never be able to run your procedure. create proc dbo.sp_help … in your database - exec sp_help and exec dbo.sp_help and exec MyDB.dbo.sp_help will all run the system stored procedure. Tested just now in SQL Server 2012 Dev edition (v 11.0.2100.60).
–
Greenstone WalkerMay 14 '13 at 20:54