SQL Server Performance

Two Databases

Discussion in 'Performance Tuning for DBAs' started by Luis Martin, Oct 23, 2007.

  1. Luis Martin Moderator

    I have 2 databases about 10Gb each. Both are using with similar reads and writes.
    Two physical disks with separates channels.
    What is the best option to place data and log files?
    1)
    Disk 1 )
    OS, SQL, system databases, log files.
    Disk 2)
    Data files.
    2)
    Disk 1)
    OS, SQL, system databases, one database.
    Disk 2)
    Other database.
    3)
    You tell me.[:D]
  2. techbabu303 New Member

    Nice test , But I guess you have not mentioned if these databases are small or big or if they are 24/7 application running on it with more than 1000 batch req/sec.
    [:#]
    Sat
  3. techbabu303 New Member

    Sorry the size you have mentioned , I would go for second one but would also place system database on disk 2 except for tempdb on another disk.
    Another disk for log files not place them along with data files or OS since it needs more reliable disk with more spindles and good raid with backup power offcourse.
    Cheers
    Sat
  4. satya Moderator

    Other questions crop up such as what is growth in next 3 to 5 years for these databases, what is the RAID configuation?
    I incline towards option 1 for keeping user and system databases seperately.
  5. Luis Martin Moderator

    I presume the grow will go to the double in 3-5 years.
    4 disk 160GB 15000 rpm, mirrored. Two in one channel, two in the other. Hardware Mirrored.
    IBM Xeon 2x dual core 2Gh, 4GB RAM. Windows 2003 Enterprise, SQL 2000 Enterprise. SQL dedicated.
    Actually (with actual server) more than 1000 batches. (no optimization yet)
  6. Luis Martin Moderator

    Well, I have to do this next saturday.
    So, I'm short of time.
    That is what I will do:
    Go with 1 option.
    The only difference will:
    Both database access one table a lot, so (in old server a lot of locks) I'll move one of that table to Disk 1 and let the other in Disk 2.
    Why?. Each user open severals copies of the same application and in each copy make invoices. When the application try to find the next invoice number, search and update one table to find out the next one. So, one user block other and the same user is blocked by itself. Such table means one page, so all the table are blocked. That is the reason to place one table (remember 2 databases) in one disk and the other in the other disk.
    I'm listening to any other opinion till Friday.
    Thanks to all.
  7. Luis Martin Moderator

    Waiting.....
  8. satya Moderator

    Is this a third party tool application?
    Still I go with option1 as it suits better for such type of application (in my exp.) You could avoid putting frequently access table on diff. filegroups in order to reduce such contention. But should avoid any sort of database maintenance optimization jobs for such defragmentation.
  9. Luis Martin Moderator

    Yes it is.
    Assuming option 1.
    I already have filegroups for some indexes, and I'll store in disk1.

    Should I put that table in that filegroup or in a new one?
  10. satya Moderator

    New is better based onthe data growth and sizes.
    (the problem is no one else - Mods are not giving their opinion, even I'm interested to hear them) [:)]
  11. Luis Martin Moderator

    I have a theory about that.
    Mods may assume "one moderator must to know everything" , so why to read one thread from a moderator?[:D]
  12. satya Moderator

    Good one....
    Jack of all trades and expert in none [;)]

Share This Page