February 22, 2015 at 4:11 am
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
February 22, 2015 at 4:48 am
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://sqlmag.com/blog/what-best-value-fill-factor-index-fill-factor-and-performance-part-2
February 22, 2015 at 5:25 am
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply