I'm 100% convinced this is a duplicate but after more than an hour of searching I just can't seem to find the answer.
When using special procedures (i.e. the sp_ ones like sp_executesql), is it wise to use the full 3-part identifier master.dbo (or master..) or just use them as is? I'm looking for the most performance optimized version of this:
1. sp_executesql
2. master..sp_executesql
3. master.dbo.sp_executesql
Are 2 and 3 identical in terms of performance specifically regarding the above (i.e. referencing master) and is it safe to user master.. or should you not risk it even on master since someone could still create another schema there at some point?
Much appreciated.
TL;DR;
Shouldn't be any noticeable performance difference.
The long story:
Whenever you are executing a stored procedure that starts with the sp_ prefix SQL Server will first search for it in master.dbo, so all three options should have the same performance.
From an article posted by Eric Cobb in 2015 entitled Why you should not prefix your stored procedures with “sp_”
Whenever SQL Server sees “sp_” at the beginning of a stored procedure, it first tries to find the procedure in the master database. As stated in the Microsoft documentation above, “This prefix is used by SQL Server to designate system procedures“, so when SQL Server sees “sp_” it starts looking for system procedures. Only after it has searched through all of the procedures in the master database and determined that your procedure is not there will it then come back to your database to try to locate the stored procedure.
Also, it quotes another official documentation (with a link to 2008 version, working on finding current version):
A user-defined stored procedure that has the same name as a system stored procedure and is either nonqualified or is in the dbo schema will never be executed; the system stored procedure will always execute instead.
That quote, even though I couldn't find in the documentation of current version, I can easily prove.
Consider the following script:
USE <YourDatabaseNameHere> -- change to the actual name of the db, of course
GO
CREATE PROCEDURE dbo.sp_who
AS
SELECT 'Zohar peled' as myName
GO
-- change to the actual name of the db, of course
EXEC <YourDatabaseNameHere>.dbo.sp_who
EXEC dbo.sp_who
EXEC sp_who
GO
DROP PROCEDURE dbo.sp_who -- cleanup
When tested on 2016 version (which is the server I've had available for testing),
All three exec statements executed the system procedure. I couldn't find any way to execute my procedure.
Now I can't fiddle around with the master DB on my server, so I can only show that it's true for existing system procedures, but I'm pretty sure that it's going to be the same for any procedure that starts with the sp_ prefix, even if you wrote it yourself to both the master database and your own, as Aaron Bertrand illustrated on his article under the title Another side effect : Ambiguity
However, even if that wasn't the case, unless having many procedures in the current schema, and running the stored procedure in a tight loop, I doubt you'll see any noticeable performance difference.
Later on in the same article:
As alluded to in the previous point, procedures named with “sp_” are going to perform slower. It may not always be noticeable, but it is there. Connecting to DatabaseA, jumping over to the master database and scanning every stored procedure there, then coming back to DatabaseA and executing the procedure is always going to take more time than just connecting to DatabaseA and executing the procedure.
Note that this paragraph is talking about performance issues executing a user-defined stored procedure that has the sp_ prefix - so let's reverse this process for a moment:
Suppose SQL Server would have to scan all the stored procedures in the current schema, and only then, if not found, go to Master.Dbo and start looking there.
Easy to see the more procedures you have in the schema the longer it takes. However - have you ever noticed how long it takes SQL Server to find the procedure it needs to run?
I've been working with SQL Server since it's 2000 version, and I've had my share of databases containing hundreds of procedures all cramped up in the same schema - but that was never a performance issue.
In fact, in over 15 years of experience with SQL Server, I've never encountered a performance issue caused by the time it takes SQL Server to find the stored procedure it needs to run.
