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 !

 

Scenario:

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

 

Reference  http://support.microsoft.com/default.aspx?scid=KB;en-us;q263889

 

 

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

 

Script to produce the above screenshot:-

 

use master

go

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

go

 

 

 ( 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

as

set nocount on

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

GO

 

create procedure usp_DoThisThen

as

set nocount on

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

GO

 

 

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

as

set nocount on

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

GO

 

 

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

 

 

Use Master

go

CREATE procedure sp_DoThisThenMaster

as

set nocount on

exec Sales.dbo.sp_DoThisThen

GO

 

 

 

 

 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 

 

Conclusion:

 

© Colin leversuch-Roberts  www.kelemconsulting.co.uk

 

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.