A long (but not missed) friend revisited, prefixing stored procedures with SP_
Coming as a simple sample with PBM (creating a policy with a condition that procedure names shouldn't´t start with SP_) and getting an interesting question in one of my classes, I wanted to revisit the question about the yet in some places existing naming convention of prefixing the procedure with SP_. To keep a long story short, don't do this at home or your work. It will show you the basics of this problem and further questions which came up.
If you need additional information about this, you can have a look at the following articles:
Should I Use the sp_ Prefix for Procedure Names?
There is also a design rule for code review available on this:
https://msdn.microsoft.com/en-us/library/dd172115.aspx
The prefix SP_ indicates for SQL Server that this is a (S)ystem(P)rocedure_ not a stored procedure.
Therefore SQL Server will assume that this procedure lives in the master database, looks for a compiled plan, does not find one, raises a Cache miss event and recompiles the procedure. Well, for the most of you, you think “Producing a new plan, that shouldn't be a big deal”. Well yes – it is. When it comes to compiling plans of whole procedures SQL Server will need to place a schema lock (to make sure that the procedure wont change in between) and wont enable the other callers (who also need to make a compile due to the above mentioned reasons) to recompile the procedure as well as they will queue up in the schema lock chain. Not that you will lose all the functionality like statement level recompilation (a nice feature since SQL Server 2005), for heavy workloads and a often called procedure, this isn't scalable AT ALL.
OK, the easiest sample can be seen here:
You see that a Cache Miss is produced once the procedure is called form the batch.
The first question was “SQL Server will be smart enough to know that the current database is meant and not the master database if you place the schema owner in front of the procedure name, right ?”.
-Well – no. SQL Server still assumes that this is a procedure that lives in master database and will first search here.
The second question was “SQL Server will be smart enough to know that the current database is if you specify the procedure call with a three-part-name, explicitly calling the procedure in the database, right ?”.
-Well – no. SQL Server resists to think that this procedure that lives in that database and will have a look in the master database first.
The conclusion to this is, that there is NO reason to name your procedure with SP_ unless you want to procedure to be compiled upon every call, and hey, we can do better than this by using the compiling hints like “WITH RECOMPILE”. See more recompilation hints on this link here:
-Jens
Comments
Anonymous
April 27, 2009
PingBack from http://www.anith.com/?p=33032Anonymous
June 11, 2009
This is not the case for SQL 2005 or SQL 2008. When procedure metadata is looked from catalog, if it is in dbo schema and name begins with sp_, we check if there is system procedures with same name and cache this fact. Later, we simply look up current database first, using this fact to avoid looking up system procedures. There should not be cache misss simply due to sp_ prefix naming, as this article describes. End users are not encouraged to use the sp_ prefix, unless the name will not likely be used by system procedures shipped with SQL Server in the future. Best Regards, Andrew Zhu [MSFT]