November 12, 2002 at 12:00 am
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/jtravis/sp_performance.asp
November 14, 2002 at 2:56 am
Interesting
I'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.
November 14, 2002 at 3:57 am
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 Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
November 14, 2002 at 7:23 am
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
Jon
November 14, 2002 at 7:24 am
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
Jon
November 14, 2002 at 7:47 am
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 master
So how do we explain these 2 sets of opposing results?
November 14, 2002 at 2:05 pm
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 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
November 14, 2002 at 4:09 pm
I believe Brain is right. If you look back I mark the sp as a system object by running
sp_MS_marksystemobject
against 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.
November 14, 2002 at 4:12 pm
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 Sabin
Co-author of SQL Server 2000 XML Distilled
http://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).
November 14, 2002 at 4:35 pm
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>!
November 15, 2002 at 5:00 am
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.
November 15, 2002 at 6:34 am
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?
November 15, 2002 at 7:33 am
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
@kbriankelley
November 15, 2002 at 8:36 am
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?
November 15, 2002 at 8:39 am
I do believe that is correct. Might check with profiler just to be sure. I may when I get a chance.
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply