Hi all
A collegeue recently found an item on
http://www.sql-server-performance.c..._procedures.asp (do a search on
the page to "fully qualified name")
In theory this does make sense, and I can understand what the item is
getting at. I work with a team of 20 developers, and we had not implemented
that item.
I decided that before telling everyone to change the thousands of stored
procedures to use this convention, I wanted to be sure that it was worth it.
Anyway, I did a test using SSMS and it didn't seem to matter if I used just
the procedure name, a two part name or a three part name.
The article was written a while ago, so it must be directed at SQL2000. I'd
like to perform a better test to prove this item one way or another.
Can anyone suggest a fair, method of testing this claim and being able to
properly and fairly mesure the difference?
Regards
Colin Dawson
www.cjdawson.comHi, Colin
> Anyway, I did a test using SSMS and it didn't seem to matter if I used
> just the procedure name, a two part name or a three part name.
Well, I think if you specify fully qualified name it will help to SQL
Server to "find" the object more quickly , I mean not to run thro all
objects that have DBO. or all default schema , so it is just my point
of view
"Colin Dawson" <newsgroups@.cjdawson.com> wrote in message
news:Udh8g.67139$wl.47904@.text.news.blueyonder.co.uk...
> Hi all
> A collegeue recently found an item on
> http://www.sql-server-performance.c..._procedures.asp (do a search
> on the page to "fully qualified name")
> In theory this does make sense, and I can understand what the item is
> getting at. I work with a team of 20 developers, and we had not
> implemented that item.
> I decided that before telling everyone to change the thousands of stored
> procedures to use this convention, I wanted to be sure that it was worth
> it. Anyway, I did a test using SSMS and it didn't seem to matter if I used
> just the procedure name, a two part name or a three part name.
> The article was written a while ago, so it must be directed at SQL2000.
> I'd like to perform a better test to prove this item one way or another.
> Can anyone suggest a fair, method of testing this claim and being able to
> properly and fairly mesure the difference?
> Regards
> Colin Dawson
> www.cjdawson.com
>|||On Wed, 10 May 2006 08:10:28 GMT, Colin Dawson wrote:
>Hi all
>A collegeue recently found an item on
>http://www.sql-server-performance.c..._procedures.asp (do a search o
n
>the page to "fully qualified name")
>In theory this does make sense, and I can understand what the item is
>getting at. I work with a team of 20 developers, and we had not implemente
d
>that item.
>I decided that before telling everyone to change the thousands of stored
>procedures to use this convention, I wanted to be sure that it was worth it
.
>Anyway, I did a test using SSMS and it didn't seem to matter if I used just
>the procedure name, a two part name or a three part name.
>The article was written a while ago, so it must be directed at SQL2000. I'
d
>like to perform a better test to prove this item one way or another.
>Can anyone suggest a fair, method of testing this claim and being able to
>properly and fairly mesure the difference?
Hi Colin,
Contrary to what this article says, there's no need to database-qualify
any objects (unless they are in a different database of course).
Owner qualifying is important, though, for the following two reasons:
1. Reduce chance of unexpected bugs. (Example: EXEC MyProc suddenly
throws errors when executed by Alice, becuase she created a procedure
Allice.MyProc yesterday)
2. Performance gain. (EXEC MyProc for Alice takes two lookups: first for
Alice.MyProc, then [if the former doesn't exist] for dbo.MyProc).
The performance gain is real, but in most cases not big enough to
warrant the cost of changing thousands of existing stored procedures.
Just make it your policy to demand that all table, view, and procedure
names shoould be owner-qualified in new code, and that the owner should
be added when changing any existing code (exempting bugfixes and small
changes under heavy time pressurre).
Hugo Kornelis, SQL Server MVP|||Colin Dawson (newsgroups@.cjdawson.com) writes:
> A collegeue recently found an item on
> http://www.sql-server-performance.c..._procedures.asp (do a
> search on the page to "fully qualified name") In theory this does make
> sense, and I can understand what the item is getting at. I work with a
> team of 20 developers, and we had not implemented that item.
> I decided that before telling everyone to change the thousands of stored
> procedures to use this convention, I wanted to be sure that it was worth
> it. Anyway, I did a test using SSMS and it didn't seem to matter if I
> used just the procedure name, a two part name or a three part name.
Including the database name is meaningless, and only increases the
complexity of the programming. (Since you need to get to get the database
from somewhere, and hard-coding it seems like a bad idea.)
Including schema/owner on the other hand matters.
Let's first look at this on SQL 2000. On SQL 2000, each user has a default
schema with the same name as the user name. Thus, if fred says
EXEC some_sp
(or make this call through RPC), SQL Server will first lookup "some_sp" in
the cache, including the uid. If there is a fred.some_sp, there will be a
cache hit, else a cache miss. Later SQL Server will look up some_sp in
metadata, and when fred.some_sp is missing, it will try dbo.some_sp, which
it will find in the cache.
On the other hand, if fred says:
EXEC dbo.some_sp
he will get a cache hit directly.
On SQL 2005, this is somewhat different, as a user can have dbo as the
default schema. In this case, at least in theory, it should not matter
whether you specify dho or not.
Now, how to set the default schema of a user? Well, if you use the new
command, CREATE USER, the user's default schema will be dbo, and there will
be no schema with the user's name. But if you create users with sp_adduser
out of habit, you will get a schema with the user's name, and that will
be the default schema.
Since you cannot be sure that future users get not added in a proper way,
it appears to be a good recommendation to include schema on SQL 2005 as
well.
As for the question of what the impact is of not including dbo, well I don't
have any numbers, but on a busy system with few cycles to spare, I believe
all those cache misses can cause quite some aggrevation.
I should add that including owner/schema is even more important when you
work with dynamic SQL, be that sp_executesql or parameterised command from
the client (which is sp_executesql anyway). If there is a single unqualified
table in the query, the plan will be private to the user, and cannot be
used by other users. (Again, not if the user's default schema is dbo, but
do not rely on that.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks for the info. It's helping me to understand this particular nuance.
In this case, I think that I'm not going to insist on the change. The
reason of this is that the db is accessed from a middle tier. The middle
tier uses connection pooling so we need to standardise on a specific user.
This user we need to setup, to it is safe (in this instance) to rely on the
the schema being set to dbo.
I'll certainly remember this for when I'm working on and dynamic sql (of
which there is currently none in the entire app!)
If we find that the app comes even close to maxing out the processor (very
unlikley at this stage) then I'll take a look at forcing dbo. infront of all
objects.
Thanks for the enlightenment.
Regards
Colin Dawson
www.cjdawson.com
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97BF808BB6063Yazorman@.127.0.0.1...
> Colin Dawson (newsgroups@.cjdawson.com) writes:
> Including the database name is meaningless, and only increases the
> complexity of the programming. (Since you need to get to get the database
> from somewhere, and hard-coding it seems like a bad idea.)
> Including schema/owner on the other hand matters.
> Let's first look at this on SQL 2000. On SQL 2000, each user has a default
> schema with the same name as the user name. Thus, if fred says
> EXEC some_sp
> (or make this call through RPC), SQL Server will first lookup "some_sp" in
> the cache, including the uid. If there is a fred.some_sp, there will be a
> cache hit, else a cache miss. Later SQL Server will look up some_sp in
> metadata, and when fred.some_sp is missing, it will try dbo.some_sp, which
> it will find in the cache.
> On the other hand, if fred says:
> EXEC dbo.some_sp
> he will get a cache hit directly.
> On SQL 2005, this is somewhat different, as a user can have dbo as the
> default schema. In this case, at least in theory, it should not matter
> whether you specify dho or not.
> Now, how to set the default schema of a user? Well, if you use the new
> command, CREATE USER, the user's default schema will be dbo, and there
> will
> be no schema with the user's name. But if you create users with sp_adduser
> out of habit, you will get a schema with the user's name, and that will
> be the default schema.
> Since you cannot be sure that future users get not added in a proper way,
> it appears to be a good recommendation to include schema on SQL 2005 as
> well.
> As for the question of what the impact is of not including dbo, well I
> don't
> have any numbers, but on a busy system with few cycles to spare, I believe
> all those cache misses can cause quite some aggrevation.
> I should add that including owner/schema is even more important when you
> work with dynamic SQL, be that sp_executesql or parameterised command from
> the client (which is sp_executesql anyway). If there is a single
> unqualified
> table in the query, the plan will be private to the user, and cannot be
> used by other users. (Again, not if the user's default schema is dbo, but
> do not rely on that.)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Colin Dawson (newsgroups@.cjdawson.com) writes:
> In this case, I think that I'm not going to insist on the change. The
> reason of this is that the db is accessed from a middle tier. The middle
> tier uses connection pooling so we need to standardise on a specific
> user. This user we need to setup, to it is safe (in this instance) to
> rely on the the schema being set to dbo.
And you are on SQL 2005?
Else you need to make that standardised user dbo.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97C04703D6F1Yazorman@.127.0.0.1...
> Colin Dawson (newsgroups@.cjdawson.com) writes:
> And you are on SQL 2005?
> Else you need to make that standardised user dbo.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
Yes, I've been on SQL2005 for the last 12 months of so.
Regards
Colin Dawson
www.cjdawson.com
