Index Fragmentation

• MMartin1

  One Orange Chip

  Points: 27826

  More actions

  March 27, 2025 at 9:03 pm

  #4568729

  In an environment where you are doing lots of inserts and with a uniqueIdentifier type, first consider that you are not using the 100% fill factor. From there you can defragment as you see fit but in my experience I have noticed if you process too many of these at once or rebuild at a low defragmentation value (~ 5%) , then the tables seems to create blockers to other sessions while the operation is in place. This can be a problem if you are a 24/7 site. I tend to go a little higher as my defrag threshold (still below the 30%) and update statistics instead if the defrag threshold is not met.

  ----------------------------------------------------

• MMartin1

  One Orange Chip

  Points: 27826

  More actions

  March 27, 2025 at 9:04 pm

  #4568730

  In an environment where you are doing lots of inserts and with a uniqueIdentifier type, first consider that you are not using the 100% fill factor. From there you can defragment as you see fit but in my experience I have noticed if you process too many of these at once or rebuild at a low defragmentation value (~ 5%) , then the tables seems to create blockers to other sessions while the operation is in place. This can be a problem if you are a 24/7 site. I tend to go a little higher as my defrag threshold (still below the 30%) and update statistics instead if the defrag threshold is not met.

  ----------------------------------------------------

• Jeff Moden

  SSC Guru

  Points: 1004558

  More actions

  March 31, 2025 at 1:31 am

  #4570469

  If you're going to start talking about Random GUIDs (uniqueIdentifier type not using NEWSEQUENTIALIUD()), then you absolutely MUST watch the following video, which proves that what most people think they know about Random GUIDs and Index Maintenance is totally wrong if they use anything like the old 5/30 method that MS finally took down back on 20 April 2021.  The bottom line is that you can insert 100,000 rows (chart for that after the Q'n'A) into a Random GUID clustered index for 10 hour per day with almost virtually no fragmentation, no page splits, and no index maintenance for nearly two months and that's just at the end of the first year!

  And, a lot of other myths about non-GUID based indexes are included, as well.

  https://www.youtube.com/watch?v=rvZwMNJxqVo

  If you're listening with a headset, be aware that they added 3 advertisements at the 15:00, 30:15, and 45:25. The advertisements are important to help support this event but I wanted to let you know that they're sudden and they're loud! They WILL lift your headset!

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)