July 2, 2012 at 11:21 pm
Hi All,
Could you please let me know your comments/advises on the below mail received from my dev team.
---------------------------------------------------------------
Is this applicable to the version of SQL server we run, or do you know the equivalent tunable(s)?
http://msdn.microsoft.com/en-us/library/ms190768.aspx
I'm thinking this would be good to have on an SSD partition (approx 200GB or more). If it's already in RAM, that's another story. I don't think it is by default, and is likely either using the system disk or the datastore disk.
Is the tempdb configurable as that documentation states in SQL Server
2008 (or whichever we have in prod); or what else you know that is configurable/movable like this and would benefit from SSD?
----------------------------------------
Thanks and Regards,
Ravi.
July 3, 2012 at 6:13 am
Hi,
Sounds like a nice idea and i'm sure the performance would be great (Is it necessary?), but...
SSD have a certain amount of read / write cycles and the TempDB is quite a busy DB.
If SSDs are hotswapable and you don't mind changing them, go for it.
I myself would prefer to have long time reliability and slightly less performance.
July 4, 2012 at 5:38 am
Hi,
Just was reading about this. I think you should take a look at these posts for a more in depth explanation and comparison.
http://archive09.linux.com/feature/142658
http://www.novicksoftware.com/Articles/sql-server-ssd-solid-state-disk.htm
Looks like it's becoming quite common to use SSDs for both the data and the tempdb.
July 4, 2012 at 9:06 am
SSD's (Enterprise Level) have enough read and write cycles that you should not worry about them dieing out before the hardware becomes obsolete.
Here is what I have noticed, if you have an application that is very heavy on TempDB usage you will shave a few seconds off table creation and any updates/reads off the temp objects. You will however not get any benefit from inserts into the temp table as you are probably using a SAN or local spinning storage so you are limited to that speed. Personally I have a few large applications that use the temp db moderatly and we have seen during an 8 hour performance run that we shaved off 30-40 minutes by using a SSD tempDB.
If you are seriously looking into this then here is what you do, look for a decent RAID card (if not already in your server) make sure the RAID has enough cache to not slowdown the SSD (4GB should be plenty enough) then get 2 SSD drives sized correctly, then mirror the drives to protect yourself from any OEM failures.
Best way to test this out is to go and get a cheap 60GB desktop grade SSD plug it into your test / development server and see if it improves performance. All you have to do is update the locations of the temp db files and restart SQL then you will be using the SSD.
Hope this helps.
July 4, 2012 at 9:21 am
I'd concur with Adam, both on the analysis and advice. Tempdb can benefit, if you use it a lot, but you have to test.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
July 5, 2012 at 1:33 pm
You should take a look at products such as Fusion IO which can move the temp DB into the box SQL Server is running on and eliminate the time to get data across network. They also provide more reliability than a typical SSD.
July 6, 2012 at 6:32 am
Adam Seniuk (7/4/2012)
Best way to test this out is to go and get a good 60GB desktop grade SSD with a high published random IOPS rate plug it into your test / development server that has TRIM enabled and see if it improves performance. All you have to do is update the locations of the temp db files and restart SQL then you will be using the SSD.Hope this helps.
Fixed it for you. Many cheap desktop SSD's have awful random write performance, and if the server doesn't have TRIM support, particularly with the cheaper desktop drives, it may have degraded performance after all the blocks are written to.
We use local SATA/SAS SSD's for tempdb extensively here, and by and large they're great.
1) The tempdb load is taken entirely off your spindle disks and/or SAN bandwidth.
2) Tempdb data and logs can be put on the same set of SSD's (though, obviously, you can gain higher peak performance with separate sets of SSD's.
3) We've always used RAID1 and RAID5 for our SSD raidsets - just because it's tempdb doesn't mean we want SQL to go down if an SSD dies; SQL should stay up until a replacement's put in. There's no more reason to abandon redundancy of SSD's as there was to abandon redundancy on spinning disks.
4) BENCHMARK your SSD's - we see very high rates for most of our installations, but sometimes you'll see grossly sub-par performance... and it's time to call your vendor.
5) Peak performance, per drive bay, is (in a proper setup) equal to or greater than 15k spinning disks in all aspects. Sequential write throughput is equal to or greater (bay for bay... dollar for dollar, it's pretty poor), while random throughput is much, much greater. We've seen sets of six SSD's in RAID10/5 perform at over a gigabyte per second for sequential throughput - it takes active/active 8Gbps FC channels to match that, and if your tempdb is sucking that much up... and it's on dedicated local SSD's... you've still got all that bandwidth for your other databases.
6) If you're not IOPS and/or throughput bound, it's not going to help.
7) It's not going to help as much as more RAM if you're bound by an inability to cache enough data.
8) Be aware than when SSD's fail, they tend to fail hard and fast - no real warning, and often no way to erase your data. If you're bound to keep information confidential, warranty returns need to be planned for via "Keep your drive", budgeting, or something similar
July 6, 2012 at 8:44 am
Thanks for the update, I guess I just assumed some of that would be researched before purchasing. 😀
Also I can agree with some of the very cheap drives comment as I have had a few brand name drives die really early or slow down over time.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply