What is the search order for Procedures prefixed sp_?

Question

What is the search order for Procedures prefixed sp_?

Viewing 12 posts - 16 through 26 (of 26 total)

You must be logged in to reply to this topic. Login to reply

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 1

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
@kbriankelley

Paul Thornett Right there with Babe Points: 790 More actions · Answer 2

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.

Antares686 SSC Guru Points: 125444 More actions · Answer 3

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

gundapaneni Mr or Mrs. 500 Points: 593 More actions · Answer 4

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

Antares686 SSC Guru Points: 125444 More actions · Answer 5

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?

wodom Hall of Fame Points: 3813 More actions · Answer 6

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 Right there with Babe Points: 790 More actions · Answer 7

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 SSC Guru Points: 114642 More actions · Answer 8

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 Right there with Babe Points: 790 More actions · Answer 9

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 SSC Guru Points: 125444 More actions · Answer 10

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 SSC Guru Points: 114642 More actions · Answer 11

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 SSChampion Points: 13131 More actions · Answer 12

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