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 Monday, November 25, 2002 10:03 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 15, 2014 11:48 AM
Points: 134, Visits: 189
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.




Post #46512
Posted Monday, November 25, 2002 4:02 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
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.




Post #46513
Posted Monday, November 25, 2002 6:34 PM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Monday, September 15, 2014 8:57 AM
Points: 6,634, Visits: 1,872
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, 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 #46514
Posted Monday, November 25, 2002 6:46 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
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.





Post #46515
Posted Tuesday, November 26, 2002 3:56 AM
SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: Moderators
Last Login: Yesterday @ 7:25 AM
Points: 8,369, Visits: 740
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.




Post #46516
Posted Tuesday, November 26, 2002 7:52 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Monday, September 15, 2014 8:57 AM
Points: 6,634, Visits: 1,872
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, 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 #46517
Posted Monday, March 22, 2004 8:30 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 5, 2014 9:04 AM
Points: 2,184, Visits: 1,976
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
Post #107422
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse