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:
There is also a design rule for code review available on this:
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: