Results 1 to 8 of 8
  1. #1
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6

    Unanswered: General design efficiency question

    My general question is whether there is anything to be gained by having 50 tables in one database versus 5 tables each in 10 databases.

    I have a number of different databases running on a server (SQL Server 2k). The different databases represent different functional groups, for instance car maintenance, cab reservation/dispatch, cab accounting, limo reservation/dispatch, limo accounting, etc. There is some crossover, for instance the cab dispatch system would look to car maintenance to validate the car number entered.

    A friend who happens to be IT Director at the local university suggested that the server would run more efficiently if there was only one database, rather than the roughly 12 I have now. His belief is that each separate database carries a certain amount of overhead, and combining them into one would be advantageous.

    Is he all wet, or would there be gains to be made?

    TIA
    Paul

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    There would be gains. It is just a matter of whether you would notice them. I have never tested the scenario, so I have no actual numbers. Personally, I would favor the single database approach, for permissions administration reasons. A user only needs permissions on a stored procedure and not the underlying tables, IF all of the tables it accesses are in the same database. Same for views.

    Now, suppose you want to restore the 10 databases back to a point in time just before your temp deleted a pile of data in "some tables". Do you want to do 10 separate restores, one?

    I am sure some of the other folks here can come up with other examples, if they tried.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    He's right for the wrong reasons.

    I don't think you would get a performance boost from combining the databases. You could make arguments for either increased efficiency or decreased efficiency either way.

    But...from a data management standpoint it makes administrative sense to combine the databases if they reference eachother for lookup values or cross-database queries. From you limited description of the situation, I would recommend combining them.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Thanks for your thoughts. I guess I'll look at going through the effort.
    Paul

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I read the first line and fell off my barstoo...um office chair

    Quote Originally Posted by pbaldy
    My general question is whether there is anything to be gained by having 50 tables in one database versus 5 tables each in 10 databases.
    Are you kidding? Ever hear of maintenance?

    I have a number of different databases running on a server (SQL Server 2k). The different databases represent different functional groups, for instance car maintenance, cab reservation/dispatch, cab accounting, limo reservation/dispatch, limo accounting, etc. There is some crossover, for instance the cab dispatch system would look to car maintenance to validate the car number entered.

    A friend who happens to be IT Director at the local university suggested that the server would run more efficiently if there was only one database, rather than the roughly 12 I have now. His belief is that each separate database carries a certain amount of overhead, and combining them into one would be advantageous.

    Is he all wet, or would there be gains to be made?

    TIA
    I like the cut of his gib....are all the tables named differently? Also, what about the apps? Would they be hard to port?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Aug 2004
    Location
    Calgary, Alberta
    Posts
    106
    Putting objects in different databases will allow you more flexibility in terms of allocating data and log files. (IE if you have to use primary for select into reasons, you can more easily manage it with multiple databases). Also, depending on your backup requirements you may be able to set some databases to simple mode, some to full, only do trn backups for certain databases and the list goes on. I think it's more a management thing then a performance thing.

    -kilka
    There is nothing more helpless and irresponsible than a man in the depths of an ether binge. -HST

  7. #7
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Quote Originally Posted by Kilka101
    Putting objects in different databases will allow you more flexibility in terms of allocating data and log files. (IE if you have to use primary for select into reasons, you can more easily manage it with multiple databases). Also, depending on your backup requirements you may be able to set some databases to simple mode, some to full, only do trn backups for certain databases and the list goes on. I think it's more a management thing then a performance thing.

    -kilka
    Nope ... and thank you for playing (at least for the first statement). You can create as many segments as you desire for a single database for both data and log files ... non-clustered indexes on multiple disks, split log files, etc., etc., etc.

    Not to mention that referential integrity rules can only be enforced with database, not across database.

    Also, IMHO, cross database joins requires the engine to drill down thru the metadata of the other databases to access the index and page structures of the "foreign" database since SQLServer "cooks" it database space when it is allocated.

    That being said, it would be interesting to see the results of an empirical test. If Paul Randal is still hanging around, maybe he can comment on this topic!

    -- This is all just a Figment of my Imagination --

  8. #8
    Join Date
    Aug 2004
    Location
    Calgary, Alberta
    Posts
    106
    What do you mean by "cooks" space when it's allocated?
    There is nothing more helpless and irresponsible than a man in the depths of an ether binge. -HST

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •