SSD and Internal SQL Server Tuning Strategieshttp://sqlblog.com/blogs/joe_chang/archive/2013/09/09/ssd-and-internal-sql-server-tuning-strategies.aspxMicrosoft does not like to make meaningful tuning parameters in SQL Server. In part, I understand this because‌ in the past I have seen really bad settings being used in other products that do expose more tuning parameters based on seriously mistakenenCommunityServer 2.1 SP2 (Build: 61129.1)re: SSD and Internal SQL Server Tuning Strategieshttp://sqlblog.com/blogs/joe_chang/archive/2013/09/09/ssd-and-internal-sql-server-tuning-strategies.aspx#50928Tue, 10 Sep 2013 03:25:19 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:50928Theron Knapp<p>By entire device, do you mean the proposed SLC partition? &nbsp;Wouldn't the SLC mode reduce the storage by half and require the MLC data to be read into cache first?</p>
re: SSD and Internal SQL Server Tuning Strategieshttp://sqlblog.com/blogs/joe_chang/archive/2013/09/09/ssd-and-internal-sql-server-tuning-strategies.aspx#50935Tue, 10 Sep 2013 14:54:05 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:50935jchang<p>Why are you so locked-in on capacity?</p>
<p>Do you not know from HDD days that by the time you get performance correct, capacity is a non-issue.</p>
<p>The storage vendor idiots talk capacity, and that is why if you let a storage vendor do your configuration, you end up with really crappy performance, especially after spending $1M++</p>
<p>Consider two options: 1) buy MLC SSD at $3K/TB or 2) SLC at $6K/TB. Suppose your workload is such that the MLC write endurance would put the storage system at risk after 1 year of operation.</p>
<p>Do you want to change out the storage after 1 yr with MLC, or do you opt for the 2X cost (or more) of SLC?</p>
<p>Now consider that the majority of writes come from tempdb. With the split MLC/SLC option, we can operate as MLC for low write data, and SLC for high write SLC.</p>
<p>lets suppose the storage system consists of 64 SSDs (plus additional hot-spares) each rated at 256GB in MLC, 128GB SLC or some mixed combination</p>
<p>such that 1*(MLC) + 2*(SLC) = 256GB</p>
<p>Suppose we elect for 192GB of each device to be MLC leaving 64GB remaining MLC capacity to be configured as 32GB SLC. (The device itself may elect some SLC area for internal operation?)</p>
<p>There are 8 RAID Groups in 7+1 RAID5, the MLC capacity is 8x7x192 = 10.7TB</p>
<p>and the SLC capacity is 8x7x32 = 1.79TB. (1TB = 1000GB for simplicity)</p>
<p>So why is there a matter of first reading MLC data to cache when we have not put any data on the device yet?</p>
<p>Note that I explicitly do not want the alternative configuration nitwits feel compelled to suggest: that is 48 x 256GB MLC SSD and 16 x 128GB SLC SSD. This is a database! When we access data, we want to hit all 64 SSDs simultaneously, when we access tempdb, we want all 64 SSDs to be contributing. At the end of the day, we might find that 75% of IO went to data files, and 25% to temp, but at any given point in time, from a single DW query, it is either all data or all temp.</p>
re: SSD and Internal SQL Server Tuning Strategieshttp://sqlblog.com/blogs/joe_chang/archive/2013/09/09/ssd-and-internal-sql-server-tuning-strategies.aspx#50940Tue, 10 Sep 2013 19:00:21 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:50940jchang<p>My apologies for being snappy. Every time I hear capacity, I get chills because on so very many occasions the storage vendor told my client to buy on capacity, and performance was just a disaster that I automatically recoil on hearing capacity.</p>
<p>Oh, never mind. I see you are talking about the very end.</p>
<p>My bad again. </p>
<p>After years of use, it is possible the MLC SSD is near end-of-life in terms of write endurance. For us DW types, being smart, we could use these EOL MLC SSDs for as archival storage. So we will wipe it clean, then do one last write for the archive data.</p>
<p>The other possibility that I mentioned in the post is to wipe it clean, convert it to SLC mode - at half capacity, then continue to use it. So there are two options depending on what you need. Note, just because the NAND cannot provide quality resolution of 4 voltage levels for MLC, it can still resolve 2 voltage levels for SLC operation.</p>
<p>There is no talk of an online op. Backup the DB or file, filegroup, restore to new device, then reuse the EOL MLC SSD.</p><p>ps- ordinary MLC, say for&nbsp;example a 128GB SSD&nbsp;rated for 75TB write endurance, ie, about 600 cycles.&nbsp;You&nbsp;could use the tool provided by the SSD vendors to read the wear info.&nbsp;(Oh yeah, we need server/RAID controller vendors to enable us to read SSD info through the RAID controller). Do you replace the SSD at 75% or 90% or 100% of rated life? I don't know. It might be if there were some tool that could&nbsp;check the actual voltage/charge accuracy of the NAND cells to assess replacement recommendation?</p>re: SSD and Internal SQL Server Tuning Strategieshttp://sqlblog.com/blogs/joe_chang/archive/2013/09/09/ssd-and-internal-sql-server-tuning-strategies.aspx#50941Tue, 10 Sep 2013 23:47:30 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:50941Theron Knapp<p>Thanks for the clarification. &nbsp;Yes, I only misunderstood the end and was not trying to counter the benefit of performance over capacity. &nbsp;:-)</p>
re: SSD and Internal SQL Server Tuning Strategieshttp://sqlblog.com/blogs/joe_chang/archive/2013/09/09/ssd-and-internal-sql-server-tuning-strategies.aspx#50942Wed, 11 Sep 2013 01:37:08 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:50942Manson<p>Great idea on read/write, IO tier/partition on storage</p>
re: SSD and Internal SQL Server Tuning Strategieshttp://sqlblog.com/blogs/joe_chang/archive/2013/09/09/ssd-and-internal-sql-server-tuning-strategies.aspx#50947Wed, 11 Sep 2013 09:51:48 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:50947byron f<p>very interesting post. &nbsp;i particularly like the idea (in your second comment) of sending MLC to the data warehouse for one last wipe/write cycle...very clever.</p>
<p>do you have a link/citation/more information for this:</p>
<p>&quot;Samsung described a trick for MLC, allowing certain internal operations to using a portion as SLC! Recall that the NAND storage cells are just that, suitable for either SLC or MLC. Something in the logic portion of the NAND chip must understand SLC or MLC voltage levels. Apparently there is little overhead to add SLC logic to a MLC chip?&quot;</p>
re: SSD and Internal SQL Server Tuning Strategieshttp://sqlblog.com/blogs/joe_chang/archive/2013/09/09/ssd-and-internal-sql-server-tuning-strategies.aspx#50949Wed, 11 Sep 2013 13:57:51 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:50949jchang<p>Start here, Anandtech </p>
<p>New Elements to Samsung SSDs: The MEX Controller, Turbo Write and NVMe</p>
<p><a rel="nofollow" target="_new" href="http://www.anandtech.com/show/7152/new-elements-to-samsung-ssds-the-mex-controller-turbo-write-and-nvme">http://www.anandtech.com/show/7152/new-elements-to-samsung-ssds-the-mex-controller-turbo-write-and-nvme</a></p>
<p>Anandtech has several more Samsung tech articles too</p>
re: SSD and Internal SQL Server Tuning Strategieshttp://sqlblog.com/blogs/joe_chang/archive/2013/09/09/ssd-and-internal-sql-server-tuning-strategies.aspx#50986Fri, 13 Sep 2013 15:06:32 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:50986adopilot<p>Does that mean that We shoud wait more time before start building our infrastructure on SSDs for small offices (datacenters). &nbsp;</p>
<p>By the way, Great tex and explanation</p>
re: SSD and Internal SQL Server Tuning Strategieshttp://sqlblog.com/blogs/joe_chang/archive/2013/09/09/ssd-and-internal-sql-server-tuning-strategies.aspx#50988Fri, 13 Sep 2013 19:37:44 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:50988jchang<p>adopilot: Let me expand the general concept of your question to both software and hardware, because I do not want people to expand what I say. </p>
<p>On software - OS, I was never a fan of wait for sp1. Sure there might issues in brand new major releases, but software is very complex and operator error is a bigger source of problems. So my view is the IT pro needs to start evaluating at RTM, but its ok if deployment is around sp1. I raise this because people slant toward not bothering to learn until after sp1.</p>
<p>On SQL Server specifically, the new features in each major release are hugely valuable, more so on the DW/BI side. So my view is jump on SQL Server RTM or even CTP for DW. Of course MS is saying DW is now a critical enterprise resource, which is taken to mean that enterprises should apply stupid "high availability + reliability" principles - typically this means putting the DW on the big SAN controlled not by the DBA. Personnaly I evaluated such principles to be high CYA rather than HA.</p>
<p>But it is hard to argue with MS or corporate IT, so I say get a private DW with direct-attach storage and starting learning right away.</p>
<p>Now to hardware. I think there is something to waiting a few months after brand new hardware comes out. Occasionally there are PITA firmware issues. I am more familiar with large scale. I fight with corporate IT all the time. There gut principle from having been burn so very many times and more so, is standardization. Pick a mature product, test it, and stick with it for several years.</p>
<p>My preference is rolling acquisitions. If product life-cycle 4 years. </p>
<p>Pick a standard each year, replacing 25% of old equipment every year.</p>
<p>This way you don't miss out on new technology by more than 1 year, yet their is still some standardization.</p>
<p>One other thing: in the old days, people talked about investing in computer/storage hardware. Back then HW was so expensive it had to amortized over 10 years. You were not investing in HW, you were depreciating it! Technically, the value of hardware depreciates 40% per year per Moore's law, in that you can get new HW 40% at approx. the same cost, more or less.</p>
<p>So I treat HW as consumable/disposable. Use it, then lose it. HW is a great consumable - like food and fuel, but a crappy investment.</p>
<p>I think you should go ahead evaluate how SSD should be used. Don't be afraid to experiment, don't be afraid to fail - learning is more valuable - assuming you try to keep the scale of failures small.</p>
<p>Hope this helps.</p>