How to calculate fill-factor percentage related to the size of a database?
I have rebuilt all indexes in my database setting fill-factor of 95 (5% free space) using a maintenance plan. After the reindex the database is almost doubled in size - reported free space is 42%.
How is calculated fill-factor related to the size of the database? Maybe something is wrong with reindex; what causes so much growth of size? Some database info after reindex:
Size (MB): 164 983.625 Data Space Used (KB): 82 907 896 Index Space Used (KB): 14 073 320 Space Available (KB): 71 879 024 Generated T-SQL for maintenance plan for one table: ALTER INDEX [Table1_Index1] ON [dbo].[Table1] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) Additional informations: Result of sp_spaceused 'dbo.BigTable' name rows reserved data index_size unused BigTable 58028080 72824296 KB 68393936 KB 4424000 KB 6360 KB What is a SQL server fill factor? what it is used for?
SQL server fill factor SQL Server Index Fill Factor is a percentage value to be filled data page with data in SQL Server. The default value is 0 in SQL Server Index Property with each index of Tables, it prevents 100% storage to be filled in each data page. The fill factor is the value that determines the percentage of space on each leaf-level page to be filled with data. In your case, what you see is correct. When you rebuild an index first the new index is built and only then the old index is dropped. And if you don't use SORT_IN_TEMPDB = ON the space that server needs to make a sort is allocated within your db Your db has now empty space, it's not space reserved to any object, it's just free space (space that was freed after temp objects needed to do sort were dropped) So your indexes are not empty on 42%, it's your DB that is empty on 42% and if you do ashrink of data file (I don't recommend it) your DB will occupy only 82Gb