Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


What is the search order for Procedures prefixed sp_?


What is the search order for Procedures prefixed sp_?

Author
Message
wodom
wodom
SSC-Enthusiastic
SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)

Group: General Forum Members
Points: 146 Visits: 255
Well, Paul, I'm rather tickled that someone else besides has also hit upon the idea of using sp__ to identify locally-written system stored procedures.

I think I understand the performance issue people are talking about, but I don't think it matters in the restricted case we are talking about. According to bkelley (emphasis mine):

quote:

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..... , etc.



But you and I are talking about sp__ procedures that ARE in the master database (and not in the local DB), so according to this quote, the performance hit doesn't apply.



Paul Thornett
Paul Thornett
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 96
Congratulations to wodom! I'd pretty well given up on this discussion since respondents either didn't seem to be reading my replies properly, or were failing to understand them.

Indeed, since the sp__ procedures I was discussing are all in the Master database, and _nowhere_ else, I too cannot understand some of the responses I got.



K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (6.8K reputation)

Group: Moderators
Points: 6820 Visits: 1917
If the stored procedure prefixed sp_ (whether sp_RunMe or sp__RunMe) is in the master database, no, there's not a performance hit. The reason there's one in the first place is SQL Server makes the assumption that any such stored procedure is in the master database. If you've placed it there, great, no issue. The [COMPILE] lock only shows up when the stored procedure isn't in master.

So in short, I agree with you Paul, there isn't an issue with your current standard. My comments about the performance hit was directed at the use of sp__x in NotMaster you cite as an example. I think we're both in agreement there that there is a performance hit.

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
@‌kbriankelley
Paul Thornett
Paul Thornett
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 96
OK, but I'm still a little surprised about this outcome.

Let's recap, using a different model.

Suppose the following:
- I have different versions of a stored procedure called sp_x in BOTH Master and NotMaster databases
- I am currently in the NotMaster database
- I have not run sp_ms_marksystemobject

If I now enter 'Exec sp_x' while in NotMaster, I am ALWAYS going to run the version in NotMaster.
So why does Sql Server ever bother to check the Master database for this sp, since it's never going to override the local shadow? It sounds a bit like using IIF in VBA, where both the False and True execution statements are evaluated.

To me a more logical method would be for Sql Server to check the current database for the existence of sp_x. If found, compile and run the thing, and that's it. Only if not found does Sql Server then need to look in Master.



Antares686
Antares686
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: Moderators
Points: 8426 Visits: 780
Sorry, I misread your earlier post too, was not thinking in the lines of Master as where the sp_ item is stored.

As for the later, my guess is it doesn't actually check master but instead it checks a list in memory of the System Stored Procedure. This list must be built at the time SQL Server is started sine as I noted you can change a procedure to a system one by using the sp_MS_marksystemobject procedure, but once done the outcome is still the same as when it was a user type until you restart the server. Every System Procedure in master is considered to have precidence over any other with the same name, this way you common admin tasks are packaged and do not need special referencing since most you want to execute within the same database executed from. If they allowed you to have an SP named say, sp_changeobjectowner, then if you wrote your own you would not be able to use the functionality of the system one and thus an admin task is now broken for that database until you remove yours.



K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (6.8K reputation)

Group: Moderators
Points: 6820 Visits: 1917
Yup, and from a security perspective this makes a lot of sense. Consider the following scenario:

Instead of the current behavior, SQL Server searches in the local database first for any stored procedure, even ones that are normally system stored procedures.

I have the ability to create a stored procedure in a user database. I decide to name it sp_password. My stored procedure does the following:

(1) it emails me the username and password
(2) it then calls sp_password from the master database and performs the password update just fine.

See the problem?

Microsoft recently released a security bulletin that most of have said, "Tell me something I didn't know" which points out that if you let something drop an executible in C:\ (root), and it happens to be the same name as a system executible say like ping or any of the others, the one in root will be found and executed first if you try to run it by Start | Run. The reason, of course, is because while C:\ is not typically in the search path, for operations like Start | Run it can be. Oops. Here's the article:

http://www.microsoft.com/technet/treeview/default.asp?url=/technet/security/bulletin/MS02-064.asp

So the simple solution is not to use sp_ in user databases and the whole issue is avoided. As it is now, a security issue is mitigated with SQL Server's default behavior. This shouldn't change.

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
@‌kbriankelley
fhanlon
fhanlon
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2327 Visits: 2271
Personally I think using 2 underscores is difficult to see and therefore is very prone to error. __ is not obviously different from _ I can see how developers will consistantly misspell sp__ procs



Francis
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search