3

So I've got a very frequently-run query on my SQL server instance that's generating a lot of wait time. On examining the Plan, I was pointed in the direction of a clustered index seek that's accountable for 93% of the cost of the whole operation.

Examining the clustered index, I discovered that while it has 0% fragmentation listed, it has a page fullness value of only 23%. From all the research I've done, I can't really find any indication of why you would want page fullness to be low, and am anticipating that I'll want to do an index reorganize operation to set the value to something more like 90%. (This table is very frequently read from and written to, and I am led to believe too high a page fullness value creates slowdown during write operations, hence why I don't set it to something like 99% or 100%.)

My question is this: Is there any reason I shouldn't reorganize the index and set the page fill factor to 90% or so? Any downside to doing that? I need this query, and the SQL text itself has already been optimized through Lync.

StuartLC
  • 104,537
  • 17
  • 209
  • 285
Biztalk Amateur
  • 53
  • 1
  • 1
  • 7
  • I'd say that there is an optimal fill factor for each index and if possible you should use that. The page fullness that emerges during normal operations has nothing to do with the optimal value. – usr May 15 '14 at 12:41
  • What's the size of that index in pages*pagesize? Maybe that index is too big to fit in memory, thus is always read from disk, thus index seek is done at I/O performance, not memory read performance. – Vesper May 15 '14 at 12:42
  • 1
    This will depend greatly on the choice of clustering key - if there is no chance of inserting into old pages (e.g. an incrementing IDENTITY) then a 100% Fill factor is good. If you are clustered e.g. by `Surname` then page splits could hurt. – StuartLC May 15 '14 at 12:42
  • Here's an article which gives a [high level overview](http://www.brentozar.com/archive/2013/04/five-things-about-fillfactor/) – StuartLC May 15 '14 at 12:55
  • Put more RAM in your server if possible :-) – Ben May 15 '14 at 13:22
  • 3
    The poor mans illustration is books on a shelf. If you only put books on the shelf that has space, then you don't need "wiggle" room on the earlier shelves. But if you alphabetize your books, and leave 20% space open on each shelf, then when you buy a book that starts with the letter "A", you can put that shelf on the top shelf without having to move every other book to the right and down. So the previous comment about the clustered index is spot on. Some tables are always having random named books put into them, thus the need for a low fill factor. – granadaCoder May 15 '14 at 13:57
  • I have never heard anyone recommend less than 60-70% on even the heaviest tables for writes. granadaCoder is right in the analogy, you only want space when you will need it. If you have an Identity(auto Increment) Primary key, then the Primary Key index can be 100% full, since its like alphabetizing the books before putting them on the shelves, then doing so alphabetically. If not, you want room, but one overfull page splits to 2 at around 50% each, so less than 50% just makes reads hard and has no benefit in most cases. 80-90% full is common – Ryan Feb 03 '16 at 19:17

0 Answers0