SQL Server Configuration - with RAID arrays

  • What would be the recommended set up to optimize performance on a new server with 8 144 GB hot swappable hard drives? It was suggested that 2 be mirrorred for the OS, and the other 6 be configured into 2 RAID5 arrays; 1 for logs and the other for data. Any suggestions or experience with configuration would be appreciated. Thanks.

  • You can get some pretty decent understanding from here - http://msdn.microsoft.com/en-us/library/ms190764.aspx

    Ultimately though it does depend on your environment as the amount of read / write activity really drives what configuration is going to work best for you and fit your budget and storage capabilities. A good query to start to get this information is as follows;

    select db_name(database_id), * from sys.dm_io_virtual_file_stats(-1, -1)

    This will give you an idea of what you are seeing at the disk level for each database file and should give you some solid information to base your decision on.

    The recommendation that you posted would be sufficient for an environment that was not going to see a lot of write activity. Getting more spindles (disks) associated with your disk presentation to the server will improve your read performance so, there times when combining everything into one group might be a good solution, especially when in a read-only environment. (I will probably catch flak from the rest of the community for putting this last line in here. :hehe: )

    Feel free to post with more questions if you need and I hope it goes well for you in your research and implementation.

    David

    @SQLTentmaker

    β€œHe is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • I like having two separate, physical partitions, one for logs and one for data, each having their own set of spindles. I prefer Raid 1 or 10, never been a big fan of Raid 5 but everybody has an opinion. Where are you going to store backups? Sounds like your heading down the right path. Post your final configuration when complete and what research led to the decision. Perhaps I can use it as added ammunition to get them to change their thinking here! Good luck.

    Here's a hit on this site where an earlier discussion took place:

    http://www.sqlservercentral.com/Forums/Topic526749-360-1.aspx#bm528638

    -- You can't be late until you show up.

  • DavidB (7/18/2008)


    You can get some pretty decent understanding from here - http://msdn.microsoft.com/en-us/library/ms190764.aspx

    Ultimately though it does depend on your environment as the amount of read / write activity really drives what configuration is going to work best for you and fit your budget and storage capabilities. A good query to start to get this information is as follows;

    select db_name(database_id), * from sys.dm_io_virtual_file_stats(-1, -1)

    This will give you an idea of what you are seeing at the disk level for each database file and should give you some solid information to base your decision on.

    The recommendation that you posted would be sufficient for an environment that was not going to see a lot of write activity. Getting more spindles (disks) associated with your disk presentation to the server will improve your read performance so, there times when combining everything into one group might be a good solution, especially when in a read-only environment. (I will probably catch flak from the rest of the community for putting this last line in here. :hehe: )

    Feel free to post with more questions if you need and I hope it goes well for you in your research and implementation.

    I do not have this procedure -

    select db_name(database_id), * from sys.dm_io_virtual_file_stats(-1, -1)

    The current system is using SQL 2000 and the database is loaded with changes from another system every night then used as the datasource for Crystal 10 Reporting from a third server. It is a very large system (currently 266 GB data file) So, not much transaction, just heavy usage during processing.

    The website you indicated was helpful for RAID discussions. Another proposal I have is for 2 HD used for mirroring OS, 1 HD as hot spare, and the other 5 split for data and logs. It decreases the storage capability, but provides backup if a disk fails. Thoughts?

  • barb.wendling (7/18/2008)

    I do not have this procedure -

    select db_name(database_id), * from sys.dm_io_virtual_file_stats(-1, -1)

    The current system is using SQL 2000 and the database is loaded with changes from another system every night then used as the datasource for Crystal 10 Reporting from a third server. It is a very large system (currently 266 GB data file) So, not much transaction, just heavy usage during processing.

    Barb, that's why it's important to watch which forum you post in. You'll get more accurate responses within the version-appropriate forums. πŸ˜‰

    -- You can't be late until you show up.

  • If it's an OLAP database (which is what it sounds like, with infrequent writes and lots of reads), RAID-5 should work well for the data files, but you probably won't need that much space for the log files (they tend to be small on that kind of system). You might even consider stuffing them onto the drive with the OS, and then you'll have one larger array for the data files, and another smaller array for the backups.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • tosscrosby (7/18/2008)


    barb.wendling (7/18/2008)

    I do not have this procedure -

    select db_name(database_id), * from sys.dm_io_virtual_file_stats(-1, -1)

    The current system is using SQL 2000 and the database is loaded with changes from another system every night then used as the datasource for Crystal 10 Reporting from a third server. It is a very large system (currently 266 GB data file) So, not much transaction, just heavy usage during processing.

    Barb, that's why it's important to watch which forum you post in. You'll get more accurate responses within the version-appropriate forums. πŸ˜‰

    OK, thanks, that was my oversight, however, we are migrating the system to SQL 2005, so in the future it will apply.

  • I do not have this procedure -

    select db_name(database_id), * from sys.dm_io_virtual_file_stats(-1, -1)

    For SQL server 2000, the function has a different name and different parameters.

    CREATE TABLE dbo.DatabaseFileIO (

    DatabaseName nvarchar (128) NOT NULL ,

    FileId smallint NOT NULL ,

    AsOfTs datetime NOT NULL ,

    ReadCnt bigint NULL ,

    WriteCnt bigint NULL ,

    ReadBytes bigint NULL ,

    WritteBytes bigint NULL ,

    IoStallMS bigint NULL)

    The capture into the above table on a regular basis (at least hourly and possibly more often)

    SELECT db_name(DbId) as DatabaseName

    ,FileId

    , Current_timestamp as AsOfTs

    ,NumberReadsas ReadCnt

    ,NumberWrites as WriteCnt

    ,BytesRead as ReadBytes

    ,BytesWritten as WriteBytes

    ,IoStallMS

    from ::fn_virtualfilestats (default, default )

    You should be looking at average wait times of less than 5 (iostallms / (reads + writes). Please note that the statistics are accumulative since SQL Server started, so you will need subtract the prior statistic to get the net over the time period. Spreadsheets can do this for you.

    SQL = Scarcely Qualifies as a Language

  • GSquared (7/18/2008)


    If it's an OLAP database (which is what it sounds like, with infrequent writes and lots of reads), RAID-5 should work well for the data files, but you probably won't need that much space for the log files (they tend to be small on that kind of system). You might even consider stuffing them onto the drive with the OS, and then you'll have one larger array for the data files, and another smaller array for the backups.

    That sounds like a configuration worth considering. I have been reading the system manufacturer's recommendations also. They selected RAID 5 and recommend OS, data, log (they will be smaller - simple option selected), index, tempdb and extracted file storage all be considered and grouped to decrease resource contention. They indicate data should be isolated, OS isolated, and group logs, tempdb and indexes as another volume. Backup storage and extract file storage can be another group. Still researching, but thanks for your ideas.

  • SELECT db_name(DbId) as DatabaseName

    ,FileId

    , Current_timestamp as AsOfTs

    ,NumberReadsas ReadCnt

    ,NumberWrites as WriteCnt

    ,BytesRead as ReadBytes

    ,BytesWritten as WriteBytes

    ,IoStallMS

    from ::fn_virtualfilestats (default, default )

    You should be looking at average wait times of less than 5 (iostallms / (reads + writes). Please note that the statistics are accumulative since SQL Server started, so you will need subtract the prior statistic to get the net over the time period. Spreadsheets can do this for you.

    My Average Wait Time was 6.

  • "My Average Wait Time was 6. " No too bad, so now look at more detail:

    select * from (

    SELECT cast( IoStallMS as float) / ( NumberReads + NumberWrites) as WaitTime

    , db_name(DbId) as DatabaseName

    , FileId

    , Current_timestamp as AsOfTs

    , NumberReads as ReadCnt

    , NumberWrites as WriteCnt

    , BytesRead as ReadBytes

    , BytesWritten as WriteBytes

    , IoStallMS

    from ::fn_virtualfilestats (default, default )

    ) as IO

    where WaitTime > 6.0

    order by WaitTime desc

    What databases and files are showing the highest waits? If tempdb, have you optimized to have one file per cpu ?

    You should also map each database file to a disk (use sp_helpfile) and determine if there is large amount of IO on some disk and less IO on other disks. You may needs to balance the disk IO by moving some files to other disks. You also may find that there is are just more IO requests than the disks can handle and that although the disk can hold lots of data, it can only be partially used else the IO requests will exceed the disk capability. The 15K RPM drives usually have a random seek time of 2.5 milliseconds so that they can only support 400 random IO requests per second.

    SQL = Scarcely Qualifies as a Language

  • barb.wendling (7/18/2008)


    What would be the recommended set up to optimize performance on a new server with 8 144 GB hot swappable hard drives? It was suggested that 2 be mirrorred for the OS, and the other 6 be configured into 2 RAID5 arrays; 1 for logs and the other for data. Any suggestions or experience with configuration would be appreciated. Thanks.

    Barb, i would set this up as follows

    OS = 2 x 144GB in RAID1

    LOGS = 2 x 144GB in RAID1

    DATA = 3 x 144GB in RAID5

    1 for spare

    Of course it does depend on the databse sizes you expect to house. You may find you need to use 4 x 144GB RAID5 for the datafiles if the databases are large

    RAID 10 is very fast and tolerant but the disk cost is extremely high and not viable with just 8 disks.

    To recap, SQL logs require a RAID1 array as this provides the best write performance.

    SQL data files require RAID 5 as this offers best read performance

    RAID 10 is the best of both worlds but at an extremely high cost

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" πŸ˜‰

  • Thanks for the recommendations and brief summary. I am continuing to research the options and will post the result selected by management.

  • Carl Federl (7/18/2008)


    "My Average Wait Time was 6. " No too bad, so now look at more detail:

    select * from (

    SELECT cast( IoStallMS as float) / ( NumberReads + NumberWrites) as WaitTime

    , db_name(DbId) as DatabaseName

    , FileId

    , Current_timestamp as AsOfTs

    , NumberReads as ReadCnt

    , NumberWrites as WriteCnt

    , BytesRead as ReadBytes

    , BytesWritten as WriteBytes

    , IoStallMS

    from ::fn_virtualfilestats (default, default )

    ) as IO

    where WaitTime > 6.0

    order by WaitTime desc

    What databases and files are showing the highest waits? If tempdb, have you optimized to have one file per cpu ?

    You should also map each database file to a disk (use sp_helpfile) and determine if there is large amount of IO on some disk and less IO on other disks. You may needs to balance the disk IO by moving some files to other disks. You also may find that there is are just more IO requests than the disks can handle and that although the disk can hold lots of data, it can only be partially used else the IO requests will exceed the disk capability. The 15K RPM drives usually have a random seek time of 2.5 milliseconds so that they can only support 400 random IO requests per second.

    The data bases showing the highest waits are DB that support web applications that monitor the system. The others are DB that support error handling for interface applications. tempdb was not is tne list (less than 6 AvgWaitTime).

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply