Performance and inter-database joins

We have ALOT of procs with joins of many tables spanning 2-4 databases at
times. Many of these procs are hit HARD during our busiest times. This
seems to me that it would be not the best way to do things. I understand
that sometimes there may be needs to go to other db's for data but shouldn't
that be an exception and not the normal rule?
Myself I'm pretty convinced that we don't have enough reasons to have the 5
different databases we have. They would all fit nicely into one db and
still only be 5-6GB...there are heavy dependencies between any combination
of these databases which seems to tell me they really should be one...
Any thoughts here? Am I concerned about performance unnecessarily? Our
server is running fine but our user base is growing consistently and I'd
like to keep it that way.
Thanks!
Tim Greenwood wrote:
> We have ALOT of procs with joins of many tables spanning 2-4 databases at
> times. Many of these procs are hit HARD during our busiest times. This
> seems to me that it would be not the best way to do things. I understand
> that sometimes there may be needs to go to other db's for data but shouldn't
> that be an exception and not the normal rule?
> Myself I'm pretty convinced that we don't have enough reasons to have the 5
> different databases we have. They would all fit nicely into one db and
> still only be 5-6GB...there are heavy dependencies between any combination
> of these databases which seems to tell me they really should be one...
> Any thoughts here? Am I concerned about performance unnecessarily? Our
> server is running fine but our user base is growing consistently and I'd
> like to keep it that way.
> Thanks!
>
There is no performance penalty for cross-database queries, to my
knowledge. Cross-SERVER queries, on the other hand, can suffer
significant penalties.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Tracy,
[vbcol=seagreen]
Unless he tests out his queries and compare them, we cannot be sure. On
complex queries, esp. ones that involve larger underlying datasets, the
performance could be very different due to significant changes in disk I/O.
Anith
|||> On complex queries, esp. ones that involve larger underlying datasets, the performance could be
> very different due to significant changes in disk I/O.
But that wouldn't be specific to inter-database traffic, right? That would be determined by file
configuration. I.e., one could use filegroups so that the file placement over the tables is the same
as when using several databases and we get the same result.
(I realize this is a bit theoretical, but my point is that the optimizer has the same information
and options whether or not we go across database boundary - assuming in the same instance of
course).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%233O3hvm3GHA.2152@.TK2MSFTNGP06.phx.gbl...
> Tracy,
>
> Unless he tests out his queries and compare them, we cannot be sure. On complex queries, esp. ones
> that involve larger underlying datasets, the performance could be very different due to
> significant changes in disk I/O.
> --
> Anith
>
|||>> But that wouldn't be specific to inter-database traffic, right? That[vbcol=seagreen]
Can we have two databases placed on the same filegroup? Otherwise, it would
have to be distinct physical file access. You are right in that one could
have the underlying files/filesgroup spread out similarly, but then it is
hard to prove one way or the other which is why he'll have to test out his
queries and compare them.
[vbcol=seagreen]
Sure, as far as the query optimizations go, agreed. But it cannot possibly
factor in all potential physical I/O information in execution plans, esp. if
the multiple files are distributed over the network or even on external
drives, or am I wrong here?
Anith
|||> Can we have two databases placed on the same filegroup?
Not unless you go back to 6.5 ;-)

> Sure, as far as the query optimizations go, agreed. But it cannot possibly factor in all potential
> physical I/O information in execution plans, esp. if the multiple files are distributed over the
> network or even on external drives, or am I wrong here?
Hmm, you confuse me a bit here. My original point was the optimizer has the same information
regardless of whether the tables involved are in the same database or are from several databases. At
least, that is how I believe it work. Also, to the best of my knowledge, the optimizer does not
factor disk layout or characteristica when creating an execution plan. Perhaps I should have said:
You can define a database using file groups so you get the same structure as if you had that set of
tables spread over several databases. (Assuming you don't introduce any table partitioning when
spreading over several databases.) If you do end up with a similar file placement of the tables, the
optimizer should produce similar plans.
Above is speculation to some degree. Who knows, perhaps the optimizer will take into account if, for
instance, a table is partitioned over different filegroups compared to the same filegroup (just an
example)?
However, there are more important factors, IMO. Having a related set of tables in the same database
has many advantages, IMO. Backup is only one of them, IMO a major one.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:u41w4$X4GHA.696@.TK2MSFTNGP06.phx.gbl...
> Can we have two databases placed on the same filegroup? Otherwise, it would have to be distinct
> physical file access. You are right in that one could have the underlying files/filesgroup spread
> out similarly, but then it is hard to prove one way or the other which is why he'll have to test
> out his queries and compare them.
>
> Sure, as far as the query optimizations go, agreed. But it cannot possibly factor in all potential
> physical I/O information in execution plans, esp. if the multiple files are distributed over the
> network or even on external drives, or am I wrong here?
> --
> Anith
>
|||>> Can we have two databases placed on the same filegroup?[vbcol=seagreen]
Somebody kill me.......! Actually I meant a single file, which I assume is
not possible. ( or is it? )
[vbcol=seagreen]
I was just emphasising on the fact that physical I/O could be a contributing
factor to performance differences. If the databases are on distinct files
( distributed or otherwise ) then it can contribute to the overall
performance of queries when the underlying implementation access distinct
physical files as opposed to a single one.
However I do appreciate your point. It can be the other around as well.
[vbcol=seagreen]
Agreed. On the same token if the underlying file placement of the files are
different, the performance could be different as well.
[vbcol=seagreen]
... which is all the more reason for the OP to test out his queries and see
it for himself.
[vbcol=seagreen]
Indeed
Anith
|||Thanks for the comments, Anith. Seems we are in agreement here, even if it took a couple of posts to
determine... :-)
On more thing, to answer one of your outstanding questions:

> Somebody kill me.......! Actually I meant a single file, which I assume is not possible. ( or is
> it? )
To be honest, I read your original question as "file". No, you cannot, as of 7.0, share the same
file over several databases. One file is owned by a database (a true subset of the database).
The old architecture was different, where you first created a database device (the file) and then
allocated storage ("segment", similar to a file group) from that file for the database. Thus, you
could end up with two databases using storage from the same file. This model doesn't really add
anything useful in the PC world, especially since we don't tend to use RAW devices. So, I'm glad MS
made the storage architecture much cleaner and simpler in the new architecture.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:OWVx4mY4GHA.292@.TK2MSFTNGP02.phx.gbl...
> Somebody kill me.......! Actually I meant a single file, which I assume is not possible. ( or is
> it? )
>
> I was just emphasising on the fact that physical I/O could be a contributing factor to performance
> differences. If the databases are on distinct files ( distributed or otherwise ) then it can
> contribute to the overall performance of queries when the underlying implementation access
> distinct physical files as opposed to a single one.
> However I do appreciate your point. It can be the other around as well.
>
> Agreed. On the same token if the underlying file placement of the files are different, the
> performance could be different as well.
>
> .. which is all the more reason for the OP to test out his queries and see it for himself.
>
> Indeed
> --
> Anith
>