Feb 192013

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

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>



This site uses Akismet to reduce spam. Learn how your comment data is processed.