May 30, 2016 at 11:56 am
Hi,
i can't find any new article from microsoft about using SQL server 2012/2014 on SSD drive.
what is the best practice about putting DATA file,LOG file and TEMPDB files on SSD RAID's perspective and what types of SSD?
THX
May 30, 2016 at 1:17 pm
I wasn't aware there was such a document. What is great for one environment could be poor for another environment. Is your environment 3 low-end SSDs in a low-end SAN coming through one 1Gb iSCSI NIC or do you have 6 FusionIO cards inside the server? Are you massively write-intensive or mostly/exclusively reads outside of an ETL window? So many variables ...
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 31, 2016 at 4:48 am
I'm with Kevin. You'd be better off looking at the vendor site for whatever drive system you have. I know, for example, that PurStorage has a lot of guidance on how best to configure their system with SQL Server. Since the hardware is so disparate, anything Microsoft said beyond "SSDs are faster than spinning rust" wouldn't be of much use.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 31, 2016 at 5:05 am
My experience with SSDs on a write-intensive instance was very bad. The SSDs went out for 2 months.
Igor Micev,My blog: www.igormicev.com
May 31, 2016 at 7:57 am
Igor Micev (5/31/2016)
My experience with SSDs on a write-intensive instance was very bad. The SSDs went out for 2 months.
What SSDs did you have? What was very bad about the experience? What does it mean "went out for 2 months"?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 31, 2016 at 8:04 am
TheSQLGuru (5/31/2016)
Igor Micev (5/31/2016)
My experience with SSDs on a write-intensive instance was very bad. The SSDs went out for 2 months.What SSDs did you have? What was very bad about the experience? What does it mean "went out for 2 months"?
I'm curious too. Like anything there are good and bad SSDs, but overall, based on all the feedback I've seen, the consultants I've worked with, they're considered to be extremely reliable.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 31, 2016 at 9:49 am
Grant Fritchey (5/31/2016)
TheSQLGuru (5/31/2016)
Igor Micev (5/31/2016)
My experience with SSDs on a write-intensive instance was very bad. The SSDs went out for 2 months.What SSDs did you have? What was very bad about the experience? What does it mean "went out for 2 months"?
I'm curious too. Like anything there are good and bad SSDs, but overall, based on all the feedback I've seen, the consultants I've worked with, they're considered to be extremely reliable.
I don't know the brand of the SSD.
Its response time in the very beginning was perfect. During time it had been increasing to the value of about 1500 ms, and we decided to leave it and move to RAID.
It got "wear out" - was told to me by the sys/network admin, but its age was very short. Actually we measured 5K updates/sec on the instance at that time, and that was probably the reason for its crash.
Igor Micev,My blog: www.igormicev.com
May 31, 2016 at 10:26 am
Igor Micev (5/31/2016)
Grant Fritchey (5/31/2016)
TheSQLGuru (5/31/2016)
Igor Micev (5/31/2016)
My experience with SSDs on a write-intensive instance was very bad. The SSDs went out for 2 months.What SSDs did you have? What was very bad about the experience? What does it mean "went out for 2 months"?
I'm curious too. Like anything there are good and bad SSDs, but overall, based on all the feedback I've seen, the consultants I've worked with, they're considered to be extremely reliable.
I don't know the brand of the SSD.
Its response time in the very beginning was perfect. During time it had been increasing to the value of about 1500 ms, and we decided to leave it and move to RAID.
It got "wear out" - was told to me by the sys/network admin, but its age was very short. Actually we measured 5K updates/sec on the instance at that time, and that was probably the reason for its crash.
1) How many years ago was this? They weren't NEARLY as robust as they are now, and many early drivers/firmware suffered from HORRIBLE write-amplification issues. Even up to a few years ago there were problems here.
2) The brand of the SSD is critical here too. Low-end SSDs do not come with much over-provisioning. Enterprise-class SSDs on the other hand typically have PETABYTE+ write capability. That is a stunningly-large amount of writes before they "wear out"
3) Another factor can be how full you made the drive. And was your stuff the only thing on there?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 31, 2016 at 10:46 am
TheSQLGuru (5/31/2016)
Igor Micev (5/31/2016)
Grant Fritchey (5/31/2016)
TheSQLGuru (5/31/2016)
Igor Micev (5/31/2016)
My experience with SSDs on a write-intensive instance was very bad. The SSDs went out for 2 months.What SSDs did you have? What was very bad about the experience? What does it mean "went out for 2 months"?
I'm curious too. Like anything there are good and bad SSDs, but overall, based on all the feedback I've seen, the consultants I've worked with, they're considered to be extremely reliable.
I don't know the brand of the SSD.
Its response time in the very beginning was perfect. During time it had been increasing to the value of about 1500 ms, and we decided to leave it and move to RAID.
It got "wear out" - was told to me by the sys/network admin, but its age was very short. Actually we measured 5K updates/sec on the instance at that time, and that was probably the reason for its crash.
1) How many years ago was this? They weren't NEARLY as robust as they are now, and many early drivers/firmware suffered from HORRIBLE write-amplification issues. Even up to a few years ago there were problems here.
2) The brand of the SSD is critical here too. Low-end SSDs do not come with much over-provisioning. Enterprise-class SSDs on the other hand typically have PETABYTE+ write capability. That is a stunningly-large amount of writes before they "wear out"
3) Another factor can be how full you made the drive. And was your stuff the only thing on there?
1) It was last year this month.
2) I could not say about the class. It was a 128GB storage which was costing about $3000 at that time.
3) The drive was about 60-70% full
Igor Micev,My blog: www.igormicev.com
May 31, 2016 at 1:15 pm
$3K is a lot of money for a 128GB SSD. I suppose you could have burned it out with writes, but a few months really isn't very long.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 31, 2016 at 1:43 pm
TheSQLGuru (5/31/2016)
$3K is a lot of money for a 128GB SSD. I suppose you could have burned it out with writes, but a few months really isn't very long.
I was surprised by its cost when i was told that. Probably the writes had done it.
Igor Micev,My blog: www.igormicev.com
May 31, 2016 at 1:43 pm
Igor Micev (5/31/2016)
Grant Fritchey (5/31/2016)
TheSQLGuru (5/31/2016)
Igor Micev (5/31/2016)
My experience with SSDs on a write-intensive instance was very bad. The SSDs went out for 2 months.What SSDs did you have? What was very bad about the experience? What does it mean "went out for 2 months"?
I'm curious too. Like anything there are good and bad SSDs, but overall, based on all the feedback I've seen, the consultants I've worked with, they're considered to be extremely reliable.
I don't know the brand of the SSD.
Its response time in the very beginning was perfect. During time it had been increasing to the value of about 1500 ms, and we decided to leave it and move to RAID.
It got "wear out" - was told to me by the sys/network admin, but its age was very short. Actually we measured 5K updates/sec on the instance at that time, and that was probably the reason for its crash.
Depends on the read\write ratio. Heavy write will degrade faster, were they MLC or SLC SSDs?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 31, 2016 at 3:41 pm
Perry Whittle (5/31/2016)
Igor Micev (5/31/2016)
Grant Fritchey (5/31/2016)
TheSQLGuru (5/31/2016)
Igor Micev (5/31/2016)
My experience with SSDs on a write-intensive instance was very bad. The SSDs went out for 2 months.What SSDs did you have? What was very bad about the experience? What does it mean "went out for 2 months"?
I'm curious too. Like anything there are good and bad SSDs, but overall, based on all the feedback I've seen, the consultants I've worked with, they're considered to be extremely reliable.
I don't know the brand of the SSD.
Its response time in the very beginning was perfect. During time it had been increasing to the value of about 1500 ms, and we decided to leave it and move to RAID.
It got "wear out" - was told to me by the sys/network admin, but its age was very short. Actually we measured 5K updates/sec on the instance at that time, and that was probably the reason for its crash.
Depends on the read\write ratio. Heavy write will degrade faster, were they MLC or SLC SSDs?
Probably SLC as its prise of $3000 for 128GB says. But I'm not sure for this information.
The write was intensive (5K wites/sec), that is the major reason for its degradation.
Igor Micev,My blog: www.igormicev.com
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply