SQL Server Performance

Quick Question on Striping a RAID10

Discussion in 'Performance Tuning for Hardware Configurations' started by rweinstein, Jul 18, 2005.

  1. rweinstein New Member

    My Network team is setting up a RAID10 for me on a dev server and they are asking what "Stripe size" they should set for the drive. It can be set from 8K up to 256K.

    What should I have them set?

    Thanks.
  2. satya Moderator

    We have set it as 128K for one of the OLTP database server.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. FrankKalis Moderator

  4. satya Moderator

    ... and an introduction about the database usage will give better idea to suggest.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  5. rweinstein New Member

    Thanks. I'll have them set to 64K. Database usage is 2.7:1 read:write ratio. DB is 19GB on SQL 2000 EE.
  6. satya Moderator

    Ah.. then 64k would be sufficient and ensure to regulate database consistency check for optimum performance.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  7. bertcord New Member

    satya,

    Are you refering to the strip size or File System Block Size?

    rweinstein,
    are they asking you what strip size to use or how to format the drives? they are differnt things that I see people interchange often.

    StripElementSize = For my SAN the default is 128 blocks or 64KB
    StripSize = Size of strip across all drives. For example with a 12+1 RAID5 group and a 64KB stripe element the stripe size is 12*64KB = 768KB
    FileSystemBlockSize = How the drive is formated via the OS

    Bert
  8. rweinstein New Member

    Bert,

    I think they meant the StripElementSize. I checked the RAID config and it looks like they set it up properly.

    Satya/Bert/Others,

    I took a look at the "Controller Settings" of my new RAID configuration, please let me know if these are set properly for best performance:

    "Expand Priority" = Low
    "Rebuild Priority" = Low
    "Cache Ratio" - 50% Read 50% Write
    "Surface Scan Delay" - 15sec.

    Also, I looked at the "Fault Tolerance" of my two new RAID sets and they both say "Fault Tolerance: RAID 1+0" One set is a RAID 1 with 2 drives and the other set is a RAID10 with 6 drives.

    Is this correct? I didn't think a RAID1 could be coded to a 1+0?

    Thanks.
  9. joechang New Member

    raid 1 is essentially raid10 or 1+0 with 2 drives
  10. rweinstein New Member

    Joe,

    Thanks for the clarification.

    Does anyone have any ideas about the config. settings and how to optimize?

    I am seeing documentation indicating that it should be set to 100% write, but this is for Exchange server configurations.

    Thanks.
  11. joechang New Member

    which raid controller is this?, vendor & model
    if compaq, the default should 128K or 256K, which is usually good
    if this is a transaction app, try 10% read, 90% write for r/w split
  12. rweinstein New Member

    Joe,

    Thanks for the help.

    My development servers have HP Smart Array Controller 5302. I just purchased the new ones for my production servers that I am going to rebuild. These new ones are HP Smart Array 6404.

    I took Satya's advice and configured my development server to 64k for the RAID10 drive, but left the RAID1 drive at 128k.

    My DB is not a transaction application, I don't believe. It is my Enterprise Data Warehouse that has transactional data in Fact, Dimension, Staging, Aggregate and lookup tables. It is about 18GB and the read/write ratio is 2.7:1.

    It is set right now to 50%/50% split.

    One big problem is that I cannot find any documentation or web links explaining these Array Controller settings, best practices for all different types of uses and detailed definitions.

    I just don't see how setting it at 64kb vs. 256kb will help/hurt performance.If anyone has input or links to documentation, that would be great.

    Rodney

  13. bertcord New Member

  14. joechang New Member

    i would suggest going back to the 128K default, this has nothing to do with DB usage,
    the reason the default was picked has something to do with settings/size in the Windows OS,
    some internal value in Windows is 256K, i do not believe there is any documentation on this
  15. rweinstein New Member

    Joe,

    I think you are right. 128k seems better for me with the high reads and the RAID10.

    Bert,

    I ran the stats on my DEV server, but how do I read the report to know what the average size of IO my DB is using? Here is my data, but you may not be able to make sense of it without parsing. One last thing, what is the last query supposed to do (exec usp_FNFileStatsReport)? When I ran it, I don't get any results.

    Thanks.

    7/20/05 1:46 PM1005010240.00 KB00.20.200.00 KB0.00 KB0.00 KB0.80.00 KB0.40
    7/20/05 1:46 PM10060133121000.00 KB00.50.501.95 KB1.95 KB4.88 KB1.991.95 KB0.990
    7/20/05 1:46 PM10033896007.81 KB00.50.5015.63 KB15.63 KB34.18 KB1.9916.60 KB13
    7/20/05 1:47 PM1003081920.00 KB00.10.100.00 KB0.00 KB2.93 KB0.41000.00 KB0.20
    7/20/05 1:47 PM1000076800.00 KB00.10.100.00 KB0.00 KB2.93 KB0.41000.00 KB0.20
    7/20/05 1:47 PM10030650245.86 KB00.20.2029.30 KB29.30 KB24.41 KB0.811.72 KB0.40
    7/20/05 1:47 PM1003381920.00 KB00.10.100.00 KB0.00 KB2.93 KB0.41000.00 KB0.20
    7/20/05 1:47 PM1003076800.00 KB00.10.100.00 KB0.00 KB2.93 KB0.41000.00 KB0.20
    7/20/05 1:47 PM10033701445.86 KB00.20.2029.30 KB29.30 KB26.37 KB0.812.70 KB0.40
    7/20/05 1:48 PM1003076800.00 KB00.10.100.00 KB0.00 KB2.93 KB0.41000.00 KB0.20
    7/20/05 1:48 PM10030363522.93 KB00.20.2014.65 KB14.65 KB13.67 KB0.86.84 KB0.40
    7/20/05 1:48 PM10033450563.91 KB00.30.3013.02 KB13.02 KB16.60 KB1.27.81 KB0.60
    7/20/05 1:48 PM1003076800.00 KB00.20.200.00 KB0.00 KB2.93 KB0.81000.00 KB0.40
    7/20/05 1:48 PM1003081920.00 KB00.10.100.00 KB0.00 KB2.93 KB0.41000.00 KB0.20
    7/20/05 1:48 PM10033870407.81 KB00.50.5015.63 KB15.63 KB33.20 KB1.9916.60 KB16
    7/20/05 1:49 PM1003076800.00 KB00.10.100.00 KB0.00 KB2.93 KB0.41000.00 KB0.20
    7/20/05 1:49 PM1003381920.00 KB00.10.100.00 KB0.00 KB2.93 KB0.41000.00 KB0.20
    7/20/05 1:49 PM10030675845.86 KB00.20.2029.30 KB29.30 KB25.39 KB0.812.70 KB0.40
    7/20/05 1:49 PM1004687040.00 KB00.10.100.00 KB0.00 KB2.93 KB0.41000.00 KB0.20
    7/20/05 1:49 PM1003376800.00 KB00.10.100.00 KB0.00 KB2.93 KB0.41000.00 KB0.20
    7/20/05 1:49 PM10043737286.84 KB00.40.4017.09 KB17.09 KB28.32 KB1.5913.67 KB0.80
    7/20/05 1:50 PM1004681920.00 KB00.10.100.00 KB0.00 KB2.93 KB0.41000.00 KB0.20
    7/20/05 1:50 PM10033445443.91 KB00.20.2019.53 KB19.53 KB16.60 KB0.87.81 KB0.40
    7/20/05 1:50 PM10016122881000.00 KB00.20.204.88 KB4.88 KB3.91 KB0.81.95 KB0.40
    7/20/05 1:50 PM10030322562.93 KB00.20.2014.65 KB14.65 KB11.72 KB0.85.86 KB0.40
    7/20/05 1:50 PM1003076800.00 KB00.10.100.00 KB0.00 KB2.93 KB0.41000.00 KB0.20
    7/20/05 1:50 PM10033696325.86 KB00.20.2029.30 KB29.30 KB26.37 KB0.812.70 KB0.40
    7/20/05 1:51 PM10093563204.88 KB00.50.509.77 KB9.77 KB21.48 KB1.9810.74 KB0.999
    7/20/05 1:51 PM1003081920.00 KB00.10.100.00 KB0.00 KB2.93 KB0.41000.00 KB0.20
  16. bertcord New Member

    Joe,

    I have to disagree. If your database is doing large amounts of Large reads . For example on sharepoint documents are stored in the database. A larger block size would increase performance in this case. I do agree that it should be left along unless you have a very specific reason.

    Rodney,

    usp_FNFileStatsReport should give you the report of data you want. First get the RUNID from TBL_FNFileStatsTracking_tasks that you want to report on. The report joins TBL_FNFileStatsTracking to sysdatabases and sysaltfiles so the collection of data needs ot reside on the server you run the reports on. If the report still does nto run set the debug bit to true @debug = 1. When this is run it will give you the actually query that is being used to generate the report. Let me know how it goes

    For a general report run
    exec usp_FNFileStatsReport
    @RunID = 1 --RunID from table TBL_FNFileStatsTracking_tasks

    To see IO at the DBlevel
    exec usp_FNFileStatsReport
    @RunID = 1 --RunID from table TBL_FNFileStatsTracking_tasks
    ,@DBLevelGrouping = 1---0=none, 1=Database, 2=FileGroup, 3=File

Share This Page