This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In the previous post, I have explained basic about the SQL Server Fillfactor.
In this post, I am going to share some scripts to configure Fill Factor for Index and SQL Server Instance.
Check the default Fillfactor value for SQL Server Instance:
|
1 2 3 |
SELECT * FROM sys.configurations WHERE name ='fill factor (%)' |
Change the SQL Server default Fillfactor from (0 or 100) to 80:
|
1 2 3 4 5 6 7 8 |
sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'fill factor', 80; GO RECONFIGURE; GO |
Check the Fillfactor value for Indexes:
|
1 2 3 4 5 |
SELECT OBJECT_NAME(OBJECT_ID) Name ,type_desc ,fill_factor FROM sys.indexes |
Create new Index with Fillfactor option:
|
1 2 3 4 5 6 7 8 9 |
CREATE TABLE tbl_TestFillFactor ( ID INTEGER PRIMARY KEY ,Name VARCHAR(50) ) GO CREATE NONCLUSTERED INDEX idx_TestFillFactor ON tbl_TestFillFactor(Name) WITH (FillFactor = 80) GO |
Alter existing Index to change Fillfactor value:
|
1 2 3 |
ALTER INDEX idx_TestFillFactor ON tbl_TestFillFactor REBUILD WITH (FILLFACTOR = 90) GO |
