March 3, 2015 at 8:42 am
Hi guys,
I need a bit of advice here... I'm having an argument with our infrastructure architect who has just gone and bought lots of SSD drives to use for our tempdb data and log files, sounds great doesn't it? There is a catch though, his plan is to add the disks to the two available slots in each blade in a RAID0+1 configuration, effectively giving you one usable drive, and adding both data and log files on to one disk.
I then pointed out that SQL Server best practice is to host tempdb data and log files on two separate drive to reduce contention. The architect then basically said that because this isn't spinning disk the issue of drive, r/w contention isn't an issue :ermm: I don't agree with this and wanted to get some opinions from the community, I'm still advising that two separate disks should be used but someone just went and spent £80k ($150k) on SSDs and doesn't want to back down... Advice and comments please
March 3, 2015 at 9:09 am
Log files have a totally different I/O pattern compared to data files. On log files you typically have sequential reads and writes, while on data files you typically have random reads and writes.
SSDs have far better random I/O performance compared to spinning disks.
Keeping this is mind, I highly doubt that your workload will benefit from putting log files on SSD drives, unless you are hitting your logs very heavily (are you?).
Tempdb is a much more sensible fit for SSDs, due to the heavy random I/O pattern.
Actually the answer is "it depends". Where are you hitting your I/O subsystem heavily?
So, the point is: are you spending your money wisely if you use premium storage for something that will bring slight performance improvements?
That said, your infrastructure guy might also be right about I/O contention (highly depends on your workload... but why did he buy SSDs then?), but that's not the point in my opinion.
-- Gianluca Sartori
March 3, 2015 at 9:22 am
To be honest I not quite sure why they bought so much SSD storage at this point, this was done before I joined the company. Th idea was that they wanted to attach SSDs to every physical blade to improve tempdb performance, however they way they designed it to have the data and log files on one SSD or to have only the tempdb data files on directly attached SSD and the tempdb log files residing on the NAS (yes, you read correct, the NAS - NetApp FAS-3140). This just seems like a very untidy an illogical design.
March 3, 2015 at 9:31 am
SSDs can really help improve performance, but it's not guaranteed that tempdb is what you want to put on that drive.
Probably your heavily written user tables would benefit from SSDs performance just as much as tempdb, or even more.
-- Gianluca Sartori
March 4, 2015 at 8:14 am
romanoplescia (3/3/2015)
Hi guys,I need a bit of advice here... I'm having an argument with our infrastructure architect who has just gone and bought lots of SSD drives to use for our tempdb data and log files, sounds great doesn't it? There is a catch though, his plan is to add the disks to the two available slots in each blade in a RAID0+1 configuration, effectively giving you one usable drive, and adding both data and log files on to one disk.
I then pointed out that SQL Server best practice is to host tempdb data and log files on two separate drive to reduce contention. The architect then basically said that because this isn't spinning disk the issue of drive, r/w contention isn't an issue :ermm: I don't agree with this and wanted to get some opinions from the community, I'm still advising that two separate disks should be used but someone just went and spent £80k ($150k) on SSDs and doesn't want to back down... Advice and comments please
What type of SSD do you have?
MCL based SSD lifetime is shorter than SLC and NAND, but SLC usually has slower capacity and it is more expensive.
Putting aside price and cost, because you said you bought it, you should find out what type of SSD you have. SSD are not like regular hard drives. To simplify (and it's not exactly like that, of course) once you used a cell (wrote on it) it cannot be reused. That leads to a shorther lifespan than a regular hard drive.
Now, let's go back to tempdb topic. All databases, at some point, will write or use tempdb, so the amount of writes and I/O is usually high. And depending of your workload and how many databases you have, that will be significant. For instance, we do have at work a 2TB database that runs reports constantly. The queries hit tempdb heavily. We have tuned down the queries but on that situation, we will require considerable amount of money to get quality SSD and enough space to reduce latency. RAM, on this case, may be better.
So putting both tempdb files on SSD (mdf and ldf) may not be bad, as they are not regular hard drives, but you may not get the performance improvement you should actually get if you consider other usage.
Me personally, I would these in that order:
1)Tune up queries that use tempdb (or see what uses tempdb, then tune)
2)Tune up TSQL for the app itself
3)Add more RAM
4) Upgrade to Ent edition if possible, so more RAM can be used
If you have tried all of the above and still you see I/O contention, then it's time to upgrade your SAN or I/O subsystem. Only then, I would start considering using SSD but probably for critical tables that are heavily accessed, not tempdb.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply