I've always been told that REORGANIZE does nothing to fix poor page fullness because all that it does is reorder pages on disk. To investigate this, I read the documentation. However, I've found it contradicting itself. _Optimize index maintenance to improve query performance and reduce resource consumption_ [claims](https://learn.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-ver16#reorganize-an-index) > Reorganizing also compacts index pages to make page density equal to the fill factor of the index. and [the docs](https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-index-transact-sql?view=sql-server-ver16#reorganizing-indexes) for ALTER INDEX support this > Reorganizing also compacts the index pages. Compaction is based on the existing fill factor value. but this is contradicted by [the documentation](https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-index-transact-sql?view=sql-server-ver16#fillfactor--fillfactor) for fill factor > An explicit FILLFACTOR setting applies only when the index is first created or rebuilt. The Database Engine doesn't dynamically keep the specified percentage of empty space in the pages. So, what's going on? Does REORGANIZE compact pages and respect fill factor? Or is it just moving leaf pages and not compacting?

Fill factor is concept that works only when creating a new index or when the index is rebuilt. Subsequently there will be fragmentation due to inserts/updates which will create new pages (page split) without the fill factor. Is it correct to say that the concept of fill factor is useful only when the index rebuilt?

When the table has lots of data, and I create a new index with fill factor (say 30), then sql server will leave space for new entries to be added into the data page. This will keep fragmentation low when new inserts are made. But if the table is empty, and I create an index, then what is the benefit of adding fill factor to the index?

I'm new to concept of what fillfactor is and why it is used. So far, reading [this article](https://www.brentozar.com/archive/2013/04/five-things-about-fillfactor/) , I've learned that by setting the fillfactor configuration of an index, we specify how much space on each page should remain free, so that whenever a new record needs to be added to a page, there will be some free space for it. My question is that: 1. Is that free space on each page, intended to remain free? I can guess that, that free space is used when an insert happens, but later the database engine, moves data, so that again we have some free space in each page. Is this what happens in a DBMS?

I am setting up a new OLTP database and expect to write to the database about 70% of the time and read from it around 30% of the time. Given this criteria, I'm wondering what would be good practice in setting the default fill factor for this database as well as other best practices I may want to look into applying given this criteria. Any input would be greatly appreciated. Thanks

I have a production database for ERP which has clustered and non clustered indexes. Some other tables have index type as heap and i don't understand what does heap means. Every month i have a job run this command sp_msforeachtable "dbcc dbreindex('?')" to rebuild all indexes in our database after that i checked the fragmentation and i found it to be still 98%. I searched on web to discover that our Fill factor option for our server is 0, so can i change it to suitable percentage and what factors I need to consider?

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

We've been having a problem with page splits on a table that is a particular nuisance - its an audit log of activities in the database and has grown over 1TB. The main indexes are on the record type, which is an NVARCHAR(100) - because you need that when there are 5 record types - it makes much more sense than a TINYINT, and a record id - which is an NVARCHAR(200) instead of the integer key of the record. They are also covering indexes, with the key, old value, new value, etc - very wide. This is an old system, and unfortunately the code for this auditing is everywhere rather than centralized in a procedure. It can't be changed, and we're going thru the painful process of a long micro-services re-write. So, I have reduced the fill factor on two of the indexes from 100% to 85%. And the page splits have gotten worse. I'd say around 3x more page splits. Is this a common outcome? Most advice says to reduce fill factor to improve page split performance. I can understand why it may do this, because of the width of the data in the keys. Would the advice be to reduce the fill factor further, or to put it back to what it was?

We were viewing sys.dm_os_buffer_descriptors using below query

select 
 d.[name]										[Database_Name],
 (count(file_id) * 8) / 1024				    [Buffer_Pool_Size_MB],
 sum(cast(free_space_in_bytes as bigint)) / 1024 / 1024		[Free_Space_MB]
from sys.dm_os_buffer_descriptors b
	join sys.databases d on
		b.database_id = d.database_id
group by d.[name]
order by [Buffer_Pool_Size_MB]

And for one of my databases, it shows [Buffer_Pool_Size_MB] = 77325 Megabytes, and [Free_Space_MB] = 15849 Megabytes So its about 20% of space in pages in buffer pool is empty. Seems like a waste of resources Questions: - Is this a problem ? - How can number of free_space_in_bytes be mitigated ? - Any other things to investigate / look at in our situation ?

I have heavily updated / accessed table where I store serialized java objects. They are in the table for 2-3 hours (also are being updated during that period) and then removed. Size of table is around 300MB. I have spotted it is very, very often VACUUMed and wonder if changing the fillfactor would help?

I came across this old article on Brent Ozar's blog (written by Kendra Little) regarding Fill Factor: https://www.brentozar.com/archive/2013/04/five-things-about-fillfactor/ In it, she briefly mentions that the Fill Factor setting does not affect Heaps. Does this still apply today, and does she mean only pure heaps that don't even have non-clustered indexes?...or to any heap table regardless if it has non-clustered indexes?

We have a table that will hold records on the 108 order. ╔══════════╦═════╦══════╦══════════════════════════╗ ║ id ║ a ║ b ║ ...many other columns... ║ ╠══════════╬═════╬══════╬══════════════════════════╣ ║ 1 ║ 1 ║ 118 ║ ... ║ ║ 2 ║ 1 ║ 1022 ║ ... ║ ║ 3 ║ 5 ║ 118 ║ ... ║ ║ 4 ║ 3 ║ 118 ║ ... ║ ║ ... ║ ... ║ ... ║ ... ║ ║ 10020 ║ 5 ║ 1022 ║ ... ║ ║ 10021 ║ 1 ║ 118 ║ ... ║ ║ ... ║ ... ║ ... ║ ... ║ ║ 5000000 ║ 2 ║ 30 ║ ... ║ ║ 5000001 ║ 1 ║ 30 ║ ... ║ ║ ... ║ ... ║ ... ║ ... ║ ║ 28218321 ║ 1 ║ 118 ║ ... ║ ║ 28218322 ║ 4 ║ 57 ║ ... ║ ╚══════════╩═════╩══════╩══════════════════════════╝ Column a always holds one of 5 values. Column b always holds one of ~5000 values. a and b are unrelated. So for every value of a there will be at least tens of millions of table rows, and for every value of b there will be at least hundreds of thousands of rows. A lot of our queries are based on WHERE a= or WHERE b= so we are indexing those columns (two separate, single-column btree indexes). I am trying to get a sense of what the optimal choice of FILLFACTOR would be for this situation. This table does not receive many UPDATEs, however it receives large bursts of INSERTs (on the order of 106) in short periods of time. At the same time it is under moderate, constant read load. If my understanding of btree index fillfactor is correct it sounds as though we may benefit for a fairly low fillfactor for a and b indexes, allowing lots of empty space for new row references to be added to the leaf pages as new records come in with the same a or b value as previous ones. Is this correct? Is this a situation where a low fillfactor would be beneficial and if so what approximate value should we be looking at using?

I've recently become a DBA of sorts for our company's ERP system. I've been making small changes to the server & improvements have been steady. One area I'm concerned with is the index fill factor. I found a YouTube video Brent Ozar did on defragmenting & fill factor of indexes which was most insightful. All my fill factors are currently 100%, but I know that this isn't good for all my indexes. I found this article/script about determining a better fill factor for certain indexes. If this article is still applicable, this brings me to my question: I'm using the Ola Hallengren scripts to help keep my indexes tidy, but I don't see a way to leave the current fill factor alone. It will rebuild/etc with either the system setting or the specified one but not the current one if it is different. Does anyone have suggestions/practices they use if an index needs to be rebuild with a specific/different fill factor?

I have a test scenario where I have added fill-factor = 90 to a clustered index over a random generated GUID primary key. If I create the table without fill-factor = 90 then the number of page splits is initially 31 for 3974 rows and splits occur with every subsequent insert. When I include the fill-factor = 90 the number of splits on population rises to 35 but splits stop happening with every subsequent insert. All good so far! The problem is that when I add a non-clustered index although the clustered index still indicates a fill-factor of 90% the initial insert is back to 31 splits and splits are happening again on every subsequent insert indicating that the fill-factor is being ignored. Does anyone have an idea as to why this might be happening? /* scenarios: 1. Create table #Test02 without fillfactor, populate and observe 37 page splits occured, insert two rows at a time and observe page splits occurring 2. Drop and create table #Test02 with filfactor = 90, populate and observe 40 page splits occurred, insert two rows at a time and observe that page splits do not occurr 3. Drop and create table #Test02 with filfactor = 90, insert two rows then populate, observer 38 page splits occurred, insert two rows at a time and observe page splits occurring 4. Drop and create table #Test02 with filfactor = 90, create index IX_#Test02_ProductName, populate and observe 37 & 35 page splits occurred, insert two rows at a time and observe that page splits occur on PK 5. Rebuild index PK_Test02, insert two rows at a time and observe that page splits do not occurr Conclusion: On an empty table with a PK FF = 90 SQL preserves the fill factor */ --non-sequential index table IF OBJECT_ID('tempdb.dbo.#Test02') IS NOT NULL DROP TABLE #Test02 --CREATE TABLE #Test02(ID uniqueidentifier default newid(), ProductName nvarchar(150), CONSTRAINT PK_Test02 PRIMARY KEY CLUSTERED (ID)) CREATE TABLE #Test02(ID uniqueidentifier default newid(), ProductName nvarchar(150), CONSTRAINT PK_Test02 PRIMARY KEY CLUSTERED (ID) WITH (FILLFACTOR = 90)) --ALTER INDEX PK_Test02 ON #Test02 REBUILD WITH (FILLFACTOR = 90); IF EXISTS (SELECT * FROM tempdb.sys.indexes WHERE name = N'IX_#Test02_ProductName') DROP INDEX IX_#Test02_ProductName ON #Test02 CREATE INDEX IX_#Test02_ProductName ON #Test02(ProductName) --populate script INSERT #Test02(ProductName) SELECT TOP 4000 COALESCE(O1.name,O2.name) FROM master.sys.objects O1 CROSS JOIN master.sys.objects O2 --two row insert INSERT #Test02(ProductName) VALUES(N'Straight Banana'),(N'Bent Banana') --observe page splits SELECT ios.index_id , o.name as object_name , i.name as index_name , ios.leaf_allocation_count as page_split_for_index , ios.nonleaf_allocation_count page_allocation_caused_by_pagesplit , ios.leaf_insert_count , i.fill_factor FROM tempdb.sys.dm_db_index_operational_stats(db_id(N'db_name'), null, null, null) ios JOIN tempdb.sys.indexes i on ios.index_id = i.index_id AND ios.object_id = i.object_id JOIN tempdb.sys.objects o on ios.object_id = o.object_id WHERE o.type_desc = N'user_table' AND o.name like N'#test02%'

I've been digging around without any luck. SQL Server 2008: Is it possible to adjust the fill factor on a specific clustered index/table without having to rebuild the entire table? For example, if it has 400 million records, can we adjust the fill factor and have SQL Server use the new fill factor on all pages that haven't already exceeded that limit (as well as new splits), and then adjust pages that are over limit during index maintenance? Is it possible to pre-define the fill-factor that an index should be built to during the next index maintenance?

How do you know how much "Padding" an index should have?

I am a bit confused about page splits in MS SQL and I'm looking for a definitive answer. There seem to be two versions of the story: 1 - Fillfactor only affects how full pages are at the time the index is created/rebuilt. Page splits are always 50/50 2 - Fillfactor also affects how pages are split. So if there is 70% fillfactor and a page overflows, it will split 70/30 Thanks a lot

So I have a table with a clustered index on an ID column (which is an auto-increment column) in SQL Server. Is it okay to have a fill factor of 100 on this since the data will always get written with the next latest ID? Would it matter if the other columns on this table get updated a lot for the existing IDs? Also, how would deletes impact the fragmentation of the table?

We have a table where we are planning to update timestamp fields only. In the first import these columns will be nulls then we will update them as the computation finished on these rows. The question is; should we set a fill-factor lower than 100 for this table or PostgreSQL will allocate a fixed size for these columns so lowering the fill-factor makes no sense?

I have inherited a script that rebuilds the fill factor sitting at 90 fill factor on the INT primary key. It rebuilds it at a fill factor of 50. Then the script drops the index, alters the column to a BIGINT and rebuilds the fill factor back to its original 90. Does that make sense to anyone? Are they doing this to grow space? Seems like as soon as they drop the clustered index, the space is a moot point. Thoughts on this?