In the documentation it states that fill_factor from sys.indexes will be used, if not passed as a parameter for IndexOptimize:
FillFactor
Indicate, as a percentage, how full the pages should be made when rebuilding indexes. If a percentage is not specified, the fill >factor in sys.indexes is used.
I have an index with a fill factor of 80.
When I run index optimize without a @FillFactor parameter, my index will be rebuild. After the rebuild the index now has a fill factor = 0.
This is unexpected. I expected the fill factor to remain 80!
I've tried to search through the IndexOptimize sql, and I don't see any reference to fill_factor column of sys.indexes anywhere in the script either. Perhaps this was missed, or removed by mistake from the script?
I'll be happy to provide more information.
SQL Server version and edition
Microsoft SQL Server 2019 (RTM-GDR) (KB5021125) - 15.0.2101.7 (X64) Jan 23 2023 13:08:05 Copyright (C) 2019 Microsoft Corporation Standard Edition (64-bit) on Windows 10 Pro 10.0 (Build 19045: ) (Hypervisor)
Version of the script
2022-12-03 17:23:44
What command are you executing?
EXECUTE dbo.IndexOptimize
@Databases = 'MyDatabase',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@LogToTable = 'Y'
What output are you getting?
Date and time: 2023-05-03 14:25:41
Database context: [MyDatabase]
Command: ALTER INDEX [IX_MyIndex] ON [dbo].[MyTable] REBUILD WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON, RESUMABLE = OFF)
Comment: ObjectType: Table, IndexType: NonClustered, ImageText: No, NewLOB: No, FileStream: No, ColumnStore: No, AllowPageLocks: Yes, PageCount: 46630, Fragmentation: 98.0742
Outcome: Succeeded
Duration: 00:00:05
Date and time: 2023-05-03 14:25:46
In the documentation it states that fill_factor from sys.indexes will be used, if not passed as a parameter for IndexOptimize:
I have an index with a fill factor of 80. When I run index optimize without a @FillFactor parameter, my index will be rebuild. After the rebuild the index now has a fill factor = 0. This is unexpected. I expected the fill factor to remain 80!
I've tried to search through the IndexOptimize sql, and I don't see any reference to fill_factor column of sys.indexes anywhere in the script either. Perhaps this was missed, or removed by mistake from the script?
I'll be happy to provide more information.
SQL Server version and edition Microsoft SQL Server 2019 (RTM-GDR) (KB5021125) - 15.0.2101.7 (X64) Jan 23 2023 13:08:05 Copyright (C) 2019 Microsoft Corporation Standard Edition (64-bit) on Windows 10 Pro 10.0 (Build 19045: ) (Hypervisor)
Version of the script 2022-12-03 17:23:44
What command are you executing? EXECUTE dbo.IndexOptimize @Databases = 'MyDatabase', @FragmentationLow = NULL, @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationLevel1 = 5, @FragmentationLevel2 = 30, @LogToTable = 'Y'
What output are you getting? Date and time: 2023-05-03 14:25:41 Database context: [MyDatabase] Command: ALTER INDEX [IX_MyIndex] ON [dbo].[MyTable] REBUILD WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON, RESUMABLE = OFF) Comment: ObjectType: Table, IndexType: NonClustered, ImageText: No, NewLOB: No, FileStream: No, ColumnStore: No, AllowPageLocks: Yes, PageCount: 46630, Fragmentation: 98.0742 Outcome: Succeeded Duration: 00:00:05 Date and time: 2023-05-03 14:25:46