27.11.13

Wasted Space Per Database For Fill Factor: Quick Tip

As I keep looking for answers on Fill Factor... I keep finding interesting Metrics to look into. Looking my own stuff, I've found 300GB of space. That could be huge. Just simple things to think about. My previous post explains some things I've learned about Fill Factors, pros vs cons. If you have time, what's your highest number? What's your lost space as a whole?

select db.name as DatabaseName, SUM(a.total_pages*8/1024)as SizeInMB, SUM(((a.total_pages*fill_factor/100)-a.total_pages)*8/1024)*(-1) as SpaceInMBLostToFillFactor
from sys.dm_db_index_usage_stats stats
inner join sys.tables as tbl on stats.object_id = tbl.object_id
inner join sys.schemas as sch on tbl.schema_id = sch.schema_id
inner join sys.databases as db on stats.database_id = db.database_id
inner join sys.indexes as idx on stats.object_id = idx.object_id
and stats.index_id = idx.index_id
inner join sys.partitions as part on stats.object_id = part.object_id
and stats.index_id = part.index_id
INNER JOIN sys.allocation_units a ON part.partition_id = a.container_id
where fill_factor != 0
and fill_factor != 100
and db.name = DB_NAME()
group by db.name



2 comments:

  1. Alexander1/12/13 18:57

    http://technet.microsoft.com/en-us/library/ms188388.aspx
    An explicit FILLFACTOR setting applies only when the index is first created or rebuilt. The Database Engine does not dynamically keep the specified percentage of empty space in the pages.

    ReplyDelete
    Replies
    1. While true, I do not have a simple way of defining where the pages with fill factor end. If fragmentation is being kept in check, I'm going to guess that you're rebuilding your indexes, at least on the tables that have higher activity, once a week or even day. This won't be 100% accurate, but still fairly close.

      Delete

All opinions welcome! Please comment with any changes thoughts or discussion points.