Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Why Object Qualification is important.

By Roy Ernest,

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_.

 

Total article views: 8391 | Views in the last 30 days: 8
 
Related Articles
FORUM

Object Execution StatisticsReport

Object Execution StatisticsReport

FORUM

Access that can allow to execute SP (create object), however not allow to create/drop/alter table from query window

Access that can allow to execute SP (create object), however not allow to create/drop/alter table fr...

FORUM

Stored Procedure At The Remote Server

Executing Stored Procedure At Another Server

FORUM

Creating Database Objects from a Trigger-invoked stored procedure....

I am trying to create job-specific database objects (views, sp) after creating the db and tables suc...

Tags
performance tuning    
security    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones