September 1, 2009 at 4:47 pm
Hi All,
I have heard many times that prefixing stored procedure with sp_ causes a roundtrip to master database. This was mentioned in BOL 2000. But I can't found any information about it in BOL 2008 and BOL 2005. Then I tried to start profiler and find this roundtrip, but I'd always get the same result for procedures which starts with sp_ and for procedures which not. Does anyone have any suggestions? Is this roundtrip actual in SQL Server 2008? Or I can create procedures starts with sp_ without any performance issue?
September 1, 2009 at 5:08 pm
The SQL engine will attempt to find the proc in the Master db first. So this will slow finding the proc down a little. Better to use prc_ or some other convention and stick to it. If you need numbers, Linchi Shea did a blog post on the performance affects, which was minor, but in high transaction environment, every bit helps.
Chuck Lathrope
-SQLGuyChuck
September 3, 2009 at 2:44 am
bad practise to use sp_ prefix, microsoft recommends against it. Most people I know use usp_ but it is a personal thing.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 3, 2009 at 2:51 am
Silverfox (9/3/2009)
bad practise to use sp_ prefix, microsoft recommends against it. Most people I know use usp_ but it is a personal thing.
I think its more that a recommendation , its a rule.
sp_ procs are processed in a different order + there is a million to one chance that MS will create a stored procedure of the same name as yours , in which case it may fire yours and all hell will break loose.
September 3, 2009 at 3:50 am
Thank you for your answers, but this article - http://www.sqlmag.com/articles/index.cfm?articleid=23011& has been written in 2001. Have you try this experiment in SQL 2008? I've try to use different prefixes, and I've always get the same result. And there is anything about performance issue when you using sp_ prefix in BOL 2008, but there are in BOL 2000. What do you think about it? Maybe SQL 2008 has something changes in this mechanism?
September 3, 2009 at 3:56 am
subtitle differences but the same advice still holds true
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply