August 19, 2012 at 12:20 am
Hello,
I'm currently hosting a game server with the current specification
E1230
32GB Ram
2x 1Tb SATA
Windows 2008 server
SQL Server 2012
Databse size is 10Gb
Number of players 1k
- The problem at first I had I/O bottleneck with 2x1Tb as I was using the default SQL configuration and all the files .mdf and .ldf were stored in default C:/SQLSERVER
- Then I added 2 more 1 Tb disks and I isolated .mdf and .ldf and TempDB locations. Problem has been fixed and I/O is performing well.
- Now I have the opportunity to change the disks system to 4x 300GB SAS in HW RAID 10, the problem I'm limited to 4 Disks 1 Array only, so I will have to store mdf, ldf and tempdb files in the same array and not isolated.
- Will I have I/O issue with this configuration? it will be better than 4x single HDD's which every physical HDD contains mdf, ldf, and tempdb files which I already use at the moment without issue or performance could be worse with shared 4x SAS 300GB RAID 10?
Thank you
August 20, 2012 at 5:21 am
Will you absolutely have IO issues? No one can say based on the information provided. Are you more likely to have IO issues having data & log on the same drive (even on a great choice like RAID 10), yes, possibly. If you absolutely had to, especially since you're dealing with such a small database, maybe a seperate drive just for the log, non-raid. But I wouldn't do that unless I was for sure seeing IO issues. Even then, I'd suggest tuning the code first. That's usually a bigger problem than the hardware.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 20, 2012 at 7:54 am
I'd second Grant's note.
You can do some calculations on throughput you're using now, based on counters. You might gain confidence on the IO throughput you need.
However the other issue is with 4 separate disks that losing one disk means you lose data, or at least the server.
Can you do 2 arrays, each a R1? That could provide some separation, but protection. You'd have to look at the IOPS there.
Ultimately, better to tune the code as much as you can, which might give you more flexibility.
August 20, 2012 at 6:00 pm
Thanks for replies,
Unfortunately I don't know the throughput, I used a query to show how much read and write the db is doing and this is so far what I got,
READS WRITES
_40245492895956
_62123111887901
_24794411576825
_9333641031770
_505606822357
_357625715876
_437448592302
_533440526738
_482597321542
_497186248593
_298480185557
_216236103251
_877389101965
_61373953058
_4739247234
_6032836772
_5067133929
_6367433888
_12614813424
_2032312473
_1247212472
_6069568602
_62526226
_554564100
_1149732050
_1865031932
_74661851
_299681427
_1195861188
_442321176
_5520912
_115526694
_244758626
_11942567
_606264417
_5702397
_359351
_5586342
_49374285
_115055238
_3978214
_242199
_22881768
_11775130
_325320
I was thinking is this configuration, as I'm limited to 4 HDD's only, I already ordered the SAS disks with RAID10 HW controller from the host provider but I can change the RAID configuration anytime.
Looks RAID 10 4x300GB (1 partition 600GB) will not be ideal for storing OS, mdf, ldf and all the files or?
So I was thinking to change the configuration to:
C:/ OS (300GB SAS 1 Disk)
D:/ MDF database files (RAID 1) (300GB SAS 2 Disks)
E:/ LDF files and tempfb files (300GB SAS 1 Disk) ..... Will this configuration work better than the RAID 10 ARRAY?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply