SQLServerCentral / Discuss Content Posted by James Travis / Article Discussions / Article Discussions by Author / What is the search order for Procedures prefixed sp_? / Latest Posts

RE: What is the search order for Procedures prefixed sp_?

fhanlon — Mon, 22 Mar 2004 08:30:00 GMT

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

RE: What is the search order for Procedures prefixed sp_?

K. Brian Kelley — Tue, 26 Nov 2002 07:52:00 GMT

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.aspSo 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 Kelleyhttp://www.truthsolutions.com/Author: Start to Finish Guide to SQL Server Performance Monitoring http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

RE: What is the search order for Procedures prefixed sp_?

Antares686 — Tue, 26 Nov 2002 03:56:00 GMT

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.

RE: What is the search order for Procedures prefixed sp_?

Paul Thornett — Mon, 25 Nov 2002 18:46:00 GMT

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

RE: What is the search order for Procedures prefixed sp_?

K. Brian Kelley — Mon, 25 Nov 2002 18:34:00 GMT

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 Kelleyhttp://www.truthsolutions.com/Author: Start to Finish Guide to SQL Server Performance Monitoring http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

RE: What is the search order for Procedures prefixed sp_?

Paul Thornett — Mon, 25 Nov 2002 16:02:00 GMT

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.

RE: What is the search order for Procedures prefixed sp_?

wodom — Mon, 25 Nov 2002 10:03:00 GMT

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.

RE: What is the search order for Procedures prefixed sp_?

Antares686 — Tue, 19 Nov 2002 13:41:00 GMT

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?

RE: What is the search order for Procedures prefixed sp_?

gundapaneni — Tue, 19 Nov 2002 08:14:00 GMT

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

RE: What is the search order for Procedures prefixed sp_?

Antares686 — Sat, 16 Nov 2002 09:45:00 GMT

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

RE: What is the search order for Procedures prefixed sp_?

Paul Thornett — Fri, 15 Nov 2002 20:41:00 GMT

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 databaseAnd using sp_ms_marksystemobject doesn't change anything, given rule 2 above.

RE: What is the search order for Procedures prefixed sp_?

K. Brian Kelley — Fri, 15 Nov 2002 11:03:00 GMT

It does not apply to sp without the underscore. For instance, spTestMe would be fine. sp_TestMe causes the Cache Miss, etc.K. Brian Kelleyhttp://www.truthsolutions.com/Author: Start to Finish Guide to SQL Server Performance Monitoring http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

RE: What is the search order for Procedures prefixed sp_?

Antares686 — Fri, 15 Nov 2002 08:39:00 GMT

I do believe that is correct. Might check with profiler just to be sure. I may when I get a chance.

RE: What is the search order for Procedures prefixed sp_?

Ivy — Fri, 15 Nov 2002 08:36:00 GMT

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?

RE: What is the search order for Procedures prefixed sp_?

K. Brian Kelley — Fri, 15 Nov 2002 07:33:00 GMT

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 Kelleyhttp://www.truthsolutions.com/Author: Start to Finish Guide to SQL Server Performance Monitoring http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

RE: What is the search order for Procedures prefixed sp_?

Paul Thornett — Fri, 15 Nov 2002 06:34:00 GMT

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?

RE: What is the search order for Procedures prefixed sp_?

Antares686 — Fri, 15 Nov 2002 05:00:00 GMT

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.

RE: What is the search order for Procedures prefixed sp_?

Paul Thornett — Thu, 14 Nov 2002 16:35:00 GMT

OK, having run sp_ms_marksystemobject, I get the same results as James.But the article was entitled 'What is the search order for Procedures prefixed sp_?'In that context, running sp_ms_marksystemobject is not relevant. And I would therefore argue that there is no reason why user-written stored procedures should not be named with the 'sp_' prefix. I would also suggest that having 2 stored procedures with the same name, where one of them resides within master, is poor management.My own convention is to prefix any sps I write which I store into master with 'sp__' (that's 2 underscores instead of 1). So I get the benefit of a universally available sp, but at the same time can easily see which sps within master are 'mine'.In other dbs, I use sprName for sps I expect to invoke directly (usually from code within my app), and spr_ for sps that are not invoked directly by me, but instead are invoked from within the (higher level) sprName sps.And fortunately, I operate in an environment where I have complete and sole control over all such standards and conventions <g>!

RE: What is the search order for Procedures prefixed sp_?

Antares686 — Thu, 14 Nov 2002 16:12:00 GMT

quote:
It would be great if you could categorise stored procedures into folders. So that in EM you have Stored Procedures Then a System folder and a User folder and you can then add more folders under the User folder.Who nows what we get with Yukon, I know we are getting auto complete like in visual studio, gone will be the days of trying to remember column names.Simon SabinCo-author of SQL Server 2000 XML Distilledhttp://www.amazon.co.uk/exec/obidos/ASIN/1904347088

Yes, that would be a great feature. This one may be a future possiblity thou if you look at what they did with QA in SQL 2000 and the schema view window (or whatever it is called).

RE: What is the search order for Procedures prefixed sp_?

Antares686 — Thu, 14 Nov 2002 16:09:00 GMT

I believe Brain is right. If you look back I mark the sp as a system object by running sp_MS_marksystemobjectagainst it.It is not a documented procedure but what it does is change the bitmask of the status of the object to match with system type. Problem is once done there is no real safe way to undo (can be done don't get me wrong) and as long as marked as a system type you cannot drop thru EM (sorry didn't try thru QA and wasn't really worried since is a test server I got to rebuild in the next few months anyway). Once you do that and restart the server, your results should match up.

RE: What is the search order for Procedures prefixed sp_?

K. Brian Kelley — Thu, 14 Nov 2002 14:05:00 GMT

Did you mark the stored procedure in the master database as a system object? I don't see that you did based on your explanation. That would explain the discrepancy based on both the original forum discussion and the article.K. Brian Kelleyhttp://www.truthsolutions.com/Author: Start to Finish Guide to SQL Server Performance Monitoring http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

RE: What is the search order for Procedures prefixed sp_?

Paul Thornett — Thu, 14 Nov 2002 07:47:00 GMT

Well, my experience is absolutely the opposite of James Travis - and is exactly what I would have expected!Here is what I did, using Sql Server 2000 Sp2 under Windows 2000 professional Sp3:- Opened Query Analyzer- Typed in the following: use master go create procedure sp_mysp as select 'I am the master' go use miscellaneous go exec miscellaneous..sp_mysp go create procedure sp_mysp as select 'I am NOT the master' go exec miscellaneous..sp__mysp and got exactly the same as James I am the master I am NOT the master- I closed Sql Server 2000 (SP2) down, then restarted it- Then use miscellaneous go exec miscellaneous..sp_mysp STILL gives I am NOT the master- And sp__mysp ALSO gives I am NOT the master- And use Maintenance go exec sp_mysp gives I am the masterSo how do we explain these 2 sets of opposing results?

RE: What is the search order for Procedures prefixed sp_?

Jonr — Thu, 14 Nov 2002 07:24:00 GMT

Good article James, well investigated.Looks like Microsoft got it right on the security front with this one too, as the s/p needs to be owned by a system user in order to exec sp_MS_marksystemobject.Jon Reade

RE: What is the search order for Procedures prefixed sp_?

Jonr — Thu, 14 Nov 2002 07:23:00 GMT

RE: What is the search order for Procedures prefixed sp_?

Simon Sabin — Thu, 14 Nov 2002 03:57:00 GMT

It would be great if you could categorise stored procedures into folders. So that in EM you have Stored Procedures Then a System folder and a User folder and you can then add more folders under the User folder.Who nows what we get with Yukon, I know we are getting auto complete like in visual studio, gone will be the days of trying to remember column names.Simon SabinCo-author of SQL Server 2000 XML Distilledhttp://www.amazon.co.uk/exec/obidos/ASIN/1904347088

RE: What is the search order for Procedures prefixed sp_?

David.Poole — Thu, 14 Nov 2002 02:56:00 GMT

InterestingI've always insisted that stored procedures were prefixed with usp (user stored procedure) so that I can spot the ones for which I should expect documentation.As there are an ever growing number of internal stored procedures it is vital that non-internal stored procedures are immediately identifiable, if only to show that you don't have to trawl for BOL to find out what they were supposed to do.

What is the search order for Procedures prefixed sp_?

Antares686 — Tue, 12 Nov 2002 00:00:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/jtravis/sp_performance.asp