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.
- Heap
- Clustered Index
- Non Clustered Index
- 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 eventually cause 10% growth of index and eventually database.
YourYou 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