Should I Use the sp_ Prefix for Procedure Names?

Some people prefix their stored procedure names with sp_. Some people say you should never use the sp_ prefix in procedure names. What's the real scoop?

Related: Recompilation and Coding Owner Prefixes on a Stored Procedure

Never might sound like a long time, but prefixing your procedure names with sp_ causes a performance penalty if the procedures exist in a database other than master. Don't do it.

The example that Listing 1 shows illustrates why you should never prefix procedures with sp_ if you intend to use them in a high-volume transaction-processing environment while maintaining the best possible performance. The code in Listing 1 creates two test procedures in tempdb. I named the first procedure Select1 and the second procedure sp_Select1. The procedures run an identical command, SELECT 1, which is the simplest SELECT statement imaginable.

Run each of the procedures once, as Listing 2 shows, to ensure that SQL Server has compiled the procedure plans for each procedure and has cached them in memory. Then, proceed through the following steps to see the performance implication of prefixing procedures with sp_.

Why did sp_Select1 generate an SP:CacheMiss but Select1 didn't? SQL Server gives name-resolution preference to the master database for procedures that have the sp_ prefix. SQL Server looks for a compiled plan for the procedure associated with the master database and doesn't find it because, in this case, the sp_Select1 procedure exists in tempdb. SQL Server assumes the procedure isn't in cache (and thus must be recompiled) and acquires an exclusive compile lock on the stored procedure for a short time. However, the short time that the lock exists is enough to cause performance problems.

Why does SQL Server need an exclusive compile lock? According to the Microsoft article, "INF: SQL Blocking Due to COMPILE Locks," "In SQL Server 7.0 and 2000, only one copy of a stored procedure plan is generally in cache at any given time. Enforcing this requires serialization of some parts of the compilation process, and this synchronization is accomplished in part through the use of compile locks." Exclusive locks, of course, block other exclusive locks, which can create a serialization point that causes blocking if many people are trying to run the procedure. This behavior is one of the reasons that you should not prefix a user stored procedure with sp_. For more information about preventing this kind of performance penalty, see the sidebar "Tip: Always Qualify Stored Procedure References."