SQL Server Performance

Raid and disk controller configuration for tempdb, Tran log and Database files

Discussion in 'SQL Server 2005 Performance Tuning for Hardware' started by WingSzeto, Jul 23, 2008.

  1. WingSzeto Member

    We are using SQL 2005 standard and the database size is about 40 gbytes. Tempdb activity is quite heavy. Based on the counter of transaction/sec for SQLserver: Database, the statistics shows high volume, in average about 4 times higher than the production database (like 100 vs 25).
    We are planning to upgrade our server to even more robust servers with a disk subsystem and both servers and disk subsystem are IBM brand. Our current database server is mainly a server with 1 cpu dual core, 4 gbytes of RAM and 6 disks, in which I put the tran log file in the C drive with the OS as Raid 1. Tempdb and database files are on a Raid 10 (4 physical disks). This configuration gives us an acceptable performance. 15 % of the time we have CPU bottelneck during 7:00 am to 7:00 pm hours. Tempdb size sometimes increased up to 40 Gbytes. We have some IO bottleneck, maybe 5% of the time.
    We are upgrading to two servers with a disk subsytem for even more redundant setup like clustering. We also like to make the performance even better. The disk subystem has a 12 HD in it. Here is what I am thinking.
    Raid 1(2 physical disks): : Tempdb
    Raid 1(2 physical disks): : Tran log
    Raid 10 (6 physical disks): databases
    2 hot spare
    The disk subsystem has dual active-active controllers. I am considering put one controller to handle the raid arrray for both tempdb and tran log. The second controller will handle the raid array for the databases.
    I have questions about this setup.
    1. since tempdb activity is quite heavy relative to the production db's, should I combine tempdb and production databases on to the same Raid 10 with a total of 8 physical disks? Or separating them as specified above is still a better way to go.
    2. If the above Raid configuration is ok, is it ok to have a controller handling the traffic of tempdb and tran log and another controller handling the traffic of production databases? Or should I have the controller handling tempdb and the other handling tran log and databases because of the tempdb activity?
    3. By the way, can Raid 1 consist of more than 2 disks? I thought it can't. The reason ask that is because during the raid 10 configuration under IBM configuration software, the Raid selection doesn't show Raid 10 but under Raid 1 it allows us to select more than 2 disks. When we finish the configuration, it shows the right amount of disk space as of Raid 10 but it still said it is a Raid 1 array. I am a little confused by it.
    If there is a better setup, please advise. Any help on this is very much appreciated.
  2. satya Moderator

    1. Seperate them based on tempdb usage within your platform and other databases usage.
    2. Differentiate them if your user databases will have more writes and reads on day to day activity.

Share This Page