question

Ok, a couple of pointers.

First of all, the cache miss/compile lock scenario you're seeing is partially explained in my answer to this question. There's a couple of reference links in there. It sounds to me like the behaviour you're seeing is due to non-schema qualified references, rather than the fact that your procs are named sp_...

The guy saying that sp_ automagically makes it a system proc is talking rubbish. You can create your own procedures in the master database which can then be accessed from all DBs, but they are still not system procedures. I call them 'user system procedures'. System procedures have elevated privileges, such as access to internal tables that you wouldn't even realise are there unless you scripted out some existing system procedures and had a look at the source.

Hope they help.

I think the key point is what your BOL quote states. I have heard, I have not tested (I'm not even sure how to), that SQL Server will first look in master for any procedures prefixed with "sp_" which will cause a small performance hit, just like when you do not schema-qualify an object the users default schema is checked first, then dbo.

The performance hit for this sort of thing is exceedingly small. If you're building a system from scratch, avoid the hit and don't code that way. If you've got an existing system, you're paying the hit, but usually there are much more important things you need to be working on in your system. This should absolutely be avoided because, after all, why pay the hit if you don't have to, but it's not a showstopper (under most circumstances) so I wouldn't spend a lot of time worrying about it.

In SQL 2008, if a user created stored procedure begins with sp_ , the local database is searched first and THEN the master database. This behavior can be verified using the following code, substituting your local database of choice for my sandcastle db. However, system procs will not be overriden by local procs with the same name. (See discussion in comments below.)

However, I still think using SP_ is a questionable practice, as it creates the possibility that you might have two different versions of the same procedure, and one might get called by mistake. In the example below, if I intended to call sandcastle.dbo.sp_testproc from the model database but forgot to qualify the object name with the dbname, I'm still going to get output. It just may not be the result set I expected. If the schema of the result set isn't changed, but logic has changed in the sandcastle version, confusion may ensue.

Use SandCastle_BobH;
go

select @@version

use master;
go
CREATE PROCEDURE dbo.sp_testProc
AS
BEGIN
    SET NOCOUNT ON;
    	select 'Run from MASTER database'
END
GO

use sandcastle_BobH;
exec sp_testproc
go

Use SandCastle_BobH;
go

CREATE PROCEDURE dbo.sp_testProc
AS
BEGIN
    SET NOCOUNT ON;
    	select 'Run from local database'
END
GO

use sandcastle_BobH;
exec sp_testproc
go

use model;
exec sp_testproc
go


use master;
drop procedure dbo.sp_testProc
go

use sandcastle_Bobh
drop procedure dbo.sp_testproc
go

Hi, in your blog you have this article :

http://sqlblogcasts.com/blogs/davidwimbush/archive/2009/11/25/how-to-process-a-cube-in-a-sql-agent-job.aspx

I have one doubt can you please explain me why this file "rep01\joblogs\ProcessASOnBI1Log.xml" and not the "\rep01\joblogs\ProcessASOnBI1KeyErrors.log"

Thanks in advance cafc