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 )

• Stored procedure calls queue and when a procedure runs for longer than normal, in this case up to 17 seconds, system degradation occurs which in turn leads to application blocking.
• For a critical production system this is clearly unacceptable.
• You may think this is an unlikely situation, well here is a screen shot from another client site showing serialised blocking on a stored procedure – the actual details are hidden to protect the guilty and this was caused by a slightly different situation, but the end result was the same

Extra screen shot added to original article

• The task was to find a SQL Server solution to the problem, the initial thought was to place “wrapper”  sp_  procedures within master which would call renamed user procedures  ( without sp_ ) within the user databases.

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

• Repeated executions show a continued extra SP:CacheMiss event for each execution

 Profiler results

• However the existing  plan continues to be used.

 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.

• A series of tests were made whereby the stored procedure was created with dbo.  prefix.
• A series of tests were made whereby the calling user was not dbo

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.

• The next step was to evaluate the effect of placing a wrapper procedure within master database which would make a correct call to the ( renamed ) user procedure in the Sales database.
• The stored procedure usp_DoThisThen in Sales database was used for the test.
• The following procedure was placed in the master database

Use Master

go

CREATE procedure sp_DoThisThenMaster

as

set nocount on

exec Sales.dbo.sp_DoThisThen

GO

• As before the procedure cache was cleared and a series of calls to the procedure sp_DoThisThenMaster were made from the context of both the Sales and Master databases.
• A further test followed whereby the procedure calls were prefixed with dbo.

 Profiler results of calls from Sales database

• From the Sales database the results were the same as before, only if the procedure was called within the context of the master database were the SP:CacheMiss events avoided.
• As a resolution to the original problem the solution failed as the context of the sp_ procedure calls was the Sales database.
• The tests also showed that using the dbo. prefix to the procedure calls did not make any difference.

ü 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'

• The same series of tests were performed.
• As can be seen in the Profiler Trace there are no SP:CacheMiss events when the procedure was called from the Sales database. The prefixing with dbo. again had no effect.

 Profiler results of calls from Sales database 

Conclusion:

• For the described problem the solution of using a wrapper procedure will only work if it is created as a system stored procedure.
• Stored procedure calls are case sensitive ( even in a case insensitive environment ) and may produce blocking in a highly concurrent environment
• The solution is probably somewhat unconventional but not every environment is ideal and the task of the Production DBA is to provide solutions to problems no matter how obscure.

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