RiCo技術農場

I refactored EF code instead of store procedure in our project in a few days ago.

I had completed this PR then push my last code to my branch on Stash and assigned reviewers.

My colleague(he is a native English speaker) added comment as “why usp_ and not sp_ like all other our store procedure”.

So I think I have to explain why.

Most people think sp_ stands for “store procedure”.

But in fact it means “special” store procedure in the master database.

If you use sp_MS_marksystemobject to marked it as a system object then the store procedure in master will execute in the context of the calling database.

In addition let’s look at a my simple performance test

create proc dbo.sp_dontdoit
as
begin
select AddressID,AddressLine1 from dbo.Address where AddressID=4
end

create proc dbo.usp_dontdoit
as
begin
select AddressID,AddressLine1 from dbo.Address where AddressID=4
end

I just executed 1000 times via SQLQuerystress for sp_ and usp_ and you saw result as below

Exec dbo.sp_dontdoit

Exec dbo.usp_dontdoit

Althought this adds a little overhead in performance but you have to know this problem.

 

Maybe you will say I just a luck or tools factor for prove different query performance.

Alright, I will track sp_ and usp_ with extended event on SQL2014 and you will find the same result.

CREATE EVENT SESSION [Compiles] ON SERVER
ADD EVENT sqlserver.query_post_compilation_showplan(
    ACTION(sqlserver.client_app_name,sqlserver.sql_text)
    WHERE ([sqlserver].[is_system]=(0))),
ADD EVENT sqlserver.query_pre_execution_showplan(
    ACTION(sqlserver.client_app_name,sqlserver.sql_text)
    WHERE ([sqlserver].[is_system]=(0))),
ADD EVENT sqlserver.sp_cache_hit(
    ACTION(sqlserver.client_app_name,sqlserver.sql_text)
    WHERE ([sqlserver].[is_system]=(0))),
ADD EVENT sqlserver.sp_cache_insert(
    ACTION(sqlserver.client_app_name,sqlserver.sql_text)
    WHERE ([sqlserver].[is_system]=(0))),
ADD EVENT sqlserver.sp_cache_miss(
    ACTION(sqlserver.client_app_name,sqlserver.sql_text)
    WHERE ([sqlserver].[is_system]=(0))),
ADD EVENT sqlserver.sp_cache_remove(
    ACTION(sqlserver.client_app_name,sqlserver.sql_text)
    WHERE ([sqlserver].[is_system]=(0))),
ADD EVENT sqlserver.sp_statement_completed(
    ACTION(sqlserver.client_app_name,sqlserver.sql_text)
    WHERE ([sqlserver].[is_system]=(0))),
ADD EVENT sqlserver.sp_statement_starting(
    ACTION(sqlserver.client_app_name,sqlserver.sql_text)
    WHERE ([sqlserver].[is_system]=(0))),
ADD EVENT sqlserver.sql_batch_completed(
    ACTION(sqlserver.client_app_name,sqlserver.sql_text)
    WHERE ([sqlserver].[is_system]=(0))),
ADD EVENT sqlserver.sql_batch_starting(
    ACTION(sqlserver.client_app_name,sqlserver.sql_text)
    WHERE ([sqlserver].[is_system]=(0))),
ADD EVENT sqlserver.sql_statement_completed(
    ACTION(sqlserver.client_app_name,sqlserver.sql_text)
    WHERE ([sqlserver].[is_system]=(0))),
ADD EVENT sqlserver.sql_statement_recompile(
    ACTION(sqlserver.client_app_name,sqlserver.sql_text)
    WHERE ([sqlserver].[is_system]=(0))),
ADD EVENT sqlserver.sql_statement_starting(
    ACTION(sqlserver.client_app_name,sqlserver.sql_text)
    WHERE ([sqlserver].[is_system]=(0))),
ADD EVENT sqlserver.uncached_sql_batch_statistics(
    ACTION(sqlserver.client_app_name,sqlserver.sql_text)
    WHERE ([sqlserver].[is_system]=(0)))
WITH (MAX_MEMORY=4096 KB,
      EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
      MAX_DISPATCH_LATENCY=30 SECONDS,
      MAX_EVENT_SIZE=0 KB,
      MEMORY_PARTITION_MODE=NONE,
      TRACK_CAUSALITY=ON,
      STARTUP_STATE=OFF)
GO

 

Exec dbo.usp_dontdoit twice

exec dbo.sp_dontdoit twice

This is why I should avoid sp_ as a prefix for store procedures.

 

Note:query each sp elapsed time via below tsql

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

 

Reference:

CREATE PROCEDURE (Transact-SQL)

SR0016: Avoid using sp_ as a prefix for stored procedures

Impact of sp_ prefix on stored procedure performance

Rename All References to a Database Object

Is the sp_ prefix still a no-no?

[SQL SERVER][Memo]撰寫Stored Procedure小細節

The Performance Impact to Prefix Stored Procedures with sp_

Optimizing SQL Server CPU Performance

Tracking Compiles with Extended Events