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.