|
|
One of the limitations of flash memory is that while it can be read or programmed a byte or a word at a time in a random access fashion just like regular RAM, it can only be erased a “block” at a time. This will set all bits in the block to 1 which is the default state for NAND memory.
Writing a byte in flash memory involves 2 steps: Program and Erase (P/E). The block is written to a new cell and the old block needs to be erased.
The programing can be done at cell level (setting it to the “0″ state) via a process called tunneling while the floating gate is being flooded with high voltage using the on-chip charge pumps.
Erasing can be done only on an entire block (resetting it to the “1″ state), thru high negative voltage pulling the electrons off the floating gate via process called quantum tunneling. Flash memory is divided in erase segments (often called blocks or sectors).
It’s time dissect the two main types of flash chips in order to understand why not all SSDs are created equal. What is after all the physical difference between SLC and MLC?
SLC stands for Single Level Cell and just like the name suggests can store one bit per NAND gate hence SLC cell has two states:
0 or 1 based on the charge of the NAND gate.
 SLC Reference Levels
MLC on the other hand stands for Multi-Level Cell and uses multiple voltage threshold levels in order to store 2 or even 3 bits (also called TLC – Triple-Level Cell) in the same NAND gate. this is done by coding 4 or even 8 states (in the case of 3 bit TLC) on the same gate so the MLC will typically one of the following states :
11, 10, 01, 00). The benefit over SLC is the increased capacity per chip (2 or 3 times more) but at the same time the voltage reference levels are a lot tighter which leads to more rapid degradation of the cell after a lot of P/E (Program/Erase) Cycles. Once the MLC NAND gate has degraded the reads are no longer predictable because the stored value overlaps reference levels. In this case the memory will report an error or if the controller supports it it will retire the cell and replace it with one from the reserve capacity.
 MLC Reference Levels (2bit cell) Typical number of write cycles is pretty solid around 100K for SLC and floats around 10K for MLC (different dies can have very different quality and will wear differently). This number is still high enough for a consumer lifecycle in the case of MLC if the entire memory is programmed 5 times daily for 5 years and runs uptu 50 years for SLC under the same usage.
| Type of flash cell |
SLC |
MLC 2bit |
TLC 3bit |
| Bits/ cell |
1 |
2 |
3 |
| States stored |
0 |
00 |
000 |
| 001 |
| 01 |
010 |
| 011 |
| 1 |
10 |
100 |
| 101 |
| 11 |
110 |
| 111 |
| Typical capacity /chip |
32GB |
64GB |
96GB |
| Endurance P/E cycles |
100K |
10K-30K |
<1K |
| Performance Over Time |
Constant |
Degrades |
Quickly Degrades |
| Application |
Enterprise |
Consumer |
Thumb drives,Camera cards |
In the case of MLC the program cycle take 2 or 3 times more than for SLC since the programming signal has to be a lot more precise to code 4 states in the space of 2. This leads to higher speed and increased number of IOPS (IO Operations Per Second) for SLC type of memory compared to MLC.
One of the main drawbacks of SSD has been reliability. Every NAND cell has a certain prescribed number of Program/Erase (P/E) cycles and as data is being written to disk, chances are it will remain unchanged for weeks or months. That means that the cells that are being used to store that data will have the same wear level (used P/E cycles) for the weeks or months that data was unchanged. This becomes a problem because the remaining free cells are going to be taxed even more and could reach their end of life and make the entire drive read only or even fail it completely.
I discovered this technology while I was trying to explain the degraded performance on my new OCZ Vertex 3 SSD drive. I ran a bunch of tests using SQLIO based on Jonathan Kehayias (Blog|Twitter) post about Parsing SQLIO Output to Excel Charts using Regex in PowerShell with a 6GB file and I got some good results. I started using the drive and installed a few VMs until 50% of the drive was full. At that point I kept running SQLIO and Crystal Disk Mark test only to see the performance sinking more and more.
Little did I know that OCZ Vertex 3 which is based on SandForce 2281 chipset implements an intelligent Static Data Rotation algorithm as part of Duraclass (Sandforce’s set of technologies to increase the reliability of the drive). This means that the SSD controller actively rotates static data from cells intensively used to other cells that were least used during idle periods to allow the drives wear leveling to work at it’s best. But what happens when you stress test the disk and you run the about 3 times the size of the drive worth of data in a couple hours while half of the drive is full. The Sandforce Duraclass algorithm will kick in and start moving data around even when the drive is not idle and the user will see a decrease in performance until the wear level is stabilized.
Essentially Static Data Rotation is there to make sure that you can use the drive for the MTTF prescribed by the manufacturer and prevents premature wear on the cells that store hot data.
There is an interesting post on the OCZ Technology Forum about this
UPDATE: Nitin Salgar (b|t) Has asked avery good question on Twitter after reading my post:
“Is Static Data Rotation in SSD a common phenomenon across all manufacturers?”
The answer is no, this is one of the strong selling points for the newer Sandforce SSD controllers that implement Duraclass. Newer Intel controllers have this technology as well but older ones do not have it. I would like to think that any Enterprise class controller has its own implementation of a Static Data Rotation algorithm.
It’s become a tradition in the SQL blogs to start a month long series on a certain topic and try to blog every day. Today I want to start my first series on SSD technologies. It has been over a year since I started speaking on this topic and as this is a hot topic there are new technologies that I feel are not well explained even on specialty blogs.
I will keep this as a master post and will add links to each of the posts as I publish them.
- Static Data Rotation
- What’s the difference between SLC and MLC?
- Program and Erase Cycle (P/E)
- Redundant Array of Independent Silicon Elements (RAISE)
- Wear Leveling
- CacheCade
- Bad Block Management
I will be presenting my session on “Optimizing SQL Server I/O with Solid State Drives” at SQL Saturday #119 in Chicago on Saturday May 19, 2012!
Since this is the 4th time I’m presenting this and I kept accumulating a lot of ideas related to Solid State Drives I decided to start a series of One SSD Technology a Day. Stay tuned!
Location is:
DeVry University – Addison Campus,
1221 North Swift Road, Addison, IL 60101-6106
View Larger Map
SQLSaturday is a training event for SQL Server professionals and those wanting to learn about SQL Server. Admittance to this event is free, all costs are covered by donations and sponsorships. Please register soon as seating is limited, and let friends and colleagues know about the event.
If you are attending please stop by and introduce yourself. Also if you are on Twitter don’t forget to use hashtag #sqlsat119 and see what others are doing.
Hope to see you there!
I’ve been meaning to write my blog post on last week’s SQL Saturday experience so finally I got to work so here it is:
 Bored Member After the previous weekend marked a record with 5 (five) SQL Saturday events all around the world on Apr 14 it was time for Wisconsin to show what they can do. And boy did they put on a great event. The organizing team did a fabulous job, helping 227 hungry minds learn about SQL Server from 33 speakers in 36 sessions organized on 6 tracks. They almost made it look easy.
I remember 2 years ago meeting the person responsible for this at SQL Saturday #31 in Chicago. Back then Jes Borland (b|t) had traveled 160 miles to volunteer at the event. She enjoyed it so much that she came back the next year as a speaker and a year after that with the help of her great friends from MADPASS , she put on an epic first time event! This goes to show a how important a single person can be in the SQL Family and anyone who thinks that they will never be able to raise to the level of some of the speakers has to learn a lesson from Jes. Every marathon starts with a first step (and she will run one in Kenosha the next week too ) and every little helps.
I left work Friday afternoon, after a pretty busy day with Vince (b|t) and we headed north to the land of beer and cheese. We got to Madison, checked in at our hotel and shortly after I was headed to the Speaker Dinner. The atmosphere was great, beer was good and the lasagna was too much for me to handle in one sitting. The highlight of the night was the paper airplane fight started by Aaron Lowe (b|t) and continued in part by me. We had a lot of fun unwary that above our head was a clothesline with underwear and shirts to complete the Italian experience.
The next day we woke up bright and early, had a frugal breakfast at the hotel and headed to Madison Area Technical College for the event, registered, grabbed some coffee and a bagel and after finding the speaker room we chose Mike Donnelly’s (b|t) SSIS: Figuring Out Configuring session, his first SQL Saturday presentation ever. He did a great job with a demo packed session that had a very good audience.
 Unicorns and gang signs w/ @StrateSQL Next we decide to take Erin Stellato’s (b|t) DBCC Commands: The Quick and the Dangerous. She did a great job condensing all DBCC commands in one session. I could not help not to drop the ” unicorns will die if you shrink the database” slogan and the room liked it. The same room hosted an extraordinary fun session on what happens when you think outside of the reporting box. Stacia Misner’s (b|t) (Way Too Much) Fun with Reporting Services was spiced up with a lot of humor with the help of her daughter Erika Bakse (b|t) . They started playing Words With Friends inside a SSRS report and then went under the covers to explain how it is done and mainly how to use Report Actions in SSRS to create interactions inside a report.
It was lunchtime and the menu included tasty brats, burgers and Cows of a Spot tables, the Wisconsin version of Birds of a feather. I sat at the Data Profiling table and had a great conversation with Ira Whiteside (b|t) and his lovely wife Theresa Whiteside. They where very kind to share with me and Vince some of the experience accumulated during the course of their vast career working with Data Profiling and Data Quality as well as some of the methods that were presented in Ira’s session that started right after lunch. It was a extremely interesting session titled Creating a Metadata Mart w/ SSIS – Data Governance and we learned a lot from it.
It was time for my session on Optimizing SQL Server I/O with Solid State Drives a session that seems to be very popular (same session was selected for SQL Saturday #119 in Chicago as well at Washington DC and Tampa before). I had some great feedback that I will try to use to improve the format and add some fresh content (whiteboard on TRIM, Wear Leveling and Bad Block Management). I had the honor to have in the audience Norm Kelm (b|t), Craig Purnell (b|t) and Matt Cherwin (t). We did an impromptu drawing for a Ted Krueger’s book (signed by Ted himself) and we stayed in the same room for the last session of the day with Sanil Mhatre (b|t)on Asynchronous programming with Service Broker . He did an excellent job and it was his first SQL Saturday presentation ever.
It was a great day and an epic event by MADPASS which made me look forward to the next SQL Saturday in Madison.
I am happy to announce that my session on “Optimizing SQL Server I/O with Solid State Drives” was selected for SQL Saturday #118 in Wisconsin’s capital, Madison on Saturday April 21, 2012!
Location is:
Madison Area Technical College – Truax Campus
3550 Anderson St, Madison, WI 53704
SQLSaturday is a training event for SQL Server professionals and those wanting to learn about SQL Server. Admittance to this event is free, all costs are covered by donations and sponsorships. Please register soon as seating is limited, and let friends and colleagues know about the event.
If you are attending please stop by and introduce yourself. Also if you are on Twitter don’t forget to use hashtag #sqlsat118 and see what others are doing.
Hope to see you there!
I am happy to announce that my session on “Optimizing SQL Server I/O with Solid State Drives ” was selected for SQL Saturday #110 in Tampa, FL on Saturday March 10, 2012!
Location is:
K-Force,
1001 East Palm Avenue,
Tampa, FL 33605
SQLSaturday is a training event for SQL Server professionals and those wanting to learn about SQL Server. Admittance to this event is free, all costs are covered by donations and sponsorships. Please register soon as seating is limited, and let friends and colleagues know about the event.
If you are attending please stop by and introduce yourself. Also if you are on Twitter don’t forget to use hashtag #sqlsat110 and see what others are doing.
Updated:
Due to a family emergency, I had to cancel this speaking engagement and luckily my slot was filled by another speaker.I hope to make it next year!
As some of you might be aware Paul Randal (blog | twitter) has just announced the launch of a new SQL Skills Community Mentoring program for a few individuals that took part in the SQL Skills Immersion Events. I’m deeply honored to be one of the select few that get access to invaluable advice from what I consider the best team in the SQL Server world.
Allow me to go back to a year ago (and boy what a great year 2011 was), when I could only dream of getting trained by Paul Randal and Kimberly Tripp, but my perseverance paid off and after multiple requests, me and my buddy Vince managed to convince the company to send us to the first week of Immersion training in May this year especially since it was in the western suburbs of Chicago. Very soon I realized that this is by far the best training I have ever received, period. The amount of content was overwhelming (as Brad put it “it’s like drinking from a proverbial fire hose”) but the presentation style was extremely engaging and well prepared. Paul and Kimberly don’t just tell you “this is what you should/shouldn’t do” they go beyond that and explain why and how and then they demo the whole concept. Just like a good teacher they can take a difficult topic and present it in a way that makes it easy for you to follow and absorb a lot of information.
On the fourth day of the IE 1 class I volunteered to present a slide deck. I was extremely embarrassed as I felt like I was not in the same league as the rest of the folks present, but everything went so well that it made me reconsider my thoughts. As a matter of fact it made me submit a couple sessions for SQL Saturday #82 in Indianapolis and this was the beginning of my public speaking adventure. A few days ago I actually presented the same slide deck from IE1 at SQL Saturday #96 in Washington, DC and the feedback was excellent.
Last month I continued my learning odyssey with the second week of training (IE2). Right away the fact that we attended IE1 made a huge difference and it helped us understand a lot better the subjects presented to us. This time it wasn’t just Paul and Kimberly but Jonathan and Joe have joined them adding their technical skills and expertise to the table. I learned so many new things that I have already used in my environment.
To conclude, these classes have helped me to understand significantly better how SQL Server works. Things like PLE, forwarding records, heap fragmentation, index tuning, page structure, SAN performance, waits and stats, extended events, NUMA architectures, MCM program are only a few of the subjects that were explained in great detail.
Now on the last binary day of the century all this becomes even better by having the unique chance to have someone like Joe Sack as Mentor and working with him to advance my career. It’s been an incredible year for me and I look forward to work with my Mentor Joe Sack and see what the next 6 months will bring !
Once more, thank you SQLskills. It is an honor to be in such good company.
This is the second time I’m writing a post in the “Meme Monday” series and this time it originates from Tom LaRock (blog | twitter). The topic is : What #SQLfamily means to me.
This weekend I participated in a #SQLfamily reunion in Washington, DC. Some might argue that this is called SQL Saturday #96 but I prefer to see it as a family reunion because this is how it felt. It started with a #SQLfamily dinner where we had a good time told stories (some would even make a good Agatha Christie book)… but I digress. We all sat around the table and felt like we are part of a family. I met Tom LaRock, Joey Dantoni, Karen Lopez, Andy Leonard, Brian Moran, Aaron Nelson, Matt Velic, Chris Skorlinski, Wayne Sheffield, Sebastian Meine and many others. I truly felt like a family reunion and it was.
On Saturday we continued, had fun presenting our sessions, learning new things, hanging out in the speaker room and talking about all kinds of things. Everybody contributed to making this #SQLfamily reunion an event to remember. Tom even offered to make coffee and but had no idea what to do with the extension cord. (…after the last power outage they had in New England you would think he should know better  )
Looking back I realize that every #SQLfamily event I have participated to has made me feel like this whether it was a SQL Saturday in Indianapolis or in Chicago or a user group meeting I never felt left out. I had my brothers and sisters and uncles and aunts and they made me feel like I am part of the #SQLfamily.
And the best part is that our #SQLfamily is growing every day. Every reunion we discover more people we can relate to even though we are not related (but in a way we are).
God bless our #SQLfamily!
John Sterrett blog|twitter asked a question about “How do I loop by rowcount in SSIS? I have a table with 5 million IDs and would like to loop to process 10K at a time. #sqlhelp”
This cannot be done natively in SSIS but you could create a stored procedure that uses the concept known as “poor man’s cursor” to split it in batches.
I included a template of code that I use to create smaller transactions that do not block normal users accessing the same tables
Let me know what you think.
/***************************************************************************************
* Name: Poor mans cursor example for batch processing
* Author: Luke Jian - luke(a.t)sqlpositive.com
* Creation Date: 11/02/11
* Purpose: Template for batch processing using Poor man's cursor
***************************************************************************************/
DECLARE @RowStart [INT]
DECLARE @RowEnd [INT]
DECLARE @RowToDo [INT]
DECLARE @RowDone [INT]
DECLARE @BatchSize [INT]
BEGIN
-- Load Users To Enable
IF ( OBJECT_ID('[TempDB]..[#PoorManCursor]') IS NOT NULL )
DROP TABLE [#PoorManCursor]
SELECT --DISTINCT
[ID] = IDENTITY ( [INT], 1, 1)
--.. SOME other rows
INTO [#PoorManCursor]
FROM dbo.BaseTables
--ORDER BY [Whatever column for batch order]
-- Add index to temp table
-- because table may easily exceed 10,000,000 rows
CREATE UNIQUE CLUSTERED INDEX [UCPoorManCursorID] ON [#PoorManCursor] ( [ID] )
-- Set up batching
SELECT @RowStart = 1,
@RowEnd = @BatchSize, --can use 10K
@RowToDo = MAX([ID]),
@RowDone = 0
FROM [#PoorManCursor]
PRINT CAST(@RowToDo AS [VARCHAR]) + ' records to be processed.'
-- Process batch
WHILE ( @RowStart <= @RowToDo )
BEGIN
BEGIN TRANSACTION
-- Load PoorManCursor_Tracking
INSERT INTO [dbo].[SomeTargetTable]
(
[col1],
[col2],
[col3],
[col4]
)
SELECT [col1],
[col2],
[col3],
[col4],
CURRENT_TIMESTAMP
FROM #PoorManCursor
WHERE [ID] >= @RowStart
AND [ID] <= @RowEnd
COMMIT TRANSACTION
PRINT CAST(@RowCount AS [VARCHAR])
+ ' records completed in this batch'
SET @RowDone = @RowDone + @RowCount
SET @RowStart = @RowStart + @BatchSize
SET @RowEnd = @RowEnd + @BatchSize
END
PRINT CAST(@RowDone AS [VARCHAR]) + ' total records processed.'
IF ( OBJECT_ID('[TempDB]..[#PoorManCursor]') IS NOT NULL )
DROP TABLE [#PoorManCursor]
END
|
|
