Skip to main content
Grammar correction
Source Link
Shekar Kola
  • 2.4k
  • 2
  • 10
  • 24

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 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

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 eventually cause 10% growth of index eventually database.

Your 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

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
Source Link
Shekar Kola
  • 2.4k
  • 2
  • 10
  • 24

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 eventually cause 10% growth of index eventually database.

Your 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