SQL Server Performance Forum – Threads Archive
Any Formula Showing RAID 10 Read/Write vs. RAID 1?
I’m trying to determine if I should replace our RAID 1 tempdb array with RAID 10 on our most critical SQL Server. We are experiencing application performance problems due to a vendor’s application code, but it may take some time before the problems can be corrected. I’m trying to find a formula or table that breaks down read/write performance for RAID 1 and RAID 10. Any numbers or links to this information is appreciated. Thanks, DaveRAID 1 is simply a RAID 10 array with 2 disks,
forget what somebody else’s definition says, just think about it if you know your tempdb generates heavy write activity, then more disks will help
run SELECT * FROM ::fn_virtualfilestats(db_id(‘tempdb’), -1)
or
SELECT * FROM ::fn_virtualfilestats(-1, -1) to see which files get the most activity
Hi Joe, Thanks for pointing out the function. I joined it with sysfiles to see the corresponding file name and ran the script for tempdb and our primary user database. I assumed tempdb had more reads then writes since this is what I see with overall activity using Profiler, however the numbers below indicate otherwise. Tempdb was recreated last week as 4 data files of 500MB each to take advantage of the 4 CPU threads. Does the read/write ratio below appear to make tempdb a good candidate for RAID 10?
— tempdb Read/Write Activity DbId Name FileId TimeStamp NumberReads NumberWrites BytesRead BytesWritten IoStallMS
—— ————— —— ———– ——————– ——————– ——————– ——————– ——————–
2 tempdev1 1 719972765 25973 55109 1456685056 1810292736 3988
2 templog1 2 719972765 19 66934 446464 4093847040 62
2 tempdev2 3 719972765 25531 54983 1449836544 1809022976 3451
2 tempdev3 4 719972765 25748 55149 1451024384 1810587648 3745
2 tempdev4 5 719972765 25791 55597 1453162496 1814806528 3522 — Moxy50 Read/Write Activity DbId Name FileId TimeStamp NumberReads NumberWrites BytesRead BytesWritten IoStallMS
—— ————— —— ———– ——————– ——————– ——————– ——————– ——————–
5 Moxy50Sys 1 719972781 17516 19648 973340672 203137024 56367
5 Moxy50Log 2 719972781 8420 921837 5793442816 12158652416 4375
5 Moxy50Data 3 719972781 260356 262051 42060111872 8065990656 205374
5 Moxy50Ix 4 719972781 84027 183262 12726476800 3677847552 110466
it does not matter how many files you have (possibly for older versions)
what matters is how many disks supports the tempdb and the Moxy50 db i am seeing 100K reads & 220K to tempdb data
360K reads and 460K writes to Moxy50 data,
how long has SQL been up since last restart? most probably if you configured the disks per my refererence, there would not be a disk issue
Last restart: 7:00PM CST on 9/27 Current configuration is as follows: Drive RAID Disk Description
C——-RAID1 (2) 36.4GB – O/S – Same controller as Logs – Cache is 50% Read / 50% Write
E——-RAID1 (2) 36.4GB – Logs – Same controller as C – Cache is 50% Read / 50% Write
F——-RAID5 (3) 72.8GB – Data – Dedicated controller – Cache is 75% Read / 25% Write
G——-RAID0 (2) 72.8GB – Backups – Shared controller with tempdb -Cache is 50/50
H——-RAID1 (2) 36.4GB – Tempdb – Shared controller with backups -Cache is 50/50 Actually the number of files is something Microsoft recommends for 2005 and also for 2000. I just found out about it last month and spoke with our Microsoft TAM and one of their SQL experts. They sent me some white papers on the subject. Tempdb in SQL Server 2005:
http://msdn2.microsoft.com/en-us/library/ms190768.aspx The reason for multiple files for tempdb:
http://support.microsoft.com/default.aspx?scid=kb;en-us;328551 Working with tempdb in SQL Server 2005
http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx
Thanks again, Dave
note item 2 is for early SQL 2000,
no actual performance data is supplied
it is also a very software centric view of this issue so i really think you are just hearing regurgitation of advice from long ago if multiple tempdb files on 1 pair of disks really solves any actual tempdb problems
why are you still having problems? real disk problems are solved with massive IOPS and bandwidth (+ good design, query, indexing) for a four proc box
this should have been 4 racks of external storage (10-15 disks per rack)
spread across 8 SCSI channels file placements should have had main db data, temp data & backup all on one common pool (separate partitions acceptable)
and logs of main app and temp each on dedicated pair
The documentation pertaining to 1 tempdb data file per CPU indicates it is for 2005 and 2000 so I’m not sure why you say it’s early 2000. That being said I have yet to prove the change has significantly improved our tempdb performance and the amount of documentaiton on the Internet regarding this approach is minimal. The hardware performance for logs, data and backups is good. The server is constantly monitored and reveals no hardware bottlenecks, however we have seen heavy tempdb usage during reported application performance issues. Logical reads are very high during these times, which makes me wonder if I can help performance by moving to RAID 10 for tempdb, however it sounds like RAID 10 will only help me if my issue was a high number of writes vs. reads. Am I understanding this correctly in that if I am looking to improve read performance moving from RAID 1 to RAID 10 won’t buy me much or will the additional spindles still help reads as well as writes? Thanks, Dave
]]>