SQL Server Performance Forum – Threads Archive

Quick Question on Striping a RAID10

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.

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.

64k or a multiple thereof are usually fine. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

… 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.

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

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.

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

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.

raid 1 is essentially raid10 or 1+0 with 2 drives

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.

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

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

Check this out http://h200005.www2.hp.com/bc/docs/…135606-004_rev4_us/TPM_135606-004_rev4_us.pdf If you are reading or writing large blocks of data on RAID10 larger size is good. You first need to know what the average size of IO that your db is using. You can use a utility I created in the following post to determine this.
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=9202 Bert

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

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

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

]]>