August 26, 2011 at 1:27 pm
Greetings all:
I am having a disagreement with a coworker who believes that reindexing is not necessary on a database that resides on solid state disks. I find this silly, but am having difficultly backing up my position. What do y'all think? Should indexes be rebuilt on a database that is only on solid state disks? when i look at the fragmentation for said database all of the indexes are greater than 90% fragmented which is far to much for my tastes. (A mix of clustered and nonclustered indexes). How can it be proven that indexes need/do not need to be rebuilt on SSD's?
Thanks!
August 26, 2011 at 1:30 pm
Absolutely yes.
Not because of the fragmentation, because of the wasted space from page splits. If your table has split and split and split and your pages are on average 60% full, that means you're wasting 40% of the very expensive SSD. Not a good ROI.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 29, 2011 at 11:08 am
+1 on the page split/fill factor optimization.
If you really want to argue with your co-worker about fragmentation on SSD's, use SQLIO and run tests on sequential vs. random. My own testing reveals that Samsung SSD's, at least, show significantly higher sequential throughput than random throughput.
Now, SSD sequential throughout/IOPS is merely significantly higher than SAS 15k sequential throughput, and SSD random throughput/IOPS is dramatically higher than SAS 15k random throughpout/IOPS, but there's still some benefit to be had.
August 29, 2011 at 11:14 am
Even is the index data is completely cached in memory (so that the disk is not involved at all) there is still a benefit because it uses less memory and it takes less CPU resource to access the data.
September 1, 2011 at 9:00 am
Gail's right concerning space, however there's a lot of views that if most of your io occurs in cache then fragmentation on disk doesn't make any difference, especially as in most disk controller systems io is batched so fragmentation would not make any difference - there's also the consideration that if the table/index fragments very quickly then leaving it fragmented may actually improve things as it will probably stabilise at some point. I also believe some SSDs handle their io very differently ( no spinning head ) - there's also the point that a rebuild will cause a lot of deletes/writes and sometimes the ssds degrade for a while or totally. Tony Rogerson is/has been doing a lot of work with SSDs and we have several terabytes of solid state memory currently under test.
http://sqlblogcasts.com/blogs/tonyrogerson/
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
September 1, 2011 at 11:48 am
+1 on Gail's recommendation.
+1 for anyone else that said "test it" because one simple test is worth a thousand "expert" opinions. If you need help created a highly fragmented table (clustered index) with a lot of page splits, see the test table setup in the following aritcle...
http://www.sqlservercentral.com/articles/T-SQL/68467/
--Jeff Moden
Change is inevitable... Change for the better is not.
September 5, 2011 at 4:42 am
so how many posters here are using SSDs or have tested sql server with SSDs or solid state memory cards ? Just interested - not having a go - because it's possibly going to be an important area and although I use SSDs in my (old) laptop and I have raided SSDs in my video editing PC with great effect my tests using SSDs with SQL Server haven't proved quite so clear cut, and our testing using solid state pci cards hasn't quite produced what was expected ( so far )
So I'm fishing to see what others have found/are using and how we might consider Solid state for storage vs just adding more memory to the server now most manufacturers will now support 1TB in a basic server.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
September 6, 2011 at 7:42 am
I have; SSD's and (D)RAM serve different purposes; there's some overlap, but not 100%.
So far, SSD's are used in a very limited role as an experiment; RAID1, 10, and 5 sets of them as local storage for highly volatile databases (such as tempdb) and their logs.
Lessons learned:
Align partitions and logical drives carefully.
Buy the most advanced SATA or SAS controller your machine can handle; if it's not current generation, buy a newer machine.
Study, very carefully, the peculiarities of your particular SSD setup, in particular with SQLIO to spot particular trouble spots.
What's the erase block size?
How do different RAID levels change things?
Do not rely on theory here; do actual testing.
How do different RAID stripe sizes change things?
SSD theory is better here; but test anyway, there are subtle differences in reality that may matter to your workload.
Advanced: for each RAID stripe size, how do various NTFS cluster sizes change things?
Do your own benchmarking; you may (or may not) be surprised at how conventional wisdom and RAID write penalty mathematics are not perfectly reflected in the results you get with SSD's and modern controllers. You may also find that your controllers have some very serious hard limits for specific tasks, and are serious bottlenecks for SSD's (or even your SAS disks).
Beware of Sandforce compression; SQLIO writes only highly compressible data, which may not replicate your SQL Server workload at all. In this case, IOMeter beta with random data is required (see below).
IOMeter is generally used as a different kind of testing tool, used to get an overall picture; knowing that for your "estimated" average workload, SSD's are X times faster is nice, but SQLIO scripts (or very tedious use of IOMeter with only one block size and type of transfer at a time) will show you that your particular setup is worse with SSD's on random 64KB reads than it was with SAS disks, even if all other types are better; from this detailed information, you can complain to your supplier and perhaps get a driver or firmware update.
September 6, 2011 at 8:11 am
Another 0.02 cents (not a typeau).
I don't have SSD in prod nor do I have tested it.
The fact is afaik MS never spoke about disk speed in their fragmentation recommendations.
The problem with fragmentation is that MORE page need to be accessed to return the same data (along with more space, cpu, ram). More pages is more pages no matter the hd speed & type.
Now granted there are interesting questions to answer here. Especially the "extra, excessive" writes of rebuild and how much it shortens the lifespan of the ssd and what's the real gain in YOUR environement to reduce fragmentation on that <set of> table.
What I would ask myself here is how much gain for those x queries I'm seeing after I've eliminated fragmentation on that table and does that gain justify making any further testings & keeping doing the current defrag strategy.
Doing something just because someone said is good practice is not a very wise thing to do.
Now I would definitely keep defragging but maybe I'd start monthly and then weekly for the tables where I really see a boost. I would choose this because of the hit on the lifespan of the array.
Now the ideal scenario would be a cost analysis of each part of the server vs use & lifespan. But I wouldn't have time here nor reason to justify a budget on this to go down that route. Maybe it's different for you if you have a 1M$ SSD array.
September 11, 2011 at 7:24 pm
I agree with these experts.. It's needed.
Test it , gather the results and prove..
Thank You,
Best Regards,
SQLBuddy
September 12, 2011 at 6:10 am
Here is a blog post from Jonathan Kehayias with some interesting testing: http://sqlskills.com/blogs/jonathan/post/Does-Index-Fragmentation-Matter-with-SSDe28099s.aspx
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 13, 2011 at 8:28 am
My question is how often should one reorganize indexes? daily, weekly or monthly or just on demand?
September 13, 2011 at 8:30 am
Ninja's_RGR'us (9/6/2011)
Another 0.02 cents (not a typeau).I don't have SSD in prod nor do I have tested it.
The fact is afaik MS never spoke about disk speed in their fragmentation recommendations.
The problem with fragmentation is that MORE page need to be accessed to return the same data (along with more space, cpu, ram). More pages is more pages no matter the hd speed & type.
Now granted there are interesting questions to answer here. Especially the "extra, excessive" writes of rebuild and how much it shortens the lifespan of the ssd and what's the real gain in YOUR environement to reduce fragmentation on that <set of> table.
What I would ask myself here is how much gain for those x queries I'm seeing after I've eliminated fragmentation on that table and does that gain justify making any further testings & keeping doing the current defrag strategy.
Doing something just because someone said is good practice is not a very wise thing to do.
Now I would definitely keep defragging but maybe I'd start monthly and then weekly for the tables where I really see a boost. I would choose this because of the hit on the lifespan of the array.
Now the ideal scenario would be a cost analysis of each part of the server vs use & lifespan. But I wouldn't have time here nor reason to justify a budget on this to go down that route. Maybe it's different for you if you have a 1M$ SSD array.
September 13, 2011 at 4:57 pm
Generally , it is recommended to do the reorgs daily after the nightly backups along with update stats.
Do the Rebuilds once in a week. If you have too big databases then you need to split these maintenance operations based on their sizes and doing it for few databases at a time and so on ..
Use the excellent scripts from http://ola.hallengren.com/
Thank You,
Best Regards,
SQLBuddy
September 13, 2011 at 5:04 pm
TheSQLGuru (9/12/2011)
Here is a blog post from Jonathan Kehayias with some interesting testing: http://sqlskills.com/blogs/jonathan/post/Does-Index-Fragmentation-Matter-with-SSDe28099s.aspx
Excellent reference - same one I would have used had I not seen your post first.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply