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.