Ins and Outs of  sp_ user stored procedures


Ø  Here’s an interesting issue for which I sought a solution back in early 2004, user stored procedures starting sp_xxxxx.

Ø  We all know we shouldn’t do this but do we actually know why?

Ø  Well if you track the KB article you’ll see it can cause blocking in the procedure cache – what actually transpires is that the plan blocking serialises the execution of the stored procedure.

Ø  You need a quite heavy concurrent environment or a general performance slow down to see it happen, but I was fortunate or otherwise to be able to successfully witness the blocking in a critical production system – this is how to produce a work around.

Ø  I did submit this for publication but I guess the work around wasn’t best practice !



A large and complex distributed n’tier B2B production system which contains a large number of user procedures named sp_

The process of working through the many millions of lines of code with asp, com, win and web services to rename the stored procedures and qualify objects considered to involve too much risk.


The issue:

Stored procedure blocking





Fig 1. This shows an actual screenshot from the system but with identities changed.


Script to produce the above screenshot:-


use master


select right(convert(varchar,last_batch),7) as "time",left(nt_username,20) as NTuser,spid,open_tran as "tran",kpid,blocked,waittype,waittime

,lastwaittype,waitresource,db_name(dbid) as DB,cpu,physical_io,memusage,status

,hostname,program_name,cmd,loginame from dbo.sysprocesses with (nolock) where dbid>4 order by blocked desc,waittime desc,[tran] desc




 ( I run this as a script rather than as a procedure as it gives me more flexibility to filter as required )




Extra screen shot added to original article













      Diagram 1. Existing process







Diagram 2.  Proposed work around.



ü  Whilst researching,  a number of interesting features emerged including the case sensitivity of stored procedure calls in a case insensitive installation.  ( See appendix 1 for server configuration )


The Test:

User stored procedure placed in user database which has a simple select

( Note that these were created with unqualified names to match the existing application ! )


create procedure sp_DoThisThen


set nocount on

select uid,sid,roles,createdate from dbo.sysusers where status=14



create procedure usp_DoThisThen


set nocount on

select uid,sid,roles,createdate from dbo.sysusers where status=14




Clear the procedure cache at the start of each run.


dbcc freeproccache


Start a profiler trace  ( Appendix 2 for trace details )


From QA in the Sales Database


exec sp_DoThisThen


( Profiler results showing SP:CacheMiss events )


List the contents of the procedure cache, run from the Sales database


 select bucketid,cacheobjtype,objtype,db_name(dbid) as 'Database',object_name(objid) as 'Object' ,usecounts,sql

from master.dbo.syscacheobjects with (nolock) where sql not like '%trace%'

order by sql



 Plans in the procedure cache for sp_DoThisThen


Note that the initial execution of the procedure produces one extra SP:CacheMiss event, successive procedure executions generate only one event and re-use the procedure plans as expected.


Showing re-use of plan


Profiler trace


If the stored procedure is called with different case then an extra SP:CacheMiss is generated.


exec sp_doThisThen     ( incorrect case )


 Profiler results



 Profiler results



 Query results from procedure cache


ü  Contrary to belief,  prefixing the procedure calls with dbo.  does  not make any difference to the SP:CacheMiss  events when calling user procedures starting  sp_


 Profiler results showing single miss for correctly named procedure call and two missed events for the changed case call.



CREATE procedure dbo.sp_DoThisThen2


set nocount on

select uid,sid,roles,createdate from dbo.sysusers where status=14




ü  The results of these tests were the same as the preceding results.



Use Master


CREATE procedure sp_DoThisThenMaster


set nocount on

exec Sales.dbo.sp_DoThisThen






 Profiler results of calls from Sales database



ü  One further test remained, that was to make the sp_  procedure within the Master database a system stored procedure.

ü  This is achieved using the  system procedure  sp_MS_marksystemobject.


exec dbo.sp_MS_marksystemobject 'sp_DoThisThenMaster'




 Profiler results of calls from Sales database 




© Colin leversuch-Roberts


Appendix 1           SQL Server Configurations


Sql Server 2000 Enterprise SP4 “out of the box”  Latin1_General_AS_CI   quad zeon 4

Sql Server 2000 Enterprise SP4 “out of the box”  SQL_Latin1_General_CP1_AS_CI  dual P3


Appendix 2           Profiler configuration


Events :-                                all stored procedure events

Data Columns:-   EventClass, DatabaseID, TextData, Reads, SPID, StartTime


Events :-                                all stored procedure events

Data Columns:-   EventClass, DatabaseID, ObjectID, TextData, Reads, SPID, StartTime


Updated November 2007 – Starting notes added and scripts placed in table boxes, extra example added.