5

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
Improve this question
7
  • What command did you use to get those DB size numbers? Different commands measure different things. Please edit the information directly into the question.
    – Michael Green
    Commented Oct 29, 2019 at 10:53
  • 1
    Do you have a large table with a clustered index? About 120% of that space will be needed during the clustered index rebuild, which will become free space after the operation. That would explain the increase in both database size and freespace after the operation.
    – Dan Guzman
    Commented Oct 29, 2019 at 10:59
  • I used SQL Management Studio to read size numbers. One table is very large, about 75% size of database. All tables have clustered PK indexes and many non-clustered.
    – Sylwester Zarębski
    Commented Oct 29, 2019 at 11:11
  • Do you have similar database info before reindex? Echoing what @DanGuzman said, when a new index structure is created, disk space for both the old (source) and new (target) structures is required in their appropriate files and filegroups. The old structure is not deallocated until the index creation transaction commits. Once deallocated it shows up as free space.
    – SqlWorldWide
    Commented Oct 29, 2019 at 12:00
  • Table size numbers after database shrink is essentially the same (differences by few MB).
    – Sylwester Zarębski
    Commented Oct 29, 2019 at 12:26

3 Answers 3

Reset to default
6

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

Improve this answer
1
  • After excluding rebuild of big table index, there are no such large growth. Thanks all for helping diagnose this issue.
    – Sylwester Zarębski
    Commented Oct 29, 2019 at 20:41
2

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
Improve this answer
2
  • I've appended additional information about big table. Index do not look like it should be grown to dozens of gigabytes, but maybe i'm reading it wrong.
    – Sylwester Zarębski
    Commented Oct 29, 2019 at 11:45
  • You probably got to check with the size that were noted before fill-factor changes there must be impact with fill factor, if the variance is not significant. Consider sepupic’s answer and this as other causes.
    – Shekar Kola
    Commented Oct 29, 2019 at 18:52
1

I have rebuilt all indexes in my database setting fill-factor of 95 (5% free space)

This is almost always a bad idea.

How many of those indexes use sequential primary keys? If any of them do, you are just wasting disk space.

You should only add fill factor space, if and when you have a good reason to. In most cases unless a index is fragmented > about 30% you would not even rebuild/reorganize it. Even then you would only consider adding fill factor space if it is getting fragmented fast or often.

I recommend you put the fill factor back to zero, and use the code below to periodically check for fragmented indexes.

If you have a scheduled index job like ola https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html it should include details about what indexes are being rebuilt/reorganized check those results before making any changes. 

--Check Index Fragmentation (where >30%)

-- Source https://gallery.technet.microsoft.com/scriptcenter/Check-SQL-Server-a-a5758043
SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName, 
ind.name AS IndexName, indexstats.index_type_desc AS IndexType, 
indexstats.avg_fragmentation_in_percent,
indexstats.page_count,
indexstats.record_count -- 'SAMPLED' in Scanning Modes gives estimate based on 1% count, NULL gives no results but is fastest query, 'DETAILED' gives actual value but is very slow.
-- Source 'Scanning Modes' https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-physical-stats-transact-sql?view=sql-server-2017#scanning-modes 
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') indexstats 
INNER JOIN sys.indexes ind  
ON ind.object_id = indexstats.object_id 
AND ind.index_id = indexstats.index_id 
WHERE indexstats.avg_fragmentation_in_percent > 30 
ORDER BY indexstats.avg_fragmentation_in_percent DESC

Additional note:

Many sources do not recommend rebuild/reorganize on indexes with less than 1.000 pages. Ola's default settings will ignore these.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.