Performance Tip: Avoid Using the SP_ Prefix in Stored Procedures

feature-sql1-618x350

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.

SP_Prefix_In_Stored_Procedures_SQL_Server_1

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.

SP_Prefix_In_Stored_Procedures_SQL_Server_2

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.

 

About Vishwanath Dalvi

Vishwanath Dalvi is a gifted engineer and tech enthusiast. He enjoys music, magic, movies, and gaming. When not hacking around or supporting the open source community, he is trying to overcome his phobia of dogs.
View more articles by Vishwanath Dalvi

The Conversation

Follow the reactions below and share your own thoughts.