locked
index Fragmentation increase with in hour for some tables. RRS feed

  • Question

  • User779033375 posted

    Hello Team,

    For few tables index fragmentation increase to 95% with in hour after index rebuild.

    Can you please suggest.

    Monday, May 3, 2021 9:02 AM

Answers

  • User-939850651 posted

    Hi saffy,

    For few tables index fragmentation increase to 95% with in hour after index rebuild.

    Have you turned on auto_shrink?

    The data file shrinkage leads to the complete fragmentation of the index, and the efficiency of the index is greatly reduced, which seriously affects the performance. And it also generates a lot of I/O operations, consumes a lot of CPU resources, and will cause performance degradation.

    Here is a good article: Why you should not shrink your data files

    If I misunderstood anything, could you provide more details?

    Best regards,

    Xudong Peng

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, May 4, 2021 7:03 AM

All replies

  • User-939850651 posted

    Hi saffy,

    For few tables index fragmentation increase to 95% with in hour after index rebuild.

    Have you turned on auto_shrink?

    The data file shrinkage leads to the complete fragmentation of the index, and the efficiency of the index is greatly reduced, which seriously affects the performance. And it also generates a lot of I/O operations, consumes a lot of CPU resources, and will cause performance degradation.

    Here is a good article: Why you should not shrink your data files

    If I misunderstood anything, could you provide more details?

    Best regards,

    Xudong Peng

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, May 4, 2021 7:03 AM
  • User779033375 posted

    Auto shrink is off. Any other possibilities?

    Thursday, May 6, 2021 7:51 AM