|
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?
(comments are locked)
|
|
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. EDIT ---------- 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. 15s! Fifteen seconds! damn, I shouldnt have typed so much !! ;)
Oct 18 '10 at 08:56 AM
Fatherjack ♦♦
(comments are locked)
|
|
+1 to you, Blackhawk and Fatherjack, but I just wanted to add some points.
+1 for your answer. A page split is always a page split and will cause IO writes. And I want to clarify that using an ever increasing clustered index is not always the best, as always "it depends". In a OLTP solution with many inserts you may end up with hotspots on the last page. Read the following pages carefully before you choose the clustered index http://www.sql-server-performance.com/tips/clustered_indexes_p1.aspx
Oct 19 '10 at 12:28 AM
Håkan Winther
(comments are locked)
|
|
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. Index defragmentation and O/S level defrag are different. The big thing is to get non-fragmented pages into RAM. Again a proper FILLFACTOR will allow for a multitude of inserts prior to a split. The secret is to determine future growth against the randomness of the data over the row size. SSD devices may lead to new Design Best Practices where we run with 10-20% FILLFACTORS anticipating growth, paying the cost up front in extra storage, rather than re-writing the same blocks and paying for it in MTBF.
Oct 18 '10 at 11:32 AM
Blackhawk-17
To avoid O/S level defragmentation pre-size your files and don't shrink or grow them.
Oct 18 '10 at 11:35 AM
Blackhawk-17
@Mark this is the essence of the question - setting the right fill factor will prevent the indexes becoming fragmented so quickly on normal disks you then have to do defrags in order to maintain acceptable iops. @Weprin is asking if it matters as much on SSD as they effect of the fragmentation will take longer to evidence itself due to the performance you can get from the media.
Oct 18 '10 at 11:35 AM
Fatherjack ♦♦
@Blackhawk, this gets complicated fast doesn't it? So if you have an index that isn't sequentially growing, but changing in the same place, it would wear out that part of the SSD? Or, maybe TRIM (or a similar tech) helps prevent that somehow - I'm not sure.
Oct 18 '10 at 11:38 AM
Mark
@Mark - those would be most likely be updates, and theoretically, they wouldn't cause defragmentation (unless row sizes increased, etc.) so FILLFACTOR is not relevant to that discussion. Complicated? Yup - proactive maintenance/monitoring will become even more important if SSD tech is as wear-prone as early results seem to suggest.
Oct 18 '10 at 11:43 AM
Blackhawk-17
(comments are locked)
|
|
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. Good points; of course they took longer to type :)
Oct 18 '10 at 08:58 AM
Blackhawk-17
I liked both answers.
Oct 18 '10 at 10:06 AM
Grant Fritchey ♦♦
As with many businesses these days, nothing is written in stone. For now, we're on SSD. But in five years, who knows what we will use? And you are correct, our business continuity hardware is not using SSDs.
Oct 18 '10 at 12:43 PM
Weprin
(comments are locked)
|

