Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2008
    Location
    Leyland in NW England, UK
    Posts
    19

    Question Unanswered: Transaction Log on a separate disk?

    I work in MI Team. System we work on is SQL Server 2000 and has 3 databases set up for reporting purposes.

    The first one, Impact, is a replication of the company Progress database. This is easily the largest of the 3 and is about 130000 mb.

    The other 2, DB_MIS and BO_MIS, are DBs set up with data pulled from Impact and are about 10200mb and 7000mb.

    These 2 are updated on a daily basis via jobs running stored procedures and seem to be taking longer to finish each day to the point where we are struggling to get the data we need to send out reports to Directors/Management etc before lunch-time.

    The data files and the transaction logs for the 3 DBs are all on the same disk. With live systems I know that it's a golden rule that the two should be on separate disks as otherwise the Read/writer is constantly being interrupted every time a front end user inserts/updates/deletes data.

    Does this also hold true for an MI system? Would putting the transaction logs for the 3 DBs on a separate disk drastically reduce the amount of time that the SQL jobs/Sprocs would take to run?

  2. #2
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Putting log on a separate device from data will usually improve the performance of write operations for any database.

    Also, make sure the files aren't growing. It's much more efficient to pre-allocate storage for data and log rather than allow the files to auto-grow - especially if the growth increment is small.

    Some other recommendations:

    Storage Top 10 Best Practices
    Physical Database Storage Design
    baarf.com

  3. #3
    Join Date
    Aug 2008
    Location
    India
    Posts
    55
    Also check autogrow option for the db is set in MB and not in % . please keep in suitable RAID while doing so

  4. #4
    Join Date
    Jun 2008
    Location
    Leyland in NW England, UK
    Posts
    19
    Thanks for the above.

    Raj - We don't have RAID - everything's saved on the one disk!

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Cue disaster in 5....4....3....

    One thing that should be noted is that the performance benefit is only goven by having separate physical drives. Drawing a line down the middle of a drive, and calling one side C:\ and the other D:\ gives you no benefit for performance.

    And seriously, disk drives are the one moving part of a computer. They fail. They fail spectacularly. And usually they fail at 2:00 AM.

  6. #6
    Join Date
    Jun 2008
    Location
    Leyland in NW England, UK
    Posts
    19
    The system has been set up this way for the last 3 years - long before I started 6 mths ago.

    On first day I said to manager we should have data & transaction files on separate physical disks & would drastically improve performance but we don't look after the actual server - IT Team do.

    New manager has just started in new position above existing manager & he's keen for us to push to get new disk so just wanted to make sure that it would drastically improve the system performance.

    The system is used for MI reporting purposes. We have 3 databases on it, 2 of which are updated once a day (between midnight & 8am) and the main DB is updated every 2 hours, so we don't have constant updating by front end users - users run the reports on Business Objects from data on it.

  7. #7
    Join Date
    Apr 2008
    Location
    Along the shores of Lake Michigan
    Posts
    242
    When you say "updated", do you mean a total refresh of data or are you only updating based on inserts/updates/deletes from the source database? What is you transaction load? Eight hours, or nearly 12 if you can barely get reports out by lunch, seems extreme, even with your physical setup. Are the a lot of indexes on the tables you are affecting? As stated previously, are your data or log files auto-growing? How much memory do you have and how is it allocated (don't starve the OS)? What else is running on the server? There are so many variables that you need to explore.

    And as MCrowley so eloquently put it - one disk is a disaster waiting to happen. Get more drives, as much as your company is willing to spend. Get them up and running and if you need more advice about what you should do at that point, post back.

    And by all means, be sure you're have full, VALID, backups and appropriate log backups, if applicable. Good luck.

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    The system may have been set up poorly, but it is your responsibility, now. "It's not my fault" carries a lot less weight than "I told you so". The system has been running for 3 years. How much money would you be willing to bet it survives the next month? 2 months?

    As for the performance increases, you will only see the performance increases for separating log from data during updates. And then only if you have a disk bottleneck. Run perfmon, and check to see if this disk is suffering disk queues of 1 or more during updates. Remember, to also measure the write queue, and the read queue, so you can tell wht the actual bottleneck is.

    Ironically, by moving to a raid 1 system (mirrored drives), you would have 2 disks available for read activity, thus reducing any read latency. And I suspect that you have more read latency than write latency.

  9. #9
    Join Date
    Jun 2008
    Location
    Leyland in NW England, UK
    Posts
    19
    Thanks for all the advice - has been very helpful.

    My background is more MI than DBA so I'm not totally au fait with what the perfmon can tell me.

    Dportas sent me a couple of docs with info on that subject. Can anyone else point me somewhere I can pick up some useful tips/hints/info?

Posting Permissions

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