So I've been thinking about using FILLFACTOR when the storage is entirely on SSD drives. Is the cost-vs-benefit worth it to have a 70%-90% fillfactor to prevent fragmentation, when seek times are <0.1ms?
Perhaps it makes sense to keep FILLFACTOR for clustered indexes, but what about non-clustered indexes? My coworker firmly believes that the extra space used for this option is well worth it, even on SSDs.
The DB in question is used for OLTP and has 70/30 read/write ratio. What are your thoughts, SSC?
asked Oct 18 '10 at 08:34 AM in Default
Seeks are an expensive operation, relatively speaking, regardless of the medium. OLTP is already requiring seeks to perform it's job - why introduce easily avoided ones? Why have data shifted around due to page splits unnecessarily?
I would still tend to work with a FILLFACTOR.
Only testing can truly determine if it is of a concern in your environment.
---------- Another factor to consider in this is that the pages from disk are brought into RAM where they would still be fragmented and you continue adding unnecessary resource utilization.
I would say that best practice is best practice.
Can you guarantee that the database will always be on SSD? Does your business continuity hardware have SSD too?
My 2c says build it like its a normal database so use all settings as normal. There are plenty of examples of programmers getting sloppy as CPU performance sped up and then getting caught out as the needs of the system showed up their work when the system was even more critical to the business. Dont let your database settings show you up like that.
If you have SSD then the system must be expected to be high end iops so any saving is a saving that other transactions can use.
+1 to you, Blackhawk and Fatherjack, but I just wanted to add some points.
answered Oct 18 '10 at 11:53 AM
Matt Whitfield ♦♦
edit-> This is more about using defragging tools in general than FILLFACTOR per se, but to an extent, that's what FILLFACTOR is about.
Fatherjack and Blackhawk might be right, but there is one disadvantage of defragging an SSD - it can shorten the disk's life. So maybe if performance is this drive's most important function and it proves to help the performance, then you might go for it.
But, maybe more importantly, Intel does not recommend using defrag tools.
Also, are you using TRIM? If you can use TRIM, the gains may not be worth it according to this site.