How much performance gained by qualifying tables with dbo. in the database?

Hello Folks:
The great majority of our queries and stored procedures do not
reference the tables with the dbo.table_name format. Due to some
production issues, we're not facing a situation that is forcing us to
redo our database by scripting it out, making a few modificationsm, and
importing the data right back into it. (please don't aske me why!)
I believe now is a perfect opportunity to owner-qualify all references
to the tables with dbo.table_name in the database scripts, and I
strongly believe the performance gain will be significant.
I need to sell this idea to my boss who thinks the risks of
manupulating the production database scripts is far graver than its
benefits, and he wants to now "how much" performance is gained by doing
this.
What do you folks think is a good answer to him? How can I show some
tangible benefits in terms of "gained performance"?
Thanks for you help!Hi Dan
Using the dbo prefix should help cache hits, and the performance gain will
depend on how busy your server is, it could be very little, but then I would
implement it regardless as it is "using best practices". Check out the
current cache misses using SQL profiler. You would also need to add prefixes
for stored procedures, views etc... At the same time you may want to check
the case being consistent.
If you don't use source code control then maybe this is the time start using
it!!
You can gain confidence in the outcome by comparing the two databases.
You should also do a full regression test, and it sounds like your superior
does not have confidence in the outcome of this.
Make sure that you cover all eventualities in the implementation plan and
have backout strategies at each stage. Do some trail runs (on copies of live
data) to get timings and proof that it works.
John
"Dan Gorrison" wrote:
> Hello Folks:
> The great majority of our queries and stored procedures do not
> reference the tables with the dbo.table_name format. Due to some
> production issues, we're not facing a situation that is forcing us to
> redo our database by scripting it out, making a few modificationsm, and
> importing the data right back into it. (please don't aske me why!)
> I believe now is a perfect opportunity to owner-qualify all references
> to the tables with dbo.table_name in the database scripts, and I
> strongly believe the performance gain will be significant.
>
> I need to sell this idea to my boss who thinks the risks of
> manupulating the production database scripts is far graver than its
> benefits, and he wants to now "how much" performance is gained by doing
> this.
>
> What do you folks think is a good answer to him? How can I show some
> tangible benefits in terms of "gained performance"?
>
> Thanks for you help!
>