Return to Revisions

How is calculated fill-factor related to the size of the database?

This is due to the fact that the database is accumulated value of following and all their size counted/stored in pages.

1. Heap
2. Clustered Index
3. Non Clustered Index
4. Un-allocated space

In the case fill factor set other than default (0 or 100), every page had to leave the empty space. i.e. a fill factor value 90% lead every page to leave 10% of free space (which already occupied in disk) this cause 10% growth of index and eventually database.

You can get quick details from each table using sp_spaceused 'dbo.TableName' or use following query to get details for all tables:

select   DB_NAME () AS DBName
        --,OBJECT_NAME (t.object_id) as ObjectName
        --,I.name as IndexName
        ,i.type_desc as UsageType
        ,SUM( (t.lob_used_page_count  * 8.00) / 1024 ) as LOB_Used_Mb
        ,SUM( (t.used_page_count * 8.00) / 1024 ) - SUM( (t.lob_used_page_count  * 8.00) / 1024 ) as Row_Usage_Mb
        ,SUM( (t.used_page_count * 8.00) / 1024 ) as Total_Used_Mb
        ,sum( (t.reserved_page_count * 8.00) / 1024) as Total_Reserved_Mb
        ,sum( ((reserved_page_count - used_page_count) * 8.00) /1024 ) as UnUsed_Mb
from sys.dm_db_partition_stats as T
        join sys.indexes as I on T.object_id = I.object_id and T.index_id = I.index_id
GROUP BY i.type_desc--, OBJECT_NAME (t.object_id), I.name
GO