The Performance Impact to Prefix Stored Procedures with sp_

Last week I ran across a blog post by Axel Achten (B|T) that outlined a few reasons why you should not use SELECT * in queries.   In the post, Axel used the SQLQueryStress tool by Adam Machanic (B|T) to stress-test a simple query using SELECT * and SELECT col1, col2,...  This gave me an idea to use the same SQLQueryStress tool to benchmark a stored procedure that's prefixed with sp_.

All DBAs know, or should know, you should not prefix stored procedures with sp_.  Even Microsoft mentions the sp_ prefix is reserved for system stored procedures in Books Online.

I'm not going to discuss the do's and don'ts of naming conventions.  What I want to know is there still a performance impact of using the sp_ prefix. 

For our test, we'll use the AdventureWorks2012 database.  First we need to create two new stored procedures that selects from the Person.Person table.

USE AdventureWorks2012;

GO

CREATE PROCEDURE dbo.sp_SelectPerson AS SELECT * FROM Person.Person;

GO

CREATE PROCEDURE dbo.SelectPerson AS SELECT * FROM Person.Person;

GO

Next, we'll clear the procedure cache, and then execute each procedure once to compile it and to ensure all the data pages are in the buffer.

DBCC FREEPROCCACHE;

GO

EXEC dbo.sp_SelectPerson;

GO

EXEC dbo.SelectPerson;

GO

Next, we'll run execute each stored proc 100 times using SQLQueryStress and compare the results.

Total time to execute sp_SelectPerson was 3 minutes 43 seconds, and only 3 minutes 35 seconds to execute SelectPerson.  Given this test run was only over 100 iterations, 8 seconds is huge amount of savings.

We can even query sys.dm_exec_procedure_stats to get the average worker time in seconds and average elapsed time in seconds for each procedure.

SELECT

     o.name AS 'object_name'

    ,p.execution_count

    ,p.total_worker_time AS 'total_worker_time(μs)'

    ,(p.total_worker_time/p.execution_count)*0.000001 AS 'avg_worker_time(s)'

    ,p.total_elapsed_time AS 'total_elapsed_time(μs)'

    ,(p.total_elapsed_time/p.execution_count)*0.000001 AS 'avg_elapsed_time(s)'

FROM sys.dm_exec_procedure_stats p

JOIN sys.objects o ON p.object_id = o.object_id;

GO

As you can see, the average time per execution is very minimal, but it does add up over time.  This could easy scale into a much larger difference if all stored procedures begin with sp_.