In the SQL Server world, there are two types of people: those who like all of their objects to be prefixed, and those who don’t. The former group is further divided into two categories: those who prefix stored procedures with
sp_, and those who choose other prefixes (such as
proc_). A long-standing recommendation has been to avoid the
sp_ prefix, both for performance reasons, and to avoid ambiguity or collisions if you happen to choose a name that already exists in master. Collisions are certainly still an issue, but assuming you’ve vetted your object name, is it still a performance issue?
TL;DR version: YES.
The sp_ prefix is still a no-no. But in this post I will explain why, how SQL Server 2012 might lead you to believe that this cautionary advice no longer applies, and some other potential side effects of choosing this naming convention.
What is the issue with sp_?
sp_ prefix does not mean what you think it does: most people think
sp stands for “stored procedure” when in fact it means “special.” Stored procedures (as well as tables and views) stored in master with an
sp_ prefix are accessible from any database without a proper reference (assuming a local version does not exist). If the procedure is marked as a system object (using
sp_MS_marksystemobject (an undocumented and unsupported system procedure that sets
is_ms_shipped to 1), then the procedure in master will execute in the context of the calling database. Let’s look at a simple example:
CREATE DATABASE sp_test; GO USE sp_test; GO CREATE TABLE dbo.foo(id INT); GO USE master; GO CREATE PROCEDURE dbo.sp_checktable AS SELECT DB_NAME(), name FROM sys.tables WHERE name = N'foo'; GO USE sp_test; GO EXEC dbo.sp_checktable; -- runs but returns 0 results GO EXEC master..sp_MS_marksystemobject N'dbo.sp_checktable'; GO EXEC dbo.sp_checktable; -- runs and returns results GO
(0 row(s) affected) sp_test foo (1 row(s) affected)
The performance issue comes from the fact that master might be checked for an equivalent stored procedure, depending on whether there is a local version of the procedure, and whether there is in fact an equivalent object in master. This can lead to extra metadata overhead as well as an additional
SP:CacheMiss event. The question is whether this overhead is tangible.
So let’s consider a very simple procedure in a test database:
CREATE DATABASE sp_prefix; GO USE sp_prefix; GO CREATE PROCEDURE dbo.sp_something AS BEGIN SELECT 'sp_prefix', DB_NAME(); END GO
And equivalent procedures in master:
USE master; GO CREATE PROCEDURE dbo.sp_something AS BEGIN SELECT 'master', DB_NAME(); END GO EXEC sp_MS_marksystemobject N'sp_something';
CacheMiss : Fact or Fiction?
If we run a quick test from our test database, we see that executing these stored procedures will never actually invoke the versions from master, regardless of whether we properly database- or schema-qualify the procedure (a common misconception) or if we mark the master version as a system object:
USE sp_prefix; GO EXEC sp_prefix.dbo.sp_something; GO EXEC dbo.sp_something; GO EXEC sp_something;
sp_prefix sp_prefix sp_prefix sp_prefix sp_prefix sp_prefix
Let’s also run a Quick Trace
CacheMiss events for the ad hoc batch that calls the stored procedure (since SQL Server generally won’t bother caching a batch that consists primarily of procedure calls), but not for the stored procedure itself. Both with and without the
sp_something procedure existing in master (and when it exists, both with and without it being marked as a system object), the calls to
sp_something in the user database never “accidentally” call the procedure in master, and never generate any
CacheMiss events for the procedure.
This was on SQL Server 2012. I repeated the same tests above on SQL Server 2008 R2, and found slightly different results:
So on SQL Server 2008 R2 we see an additional
CacheMiss event that does not occur in SQL Server 2012. This occurs in all scenarios (no equivalent object master, an object in master marked as a system object, and an object in master not marked as a system object). Immediately I was curious whether this additional event would have any noticeable impact on performance.
Performance Issue: Fact or Fiction?
I made an additional procedure without the
sp_ prefix to compare raw performance,
USE sp_prefix; GO CREATE PROCEDURE dbo.proc_something AS BEGIN SELECT 'sp_prefix', DB_NAME(); END GO
So the only difference between
proc_something. I then created wrapper procedures to execute them 1000 times each, using
EXEC dbo.<procname> and
EXEC <procname> syntax, with equivalent stored procedures living in master and marked as a system object, living in master but not marked as a system object, and not living in master at all.
USE sp_prefix; GO CREATE PROCEDURE dbo.wrap_sp_3part AS BEGIN DECLARE @i INT = 1; WHILE @i <= 1000 BEGIN EXEC sp_prefix.dbo.sp_something; SET @i += 1; END END GO CREATE PROCEDURE dbo.wrap_sp_2part AS BEGIN DECLARE @i INT = 1; WHILE @i <= 1000 BEGIN EXEC dbo.sp_something; SET @i += 1; END END GO CREATE PROCEDURE dbo.wrap_sp_1part AS BEGIN DECLARE @i INT = 1; WHILE @i <= 1000 BEGIN EXEC sp_something; SET @i += 1; END END GO -- repeat for proc_something
Measuring runtime duration of each wrapper procedure with SQL Sentry Plan Explorer, the results show that using the
sp_ prefix has a significant impact on average duration in almost all cases (and certainly on average):
We also see that the performance of SQL Server 2012 trends much better than the performance on SQL Sevrer 2008 R2 – no other variables are different. Both instances are on the same host, and neither is under memory or other pressure of any kind. This could be a combination of the additional
CacheMiss event and those transparent improvements you get from enhancements made to the database engine between versions.
Another side effect : Ambiguity
If you create a stored procedure that references an object you created, say
dbo.sp_helptext, and you didn’t realize (or didn’t care) that this name collides with a system procedure name, then there is potential ambiguity when someone is reviewing your stored procedure. They will most likely assume you meant the system procedure, not a different procedure you created that happens to share its name.
Another interesting thing happens when you create a stored procedure that references a stored procedure prefixed with
sp_ that just happens to also exist in master. Let’s pick an existing procedure that you might not be immediately familiar with (and therefore might be a more likely representative of the scenario I’m describing):
CREATE PROCEDURE dbo.test1 AS BEGIN EXEC dbo.sp_resyncuniquetable; END GO
In Management Studio, IntelliSense doesn’t underline the stored procedure name as invalid, because there is a valid procedure with that name in master. So without seeing a squiggly line underneath, you might assume the procedure is already there (and assuming the procedure in master can be executed without error, this might pass QA/testing as well). If you choose a different name for your resync procedure, let’s say
proc_resyncuniquetable, there is absolutely no chance for this ambiguity (unless someone manually created that procedure in master, which I guess could happen). If the procedure doesn’t exist yet, the caller will still be created successfully (due to deferred name resolution), but you will receive this warning:
The module 'test1' depends on the missing object 'dbo.proc_resyncuniquetable'. The module will still be created; however, it cannot run successfully until the object exists.
One more source of ambiguity can occur in this scenario. The following sequence of events is entirely plausible:
- You create the initial version of a procedure, say,
- The deployer accidentally creates a version in master (and maybe notices, or maybe doesn’t, but in either case doesn’t clean up).
- The deployer (or someone else) creates the procedure, this time in the right database.
- Over time, you make multiple modifications to
- You replace
sp_superfoo, and delete
sp_foofrom the user database.
- When updating the application(s) to reference the new stored procedure, you might miss a replacement or two for various reasons.
So in this scenario, the application is still calling
sp_foo, and it’s not failing – even though you’ve deleted the local copy – since it finds what it thinks is an equivalent in master. Not only is this stored procedure in master not equivalent to
sp_superfoo, it’s not even equivalent to the latest version of
“Procedure not found” is a much easier problem to troubleshoot than “Procedure doesn’t exist – but code calling it works, and doesn’t quite return the expected results.”
I still think that, even though the behavior has changed slightly in SQL Server 2012, you shouldn’t be using the
sp_ prefix at any time, unless your intention is to create a stored procedure in master *and* mark it as a system object. Otherwise you are exposed to these performance issues as well as potential ambiguity on multiple fronts.
And personally, I don’t think stored procedures need to have any prefix at all – but I have less tangible evidence to convince you of that, other than asking you what other type of object could it possible be? You can’t execute a view, or a function, or a table…
As I suggest often, I don’t really care what your naming convention is, as long as you’re consistent. But I think you should avoid potentially harmful prefixes like