Table size after a reindex

• terry999

  SSCertifiable

  Points: 5036

  More actions

  February 22, 2015 at 4:11 am

  #297860

  Hi

  I'm trying to understand what is happening to one large table.

  In a DB SQL 2008R2, I'm trying to track a rapidly increasing DB size. It's due to one table recently added.

  Despite the table's no rows increasing its size reduces after a scheduled re-index.

  I've recorded the space used by this table by recording EXEC sp_spaceused 'tableName'

  [font="Courier New"]

  ---

  NoRows reserved data index_size unused

  128864512300384 KB 2290928 KB9432 KB24 KB AFTER reindex

  128864515406232 KB 5366184 KB39280 KB768 KBBEFORE reindex

  [/font]

  N.B. The only thing I'm aware of happening in the time period is a reindex as part of scheduled task. I could be missing something else happening.

  The table has only one Index the PK which is clustered, No Fill factor is specified. The Server Default fill factor =0.

  I read fillfactor=0=100 Will always try and fill the pages so space used will be minimised?

  After running the reindex the index fragmentation is v.low. I've not recorded the fragmentation before reindex.

  I can see the Data is not added in Clustered index order.

  I'm confused as to what is happening?

  Thanks Terry

• e4d4

  SSCertifiable

  Points: 5777

  More actions

  February 22, 2015 at 4:48 am

  #1779138

  Looks like this table is heavily fragmented before reindex. The index size before is almost thrice as after. Fill Factor is applied only during rebuild, not when insert/delete occur. You could consider lower fill factor for this table

  http://www.sqlskills.com/blogs/kimberly/database-maintenance-best-practices-part-ii-setting-fillfactor/

  http://sqlmag.com/blog/what-best-value-fill-factor-index-fill-factor-and-performance-part-2

• terry999

  SSCertifiable

  Points: 5036

  More actions

  February 22, 2015 at 5:25 am

  #1779142

  Fill Factor is applied only during rebuild, not when insert/delete occur.

  Thank you that explains it then. Although I only measured size before and after. It must be heavily fragmented.

  From the article you mentioned

  Some indexes are prone to more fragmentation than others. For example, indexes with a high-order element (the first column of the index) that is NOT in ever-increasing order.

  The index on this table does indeed have a

  high-order element