docs.microsoft.com:
FILLFACTOR:
"Specifies a percentage that indicates how full the Database Engine should make the leaf level of each index page during index creation or rebuild. fillfactor must be an integer value from 1 to 100. If fillfactor is 100, the Database Engine creates indexes with leaf pages filled to capacity. The FILLFACTOR setting applies only when the index is created or rebuilt. The Database Engine does not dynamically keep the specified percentage of empty space in the pages."
Solution:
Declare @FillFactorLowValue int = 0
Declare @FillFactorHighvalue int = 90
Declare @PageCount int = NULL
Declare @FragmentationValue int = NULL
--Note: Check the Where clause for items not applicable to Fill Factor
Select
@@SERVERNAME As 'Server',
DB_Name() as 'Database',
OBJECT_SCHEMA_NAME(i.object_id) as 'Schema',
OBJECT_NAME(i.OBJECT_ID) AS 'Table',
istats.index_type_desc as 'Index Type',
i.name AS 'Index',
istats.alloc_unit_type_desc as 'Allocation Type',
istats.page_count as '# Pages',
Coalesce((SELECT SUM(s.rows) FROM sys.partitions s WHERE s.object_id = i.object_id AND s.index_id in (0,1)), 0) '# Rows',
fill_factor as 'Fill Factor %',
Convert(Decimal(18,5),istats.avg_fragmentation_in_percent) as 'Avg. Fragmentation %',
istats.index_id,
istats.index_level,
istats.index_depth
From sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) istats
Inner JOIN sys.indexes i on i.object_id = istats.object_id AND i.index_id = istats.index_id
Where
(istats.avg_fragmentation_in_percent > @FragmentationValue or @FragmentationValue IS NULL)
And (istats.page_count > @PageCount or @PageCount IS NULL)
And (i.fill_factor > @FillFactorLowValue OR @FillFactorLowValue IS NULL)
And i.fill_factor < @FillFactorHighvalue
And OBJECT_SCHEMA_NAME(i.object_id) <> 'sys'
And i.name IS NOT NULL
And istats.alloc_unit_type_desc = 'IN_ROW_DATA' --Fillfactor applies to in-row(IN_ROW_DATA) Data Pages.
And i.[type] <> 0 --Fillfactor setting only applies to indexes, not to all tables. (Exclude Heaps)
And i.object_id not in (Select object_id from sys.objects where is_ms_shipped = 1)
--And i.object_id >= 100
Order By '# Rows' Desc;
--Select * from sys.objects where name = ''
--Select * from sys.indexes where object_id =
Resources:
sys.dm_db_index_physical_stats (Transact-SQL)
sys.indexes (Transact-SQL)
CREATE INDEX (Transact-SQL)
Why, when and how to rebuild and reorganize SQL Server indexes
|
|
|
|
|
|