May 12, 2010 at 10:37 am
I 'm wondering do we have to qualify the sql objects (like procs) when the sql user has a default schema of dbo. This is for Sql 2005
-- If this user: has read/write and execute privilege on a proc
and in the application it does this:
execute myproc
-- would this cause a recompilation because it doesnot have a dbo as a qualify name in front?
In sql 2000, most articles i read say that a sql user that doesnot have dbo rights, will cause a recompilation. But what about in sql 2005?
May 12, 2010 at 11:39 am
So far as I know, procs do NOT get recompiled just because you leave off the schema name in the EXEC statement, not even in 2000. I'd like to see the articles that say they do, so if you could post links, I'd appreciate it.
Procs recompile when 1) the service is restarted, 2) the proc is forcibly recompiled, or 3) when a proc is created with the WITH RECOMPILE option and so recompiles every time it is run.
But, SQL Server does jump through hoops if you don't qualify the schema name. Meaning the engine will check the user's schema first and then the dbo schema. This itty bitty amount of processing may not mean much to you now, but on a huge server with lots of procs, that itty bitty amount of processing adds up and can slow down performance. So it's just a good practice to include the schema.object name in all your references.
Especially if you have objects with identical names but owned by different schemas.
May 12, 2010 at 12:09 pm
The link that says that it will cause recompilation if you don't qualify the storeproc with a dbo
is
May 12, 2010 at 12:40 pm
This article doesn't actually say the server *will* recompile the proc. It says it might.
Best Practice
It is best to owner qualify stored procedure names when you execute a procedure. This allows for better clarity and easier reuse of the existing execution plan by the current user.
Meaning if the server cannot find a previous execution plan cached, it will indeed compile the proc (or recompile as the case might be) to get a cached execution plan. This is more common when caches are cleared for one reason or another.
By not qualifying the owner name, SQL Server enters the compile code and acquires a COMPILE lock on the procedure. However, eventually it determines that a new plan is not required (assuming no other reasons apply), so it does NOT recompile the plan at this point due to the lack of qualification. However, the extra step of getting a COMPILE lock on the procedure can cause blocking contention in severe situations.
Here the article says it does not necessarily recompile the proc but that it takes the extra time determining whether it has to or not.
I can see where your confusion is, but the article doesn't actually say leaving off the schema name will force a recompile automatically.
May 12, 2010 at 10:20 pm
you say:
"But, SQL Server does jump through hoops if you don't qualify the schema name. Meaning the engine will check the user's schema first and then the dbo schema. This itty bitty amount of processing may not mean much to you now, but on a huge server with lots of procs, that itty bitty amount of processing adds up and can slow down performance. So it's just a good practice to include the schema.object name in all your references."
what if the sql user schema is dbo?
Example - name of sql user is : Anna_user with read and execute privilege on Database_X. Anna_user default schema is dbo.
The engine in this case will check the (user's schema first) dbo , correct? It would not check the Anna_user as the qualifer right?
May 18, 2010 at 4:21 am
It checks to see if Anna_User has a schema first. That is still a step that can be avoided if you use schema.object qualifications.
You may not notice the performance hit on small databases, but I promise you, it is there and the bigger your database gets, the more noticable it is. Hence, best practices are to use S.D.O.T qualifications for objects.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply