Patrick Keisler is a MCTS and MCP. For over 12 years, he has been been a database administrator for a major investment bank, Wells Fargo Securities. During that time, he has gained considerable knowledge in Microsoft SQL Server by supporting hundreds of applications ranging from high-volume trading applications to massive data warehouses.
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.
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.
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_.