SQL Server Performance

Disk, Partition, Logical Drive, Physical Drive

Discussion in 'Performance Tuning for DBAs' started by danielreber, Mar 5, 2003.

  1. danielreber New Member

    I have a client that I want to make sure that I give them the correct information. They have a NT 4 server that has 7 physical drives. When the server's array (they only have one) was configured, all 7 drives where lumped into 1 disk (disk(0)). They then created 3 partitions C,S,T. The os & sql are on C, the database is on S and tempdb is on T. Will they get any performance gains with this setup? Or do they need to separate the physical disks more.

    Any help would be great.

    Thanks.

    Daniel Reber
    Datamasters, Inc
  2. bradmcgehee New Member

    I am assuming that the array is RAID 5. It is possible to eek out a little more performance with a slightly different configuration, but I would recommend using what you current have, assuming the reads exceed the writes (which is what most typical databases experience). The performance difference will be minimal, unless the application is very write heavy, and if so, then a different configuration should be explored.

    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com
  3. danielreber New Member

    Hmmm... Yes it is RAID 5. My understanding was that tempdb, heavily used indexes & tables should be own their own separate physical drives. But when the array is configured and all 7 drives are lumped into 1 disc then how do you know that tempdb is on it's own drive? Is it because it is RAID 5 or is it because 3 partitions where created or both?

    Thanks

    Daniel Reber
    Datamasters, Inc
  4. bradmcgehee New Member

    As I mentioned earlier, there are some minor benefits to separating tempdb, transaction logs, heavily used indexes, etc, especially if these all apply to you, but the benefit is generally small. Using a RAID 5 array for everything, especially one with 7 drives, will also provide very good performance if the reads exceed the writes. This is because there are 7 drives to spread out the data, and the more data can be spread out over multiple drives, the better for performance, especially read performance. Unless the database has heavy write activity, or the tempdb database is heavily used, the current configuration is fine.

    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com
  5. danielreber New Member

    Last question [<img src='/community/emoticons/emotion-1.gif' alt=':)' />] Here is the scenario. The database is used for analytical purposes only. There is a daily download, that is run during off peak hours, of new information that uses tempdb heavily. During the day, only ad hoc queries are run. Right now the database is 50GB and is expected to go to 100GB by the end of the year. So the queries will be against relatively large tables. <br /><br />Will this, not including the download, constitute a heavily used tempdb?<br /><br />Thanks<br /><br /><br />Daniel Reber<br />Datamasters, Inc
  6. bradmcgehee New Member

    My gut feeling (and the only way to no for sure is to test) is to leave the system configured as it is now. Seven disks in the array will afford a lot of optimized I/O access, even for the tempdb database. Also, since the tempdb will be used mostly in off hours, this is another reason not to worry about it.

    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com

Share This Page