Introduction.

We all know that you should not prefix stored Procedures with sp_. You all know why you should not use it as well because you have read about it. You probably also know why you should qualify your Objects. BUT have you tried to prove it to yourself? Maybe it is time you tried to prove to yourself what you read is true.

Do not believe what others say blindly.

Let us first see what is meant by Object Qualification. For name resolution, SQL Server uses the primitive version of the Strong named .NET assembly for name resolution. Technically a complete name consists of 4 parts. They are

<Server>.<Database>.<Schema>.<Object>

By practice we do not use fully qualified object names; we typically use two part naming convention. That is <Schema>.ObjectName. We can actually disregard the Database name since that implies that the database is the Current DB that you are connected and the same applies to the Server. You only include that if you are querying a linked server.
But the schema part is not easy to infer if it is not explicitly specified. The way SQL Server works is if the Schema is not specified, it checks if the Current User is the owner of the object. If it is not, the next system check would be to see if the object is owned by the Database owner. If that is the case, then the SQL Server has to check if the current owner has permission to use the object for that specific context. We will now check what Microsoft say about object qualification and what are the problems if not provided.
Quoted as per Microsoft.
"If user "dbo" owns object dbo.mystoredproc, and another user "Harry" runs this stored procedure with the command "exec mystoredproc," the initial cache lookup by object name fails because the object is not owner-qualified. (It is not yet known whether another stored procedure named Harry.mystoredproc exists, so SQL cannot be sure that the cached plan for dbo.mystoredproc is the right one to execute.) SQL Server then acquires an exclusive compile lock on the procedure and makes preparations to compile the procedure, including resolving the object name to an object ID. Before it compiles the plan, SQL Server uses this object ID to perform a more precise search of the procedure cache and is able to locate a previously compiled plan even without the owner qualification.

If an existing plan is found, SQL Server reuses the cached plan and does not actually compile the stored procedure. However, the lack of owner-qualification forces SQL to perform a second cache lookup and acquire an exclusive compile lock before determining that the existing cached execution plan can be reused. Acquiring the lock and performing lookups and other work that is needed to get to this point can introduce a delay that is sufficient for the compile locks to lead to blocking. This is especially true if a large number of users who are not the stored procedure's owner simultaneously run it without supplying the owner name. Note that even if you do not see SPIDs waiting on compile locks, lack of owner-qualification can introduce delays in stored procedure execution and unnecessarily high CPU utilization."
Now let us try to prove it. What we will need to prove it is exclusive access to a system that has SQL Server DB installed. (Exclusive access is to reduce the noise generated by other applications and users). For this test we can create a test Database and create all the objects in it.
To create a simple Database with default settings, run this command on the Query Analyzer/SSMS (2005)

if db_id('test') is null
 create database test

Now let us create the objects required for this simple test.

use test
go
if object_id('dbo.t1','U') is not null 
 drop table dbo.t1

if object_id('dbo.spselectfromt1','P') is not null 
 drop proc dbo. spselectfromt1

-- let us create a user just for this test.
exec sp_addlogin 'User1','test'
GO
exec sp_adduser 'User1'
GO

create table dbo.t1(c1 int primary key)
go
insert t1(c1) values (987654321)
go
create procedure dbo. spselectfromt1
as
select * from t1
go

grant execute on dbo. spselectfromt1 to User1
go

Now all the objects have been created. Open up the SQL Profiler and add just the below mentioned events to be monitored and start monitoring.

• SQLStarting - This is to show that the SP has started its process
• SP:CacheMiss - If present, it will prove that it could not get the plan from the cache.
• SP:ExecContextHit - Shows that it was able to find the plan after doing additional work.
• SP:Starting - Now the actual statements have started processing
• Lock: Acquired - Shows that a lock has been acquired.
• Lock: Released - Shows that the lock that was made was released.

Open up a new query window that was authenticated (connected) by using the "Test1" user.

Make sure that you are in the right Database (Test). Once you have started the profiler, execute the stored proc without giving the Object Qualifier (Exec spselectfromt1). If you look at the output of the profiler, you will see these calls in order.

1. SQL: BatchStarting
2. SP: CacheMiss
3. Lock: Acquired
4. Lock: Acquired
5. SP:ExecContextHit
6. Lock: Released
7. SP:Starting

We see one CacheMiss, two LocksAcuired, one ExecContextHit, One Lock Released and finally the SP Starting.

Now let us see what will happen if the Object is qualified. Execute the Stored Proc specifying the Object Qualifier. (Exec dbo.spselectfromt1)
Now look at the output from the profiler. You can see that there are only couple of calls.

1. SQL: BatchStarting
2. SP:ExecContextHit
3. SP:Starting

Although we cannot quantify exactly how much additional work is done by the SQL Server to execute when Object Qualifier is not specified, the Engine must be doing something to make all the additional calls. For one stored Proc you do not see much but just imagine all the stored procs are being called without the Object Qualifier. SQL Server Engine is going to be taxed by all the additional calls.

We have now proved that not using Object Qualifier is costly. We will continue the test to find out how costly it is to execute a Stored Proc that is prefixed with sp_. First let us examine the reason why stored procs with sp_ as prefix is more costly.

When the SQL Server gets a call to execute a stored proc that has prefix sp_, first place it will look at is in the Master Database. SQL Server will look for a compiled plan in the Master Database but cannot find it there. That is the reason why you see a cachemiss even though you have specified who the owner of the stored Proc is. Now let us test to find out if this is really true. For that let us create a stored Proc with the name sp_selectfromt1.

if object_id('dbo.sp_selectfromt1','P') is not null 
 drop proc dbo.sp_selectfromt1

go
create procedure dbo.sp_selectfromt1
as
select * from dbo.t1
go
grant execute on dbo.sp_selectfromt1 to User1
go

Clear the profiler window and execute the Stored proc specifying the object qualifier. (Exec dbo.sp_selectfromt1). Check the profiler window. You will see these statements being executed.

1. SQL: BatchStarting
2. SP: CacheMiss
3. SP:ExecContextHit
4. SP:Starting

You can see one additional call than when the stored proc dbo.spselectfromt1 was executed. That is the SP: CacheMiss statement. Like mentioned above we cannot calculate what exactly is the additional load on the server but it has to do one additional statement. When hundreds and thousands of calls are done to a stored that is prefixed with sp_ then there will be additional load on the server.

Conclusion

We have seen based on the test done why a stored proc prefixed with sp_ is costly and also why Object Qualifier is also costly. The best thing to do is to make sure you qualify all objects with the Schema (Also known as Owner).
If you have any user created stored procs that starts with sp_, drop it and recreate it without the prefix sp_.