Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

What is the search order for Procedures prefixed sp_? Expand / Collapse
Author
Message
Posted Friday, November 15, 2002 5:00 AM
SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: Moderators
Last Login: Monday, October 20, 2014 1:24 PM
Points: 8,370, Visits: 743
Paul I do agree with you on the point of don't use sp_. That was the orignal statement.

The thing here was to try and understand under the hood why specifically does it have to be a System Stored Procedure that will always win out and to understand the search order SQL uses to access Procedure objects that start with sp_. The reason for bringing sp_ms_marksystemobject is to show that a system typed Stored Procedure has a specific behavior as opposed to a user typed. A user typed will not override the DB local copy of the procedure but a system typed will always win out in the search order and a local DB sp with the same name will neever run do to this.

However, using sp__ will still produce a major performance hit because it starts sp_ which I did not delve into in this article. But if you look at the thread that I researched this article for you will find the following posted by K. Brian Kelly.

quote:
Here's why...

SQL Server will initially look for the execution plan in the master database. This will result in a cache miss event. SQL Server will then put an exclusive [COMPILE] lock on said stored procedure. Exclusive means exclusive... only one process can use it as a time. SQL Server will then do a second, more comprehensive search to match up the stored procedure with an execution plan. It it finds it (and it should after you've run it once), it will then register an ExecContextHit and attempt to reuse the cached execution plan. Only then will it release that [COMPILE] lock.

This will happen every time the stored procedure is executed. Meaning if you've got several processes that could be calling the stored procedure, they will queue up. Remember, that even though one process has the stored procedure running and has released the [COMPILE] lock, the next process can't do anything until the first process is done. This is because the [COMPILE] lock is exclusive. As a result, you'll serialize the stored procedure where only one process can use it as a time.


So there are performance issues around using any procedure and starting it with sp_. This was purely a search order article.

I do appreciate the input thou.




Post #46502
Posted Friday, November 15, 2002 6:34 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, September 7, 2011 8:38 AM
Points: 28, Visits: 96
I'm still in the dark on this!

If I have an sp called sp__x in databases Master and NotMaster (and I didn't run sp_ms_marksystemobject), and I'm currently in NotMaster, then the sp__x that is executed is _always_ the local copy, i.e. the one in NotMaster. So where does the master database come into this? Surely if Sql server is always going to run the local copy of sp__x, then there are no search issues to consider. Or am I missing something here?




Post #46503
Posted Friday, November 15, 2002 7:33 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Yesterday @ 7:50 AM
Points: 6,624, Visits: 1,874
There is an issue to consider, and that issue is the performance hit, regardless of whether or not it grabs the right stored procedure or not.

Quite simply, if you have an sp_ named stored procedure that isn't in the master database, you will always get an initial SP:CacheMiss event, meaning on the initial pass through, SQL Server won't locate the cached execution plan.

SQL Server will then institute a [COMPILE] lock on the stored procedure. This is an exclusive lock, meaning only the current process will be able to touch the stored procedure (and means all other stored procedure execution requests for that stored procedure will have to wait and execute one at a time). SQL Server will then perform a more exhaustive search, and if you've run the stored procedure before it should find the execution plan on the second pass. Only then will it release the [COMPILE] lock and use the stored execution plan.

Keep in mind that though the current process has released the [COMPILE] lock, the next process in line is going to want to issue a [COMPILE] lock itself because of the initital cache miss. So it'll have to wait until the current process completes execution of the stored procedure before it can apply the [COMPILE] lock, etc.

If you want to see the screenshots of Profiler recording this, my article on stored procedure caching shows it. However, you can recreate the events for yourself in you've got Profiler from the SQL Server 2000 client tools. Simply add all the SP:Cache* events, fire off your stored procedure and watch the results. This also include SP:ExecContextHit, BTW. Their are some Microsoft KB articles that describe these behaviors. I don't have 'em off hand, but they are in the article as well. Brian Moran also wrote a column about this in SQL Server magazine, so if you're a subscriber, you can read it there as well.

K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1


K. Brian Kelley, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #46504
Posted Friday, November 15, 2002 8:36 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, June 3, 2009 9:10 AM
Points: 35, Visits: 3
I hate to post something at the very bottom of this that my be clear as day but...performance hits with only sp_ and not sp (without the _)..Correct?




Post #46505
Posted Friday, November 15, 2002 8:39 AM
SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: Moderators
Last Login: Monday, October 20, 2014 1:24 PM
Points: 8,370, Visits: 743
I do believe that is correct. Might check with profiler just to be sure. I may when I get a chance.




Post #46506
Posted Friday, November 15, 2002 11:03 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Yesterday @ 7:50 AM
Points: 6,624, Visits: 1,874
It does not apply to sp without the underscore. For instance, spTestMe would be fine. sp_TestMe causes the Cache Miss, etc.

K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1


K. Brian Kelley, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #46507
Posted Friday, November 15, 2002 8:41 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, September 7, 2011 8:38 AM
Points: 28, Visits: 96
So, if I stick to my current "standard" I am OK?

In other words:
1) I use sp__, not sp_ (so all such procedures are grouped together)
2) Any sp__ procedures are stored ONLY in master, not in any user database

And using sp_ms_marksystemobject doesn't change anything, given rule 2 above.





Post #46508
Posted Saturday, November 16, 2002 9:45 AM
SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: Moderators
Last Login: Monday, October 20, 2014 1:24 PM
Points: 8,370, Visits: 743
No keep in mind we are talking about the first three characters being sp_ doesn't matter what is after it. In you situation all procedures with sp__ still fall prey to the cache miss and other conditions of the this thread and topic. Your first three charcters are still s p _.




Post #46509
Posted Tuesday, November 19, 2002 8:14 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, July 27, 2007 12:35 AM
Points: 97, Visits: 1
I too accept with Paul Thornett, as he told I am also getting same result as he got. I have run this on Sql2000 with service pack 2 mechine. I need more help on this........any one can explain.

madhusudannaidugundapaneni



Madhu
Post #46510
Posted Tuesday, November 19, 2002 1:41 PM
SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: Moderators
Last Login: Monday, October 20, 2014 1:24 PM
Points: 8,370, Visits: 743
I would need to see how you tested and got the results. Can you post the code you used to get your results and we can see if anything is missing?




Post #46511
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse