Home Forums SQL Server 2016 SQL Server 2016 - Development and T-SQL Performance improvement following a disable indexes, shrink, enable indexes - but why? RE: Performance improvement following a disable indexes, shrink, enable indexes - but why?

  • February 12, 2018 at 2:13 am

    #1979389

    pcd_au - Sunday, February 11, 2018 8:40 PM

    Performance improves significantly, but fragmentation levels are around the same.

    I'm at a loss to explain what is going on here, can anyone help me understand why this is occurring?

    Dropping and creating an index results in updated statistics (with FULLSCAN). that's the most common reason why index rebuilds improve performance.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass