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_.

1. Start SQL Server Profiler and connect to your server. Keep all the initial defaults, with one exception: Add the event class SP:CacheMiss. To do so, expand the Stored Procedures event class tree under the Available Event Classes group. On the Events tab, select the SP:CacheMiss event class.


2. Start the trace and press Crtl+Shift+Delete to clear all events in the Profiler window.


3. Run dbo.sp_Select1 from the open Query Analyzer connection in tempdb that you used to create the stored procedures.


4. Open the Profiler window and look at the events. If you created the SQL Trace as I defined above, you should see three new events in SQL Server 2000: two SP:CacheMiss events and one SQL:BatchCompleted event. In SQL Server 7.0, you'll see one SP:CacheMiss event and one SQL:BatchCompleted event. SP:CacheMiss means that SQL Server didn't find the procedure in the procedure cache. SQL:BatchCompleted reveals that the T-SQL batch has finished.


5. Press Crtl+Shift+Delete to clear all events in the Profiler window.


6. Run dbo.Select1 from the Query Analyzer connection you used in Step 3. If you created the SQL Trace as I defined earlier, you'll see two new events in SQL Server 2000: one SP:CacheMiss event and one SQL:BatchCompleted event. In SQL Server 7.0, you'll see one SQL:BatchCompleted event—however, you won't see the SP:CacheMiss event that Step 3 generated.

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."