sp_DontDoIt

A lot of developers commonly prefix their stored procedures with sp_.  sp stands for stored procedure, and that's how Microsoft did their stored procedures, so that should be the naming convention, right?  Nope.  Sp stands for system procedure.  As for a naming convention, I personally like usp if I'm going to use one.  Frankly speaking though, enterprise manager, ADO.NET and query analyzer kinda make prefixing stored procedures with sp a bit pointless if you ask me, so I don't use them at all. 

Anyways, the point of this rambling is that I thought if you used the prefix sp_ SQL Server will search the master database for the stored procedure first.  This is even the case if the stored procedure exists in the current database.  Eeek.  You can verify this by running Query Analyzer.  Notice that you'll get a cache miss followed by a cache hit whenever you run the sp_ procedure.  Not good times.  If  I recall correctly, Olymars defaults to creating sp_ prefixed stored procedures...

My problem is that to demonstrate this fact, I created two sprocs named sp_Testing.  I created one in master, and the other in Northwind.  When I execute sp_Testing in any database, it runs just fine.  But when I run it in Northwind, it runs the sp_Testing from Northwind.  But I still see a cache miss, followed by a cache hit.  I'm mistified.

Here's a snippet from BOL about sp_ prefix:

It is strongly recommended that you do not create any stored procedures using sp_ as a prefix. SQL Server always looks for a stored procedure beginning with sp_ in this order:

The stored procedure in the master database.
The stored procedure based on any qualifiers provided (database name or owner).

The stored procedure using dbo as the owner, if one is not specified.
Therefore, although the user-created stored procedure prefixed with sp_ may exist in the current database, the master database is always checked first, even if the stored procedure is qualified with the database name.

Important  If any user-created stored procedure has the same name as a system stored procedure, the user-created stored procedure will never be executed.