SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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;
CREATE PROCEDURE dbo.sp_SelectPerson AS SELECT * FROM Person.Person;
CREATE PROCEDURE dbo.SelectPerson AS SELECT * FROM Person.Person;

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.

EXEC dbo.sp_SelectPerson;
EXEC dbo.SelectPerson;

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.

     o.name AS 'object_name'
    ,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;

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

Everyday SQL

Patrick Keisler is a Premier Field Engineer for Microsoft with over 15 years of SQL Server experience working in various fields such as financial, healthcare, and government. He currently holds an MCSE Data Platform certification, MCITP certifications in SQL Server 2008 for administration and development, and CompTIA Security+. You can follow him on Twitter or listen to him speak at various SQL Saturdays and user group meetings.


Leave a comment on the original post [www.patrickkeisler.com, opens in a new window]

Loading comments...