Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Everyday SQL

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.

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

Comments

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

Loading comments...