April 19, 2013 at 6:36 am
Hello, my company has a new Server:
64 GB RAM
2 x Intel 320 SSD 120GB RAID1
2 x SATA3 1TB RAID 1
We're going to use this server for our SQL Server 2012 production database only. It's an small 10GB database but high workload.
I would like to know wich is the best placement for OS, SQL Server 2012 binaries, MDF, LDF as TEMPDB in this Hardware.
Thanks.
April 23, 2013 at 3:12 am
Hello,
I would recommend the following scenario:
•Restore the production database on the testing server and simulate the similar workload, make sure that tempdb and a copy of the production database located on the same drive
•In the Windows Performance monitor create ~24 hours trace and capture Disk reads/sec and Disk writes /sec counters, save results as tsv (for example). Open the trace in the excel file and calculate average and stdev values for both counters. Let’s assume you have avg+stdev ~ 300 Reads/sec and ~300 Writes/sec
•Now you can calculate the storage.
Use the formula: (#Reads + #Writes* Penalty)/ # physical drives = drive throughput (I assume ~120 IOp/sec on SATA ; ~ 200 IOp/sec on SSD, difficult to find the SSD parameters)
RAID penalty on RAID 1 =2
On the 1st RAID 1, based on SSD (300 Reads/sec + 300 Writes/sec*2) / #physical drives =200 => need ~4.5 drives in RAID 1 , in real configuration =>6 drives
On the 2st RAID 1, based on SATA (300 Reads/sec + 300 Writes/sec*2) / #physical drives =120 => need minimum 7 drives in RAID 1 (8 drives)
Afterwards on Production measure the actual data with the performance monitor – use Avg Disk Sec/read and AVG Disk sec/write and check the 50 ms threshold
Both RAID 1 arrays , based on 2 physical drives, have a very limited capacity , around 100 reads/sec and 150 writes/sec.
On one of the arrays you have to install the OS and SQL server binaries, one the second the database files. Maybe you can combine the database logs and OS on SSD drives and database files on SATA, but first of all you have to estimate the load.
Best Regards,
Vladimir
April 24, 2013 at 6:19 am
Thank you very much Vladimir,
I will try to do all of this work. But which is the "theoretical best placement" for the files, according I already have the OS installed on the HDD.
I think the best I can do is:
HDD:
C:\ OS, SQL Sever binaries and other soft.
D:\ TempDB (in a different logical drive to avoid an uncontrolled growth crashes the OS)
SSD:
S:\ MDF and LDF. Formatted at 8Kb or 64kb?
What do you think? Or is better to keep MDF alone on the SSD, and put LDF on another logical drive E:\ in HDD (because of almost same sequential speed of SSD, and no volatility)?
Thank you again,
April 25, 2013 at 8:28 am
Hello,
It's not easy 🙂 because you have very limited options; the theory is simple - separate the sequential and random I/O, 64 KB formatting for MDF and default formatting for LDF.
I would recommend to split the MDF and LDF files of all databases including tempdb, it sounds me more important than separated location of the tempdb database.
C:\ OS, SQL Server binaries
D:\ MDF files + 64 KB formatting (not sure about the formatting, because I haven't used before the Windows logical partitions with different formatting options. The OS is already installed, may be better to try OS and LDF on SSD with 8 KB formatting and MDF files on the entire HDD drive with 64 KB formatting ). To avoid an uncontrolled growth of tempdb database you can restrict the size of the database and control the database size on the daily basis.
S:\ LDF files +8KB formatting. (I still have some concerns regarding the OLTP databases on SSD solutions. During the last month I have tested the performance of EMC VNX machine which has the mix of SSD and normal drives.I was very impressed with the reading performance of the storage, but I was not happy with the writing performance, maybe because of the preinstalled RAID6; finally we have asked the EMC support to help us to reconfigure the storage. )
And do the load test on your system
How about the backup strategy and backup locations? Are you planning to use the external or local location?
Thanks,
Vladimir
April 26, 2013 at 7:57 am
I would say put the DATA and LOG on the SSD (including tempdb), os on C:\ and binaries on D:\.
Backup can be done to the HDD or SSD depending on how fast it has to be done (HDD should be good enough unless you need 100MB/s+ backup speed).
SSD have incredible perf, you should not waste it by creating a bottleneck with DATA or LOG on a HDD.
April 26, 2013 at 8:21 am
js.cat (4/19/2013)
Hello, my company has a new Server:64 GB RAM
2 x Intel 320 SSD 120GB RAID1
2 x SATA3 1TB RAID 1
We're going to use this server for our SQL Server 2012 production database only. It's an small 10GB database but high workload.
I would like to know wich is the best placement for OS, SQL Server 2012 binaries, MDF, LDF as TEMPDB in this Hardware.
Thanks.
If high work load means lots of writes to disk do you really want to use SSD for that?! I wouldn't
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply