SSD and SQL Server Fragmentation Impact

By: Chad Boyd | Read Comments | Print Chad is an Architect, Administrator and Developer with technologies such as SQL Server, .NET, and Windows Server. Related Tips: More

In the last post on Solid State and it's impact on SQL Server operations, we looked at a variety of different IO patterns and sizes on multiple systems to see where and when SSD would help out and when it wouldn't. If you read the post and analyzed the data, you could clearly see the huge gains Solid State provides with random read IO over traditional spindles - writing and large sequential reads seem to still favor traditional spindles. So, assuming you are in a system that performs lots of random read IO, SSDs will provide you tons of benefits, and will be much more resilient to the impact of fragmentation on the system (since fragmentation leads to heavier random io as a general rule). Since we recently had a series about fragmentation, and in that series we had a post showing the impact of fragmentation in different types of IO and patterns, let's see how those same tests are impacted with a SSD vs. a traditional drive.

I re-ran the same exact tests that I had run previously to test the different levels of fragmentation and their impact on types of and patterns of IO and then folded them into the same spreadsheet I posted previously alongside my desktop results. See my prior SSD post for specs on my desktop and laptop machines, and see this post in the fragmentation series for the original sheet with fragmentation impact results on my desktop only.

Given what we know about SSD and my laptop, we'd expect to see the laptop be much more resilient to fragmentation in general, outperform the desktop for cold-cache random IO read operations, and likely perform better comparatively in single-threaded operations vs. multi-threaded operations (since my desktop is a quad-core xeon and my laptop is a dual-core centrino). Additionally, we don't expect the SSD to out-perform my desktop in any warm-cache operations, since, well, they'd never touch the drive in either scenario (and again, my desktop has 16gb of cache, and my laptop has 4gb of cache).

I've attached the spreadsheet in it's entirety to the post, so you can take a peak at the total results if you like, it's actually quite interesting. As expected, the SSD is much, much, much more resilient to fragmentation impact vs. traditional spindles - for heavily fragmented data, the traditional spindle system degraded from 300% up to 2,500%, whereas the SSD system for the same tests ranged in degradation of 10% up to 580%. My laptop/SSD system outperformed my desktop by as much as 300% under fragmented conditions. In nearly all cold-cache tests the SSD system destroyed the traditional spindle system with the few exceptions coming on large scans of contiguous data (which would take advantage of serialized large sequential IOs with the read-ahead manager), which we already know traditional spindles are still better at.

I've attached the full spreadsheet with detailed results, enjoy!

Chad Boyd ~~~ This posting is provided "AS IS" with no warranties, and confers no rights. Use of any included script samples are subject to the terms specified at http://www.mssqltips.com/disclaimer.asp and http://www.mssqltips.com/copyright.asp.

Share:

Share

Tweet

Free SQL Server Learning Building Blocks for Your SQL Server Career Database development best practices What Are You Waiting For?

Comments and Feedback:

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code