Stored Procedure or System Procedure?

Posted 24 March 2007 10:51 by JohnParker

If you could somehow link all the SQL Servers in the world together and then capture a Profiler trace on it, it would be extremely interesting to analyse the results.  I bet table scans and therefore missing indexes would be quite high on this list.  I wonder if recompilation of stored procedures called sp_ would be in the top ten culprits, eating up precious CPU cycles and causing wait time?  I've been using SQL Server since 1999 as a Developer/DBA and it's easy to be cynical about developers and the way they code queries.  I have lost count of the number of times I've seen developers create user stored procedures called sp_ not knowing that this forces recompilation ever time the query's run.  I started thinking about why they did this, I mean, it happens a lot.

1. Microsoft call their own system stored procedures sp_, so that they recompile when they're executed.  Developers see this and think it's a standard and trying to be good developers, replicate this Microsoft standard.

2. Developers like to adhere to naming conventions to make coding easier.  I expect they think that sp_ means stored procedure but I wonder if it actually stands for system procedure? Surely though they should be called SSP (System Stored Procedures?)

3. They just don't know, and no-one has ever told them.  It's eays to criticise other people's lack of knowledge, but I've seen this countless times.  How can they all be uninformed?  Maybe they don't have a DBA or SQL developer on site.

Thinking about this, I think Microsoft are slightly at fault here because of the reasons above.  People who develop in SQL Server may not have the time to pick up a decent programming book and start learning the nuances of the product, they have to get a solution from SQL within the whole project they're working on and also to a deadline.  Is it any surprise they don't pick up a DBA or SQL Server programming book?  Perhaps if they did then I would be out of a job.  Microsoft have done a very good job at creating a self-tuning RDBMS, however, I can't help but think the easiest solution to this problem is when someone creates a stored procedure called sp_, the informational message should be 'Stored Procedures called sp_ will be recompiled every time they are run and this may affect performance'.  Surely this would force even the most reluctant developer to investigate this?

I'm off to the SQL Server Connect website and logging this recommendation. If Microsoft added this in a future Service Pack, I wonder if that World-Wide Trace would get any smaller? 

Comments

# re: Stored Procedure or System Procedure?

26 March 2007 10:15 by Colin Leversuch-Roberts

I don't think you're right about sp_  procs always recompiling, I've certainly not noticed this as a general trait. Calls to sp_ procs first go to the master database and then onwards as quite well documented by microsoft and can cause sp cache miss events which may cause serialisation of procedure plans in highly concurrent environments which may lead to blocking in the procedure cache. see http://support.microsoft.com/kb/263889. It's worth pointing out that even qualified calls to sp_ procs have this problem. The system property of a stored procedure is seperate to the sp_ , usually system procs are sp_ or xp_ as they'd normally be in a system database. User system procs can be very useful.

# re: Stored Procedure or System Procedure?

05 April 2007 18:24 by JohnParker

Thanks for this Colin, you're absolutely right, sp_ stored procedures do not get recompiled.  I'm sure I've read this a couple of times in different books; I lack the imagination to make 'facts' up.  I'd rather be corrected though.  I did a quick test on this, comparing stored procedure execution times sp_ against usp_ and to be honest the sp_ was quicker, however this was on a dev database so I'll run it on a quiet system.  I'll do some further tests including qualified (i.e. dbo) names and if they prove satisfactory I'll continue with the Connect recommendation.  I received an email saying it was a good, simple idea and if it's worthwhile, I'll see it through.