Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
tomrippity
Starting Member
37 Posts |
 Posted - 2012-04-19 : 09:47:45
|
| Following up on a previous post..I have been tasked with creating a "best performance" scenario for an SQL/Web Server combo. Based on some research I have done, the following would be the best layout for our hard drives. Can anyone confirm this or make some suggestions? I pulled information from multiple websites and many of them had suggestions that contradicted one another. We have a 4U case that can house up to 32 HDDs. The number in the "quantity" column indicates my current HDD allocation, but feel free to change these. We also considered using SSDs as opposed to traditional HDDs. Feel free to indicate if some sections would benefit greatly from SSD and some could stay HDD. We are open to any recommendation that improves performance. Thanks in advance. Disk Quantity RAID VOLUME Purpose0 2 1 Mirrored C:\ Windows/ SQL Server Software1 2 1 Mirrored D:\ Web Files/Images2 2 0 Striping E:\ System Databases (TempDB)3 4 10 or 0+1 G:\ User Defined Databases4 2 1 Mirrored H:\ Transaction Logs5 2 0 Striping I:\ Backups, Batch Processing, Full Text Catalogs |
|
|
xyvyx
Starting Member
6 Posts |
Posted - 2012-04-24 : 10:57:14
|
| It looks like you're on the right track. Lots of variables of course, but generally, the more spindles, the better. For most SQL boxes I build these days, I often have more space than I need just so I can get more throughput on certain data volumes. If your budget allows it, max out that server with 32 disks. I'd probably put Master/Model/MSDB on one of the mirrored volumes, but TempDB on a separate striped set should be fine. With 32 disks, you'd probably also want a couple RAID controllers. I find it interesting to look at some of the TPC benchmark results & server configurations submitted by vendors. They can give you "pie in the sky" sort of server configuration... take notes, then scale back to support a more reasonable budget & data integrity scenario.http://www.tpc.org/information/benchmarks.aspThe most recent server I built was on a Dell R910 with a couple trays of SAS drive. I also used some SSDs, but only for TempDB... this was my first foray into solid-state storage, so I have some reservations on longevity and whatnot."If we can put it into a table, we can kill it"-Ahnold Sequelnator |
 |
|
|
Sachin.Nand
2937 Posts |
Posted - 2012-04-24 : 11:17:32
|
quote:
Disk Quantity RAID VOLUME Purpose0 2 1 Mirrored C:\ Windows/ SQL Server Software1 2 1 Mirrored D:\ Web Files/Images2 2 0 Striping E:\ System Databases (TempDB)3 4 10 or 0+1 G:\ User Defined Databases4 2 1 Mirrored H:\ Transaction Logs5 2 0 Striping I:\ Backups, Batch Processing, Full Text Catalogs
Now I would call that the most ideal configuration for sql server.Three things here though.What about the pagefile ? Where are you planning to place it ?Any benchmarking on ideal IOPS for your enviroment,stripe size and disk cluster size ?Are you going to place it on a SAN ? So you might need to consider the HBA I/O's and the SAN cache ?After Monday and Tuesday even the calendar says W T F .... |
|
|
|
tomrippity
Starting Member
37 Posts |
Posted - 2012-04-24 : 12:10:44
|
quote:
Originally posted by Sachin.Nand
quote:
Disk Quantity RAID VOLUME Purpose0 2 1 Mirrored C:\ Windows/ SQL Server Software1 2 1 Mirrored D:\ Web Files/Images2 2 0 Striping E:\ System Databases (TempDB)3 4 10 or 0+1 G:\ User Defined Databases4 2 1 Mirrored H:\ Transaction Logs5 2 0 Striping I:\ Backups, Batch Processing, Full Text Catalogs
Now I would call that the most ideal configuration for sql server.Three things here though.What about the pagefile ? Where are you planning to place it ?Any benchmarking on ideal IOPS for your enviroment,stripe size and disk cluster size ?Are you going to place it on a SAN ? So you might need to consider the HBA I/O's and the SAN cache ?After Monday and Tuesday even the calendar says W T F ....
Considered placing the pagefile on the same place as the user defined databases. No benchmarks as of yet on IOPS.We are currently considering using the PCIe based SSD's from OCZ, instead of such a large array of disks.. Our storage should be capable of about 410,000 IOPS. That should eliminate any bottlenecks from the system associated with large datasets and complicated queries, or at least I hope. But we are working on some tests to come up with real numbers soon. I will post again when we have a final solution and what the results indicated. |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2012-04-24 : 13:06:49
|
| 410000 IOPS is kind of bit hard to digest.http://www.oczenterprise.com/ssd-products/z-drive-c-series.htmland it claims to be for 4kB random reads but sql server does reads in 64KB blocks.So you need to be a bit careful on that part.After Monday and Tuesday even the calendar says W T F .... |
|
|
|
tomrippity
Starting Member
37 Posts |
Posted - 2012-04-24 : 14:43:35
|
quote:
Originally posted by Sachin.Nand
410000 IOPS is kind of bit hard to digest.http://www.oczenterprise.com/ssd-products/z-drive-c-series.htmland it claims to be for 4kB random reads but sql server does reads in 64KB blocks.So you need to be a bit careful on that part.After Monday and Tuesday even the calendar says W T F ....
I didn't expect to get 410,000 IOPS anyway, realizing that is theoretical and extremely unlikely, but since I am considering storage I don't yet have, I can only theorize anyway. In any case, this solution will still be much faster than any standard HDD or SSD. |
|
|
|
|
|
|
|
|
SQL HDD Server Configuration.