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