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