Database developers often name stored procedures with the SP_ prefix in SQL Server. The SP_ prefix helps other developers quickly identify an object as a stored procedure. Although there have been many debates over whether or not user created objects should be named with the sp_ prefix following the naming convention standards, we should not name our stored procedures in SQL Server with the SP_ prefix. This Tech-Recipes tutorial explains why we should avoid using the SP_ prefix in stored procedures in SQL Server.

Why should we avoid using the SP_ prefix in stored procedures?

1. If we create a stored procedure with the SP_ prefix, SQL Server considers it as a system stored procedure and does a search in the “master” database first to check whether that stored procedure exists there. Later in the user database, this adds a little overhead in performance.

2. Stored procedures with prefix the SP_ are considered as a system’s special stored procedures, which are stored in the master database.

3. Adding the SP_ prefix while creating a stored procedure in user databases might conflict with system stored procedures available in the master database.

4.Here is an additional reason to avoid using the sp_ prefix in stored procedures from Microsoft Books Online:

Avoid the use of the sp_ prefix when naming procedures. This prefix is used by SQL Server to designate system procedures. Using the prefix can cause application code to break if there is a system procedure with the same name.

Preference for Master Database Stored Procedure

The sp_help procedure is an built-in procedure which is stored in the master database. If you unexpectedly create a procedure in a user database with the same name, the procedure from the master database will still be executed, overriding your stored procedure. Below is an example.

Create a stored procedure with sp_help name in tempdb.

Use tempdb
GO

CREATE PROCEDURE sp_help
AS
BEGIN
    PRINT 'I need immediate help, I am in tempdb';
END

Let’s run the Stored Procedure from tempdb.

Use tempdb
GO

EXEC sp_help  -- Expected output is "I need immediate help, I am in tempdb"

Afterwards, we get unexpected output because sp_help is a system stored procedure which exists in the master database. Therefore, SQL Server has run the stored procedure from the master instead of running user created SP “sp_help” from tempdb because it has the first preference.

CacheMiss event in SQL Server Profiler

Let’s see using SQL Server Profiler how a CacheMiss event occurs when we try to run a stored procedure which exists in the master database and also in the tempdb database.

CacheMiss: In the following picture, you can see that a CacheMiss event has occurred while running the EXEC sp_help procedure from tempdb. This indicates that SQL Server tried to find the execution plan for sp_help SP in tempdb, but it did not find it in plan cache. Therefore, a CacheMiss event has occurred which could be a performance hit as well.

Summary: Using SP_ as a prefix for stored procedures in SQL Server has a performance impact considering SQL Server has to do the first lookup in the master database to check. Also, a CacheMiss event indicates the SQL server searches for the execution plan in the user database, but does not find it.