January 7, 2008 at 4:11 pm
I have a new machine with 3 disk arrays which are each about 300gb. I will verify how many spindles are in each in the morning. Our databases are in simple recovery mode because they serve as a warehouse for our OLAP operations.
Inserts/Updates are about 40% and reads/writes are about 60% accross the board. Our operations guys have the arrays configured at RAID 5, but I am not sure what the stripe and chunk sizes are. My primary question is how to save the physical files. We use a lot of temporary files, so my first thought was to seperate files by data/log/tempdb, but the size of the arrays doesn't lend itself to that.
What are the performance implications of splitting all the data files in two (splitting between a primary and secondary file) using a proportional fill strategy?
In this scenario I could place the primary data files on array1, the secondary data files on array2 and place the tempdb and log files on array3.
Also if you have any thoughts on Raid 5 versus 10, or any other info that seems relevant please share.
Thank you
January 8, 2008 at 7:59 am
I have 8 spindles on one array and 5 spindles each on the other two. Please do send me your thoughts even if you are not sure they would be helpful.
January 8, 2008 at 8:11 am
see readme file in SQL Server 2005 CD.
January 8, 2008 at 9:07 am
Actually I don't have access to a SQL 2005 disk, so if there is another useful resource (I could get to online) that you think might be relevant to my scenario I would appreciate it.
Thanks
January 8, 2008 at 9:26 am
RAID 5 isn't recommended. I'd really look at R1 or R10 for this, they will give you better performance.
When you say temporary files, do you mean temp tables in tempdb or physical files on the filesystem? How are they created/destroyed? Is this an import?
The splitting of tempdb off from logs/data makes some sense. IF you have 3 arrays, you can move them all separately. Does the data fit on one array? How much log activity is there? That would help determine what might be recommended.
January 8, 2008 at 10:23 am
About raid 5 - I know raid 0 (striped) and raid 10 (striped + mirror) will give better performance, but is there a white paper or some other resource you could point me to that spells out the advantages releated to SQL Server because I will have to present my case to my operations guys. They don't want to sacrifice space ( because of money ) and they want some minimum level of recoverability
I meant temp tables which is why I am thinking that maybe separating tempdb might be beneficial.
The issue with placing tempdb on its own array is that the array will likely be highly underutilized, at least with regards to space. This is why I was thinking of maybe splitting the data files, and having Primary data files on array1, secondary data files on array2 and tempdb+log files on array3. Do you think this might be optimal?
P.S. Log writing is intensive because many of the updates and inserts are dealing with large volumes of data.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply