CODEWITHSUNDEEP
sql-serverindexingdbcc
Vaibhav Survase
Vaibhav Survase

Reputation: 19

When do we specify fill factor for DBCC DBREINDEX?

I checked over google and found that if fill factor is 0 then it uses the 100% space on index page. Otherwise we need to specify the fill factor.

My questions to experts are:

  1. Should we leave it blank for maximum index efficiency?

  2. I found in our some databases that fill factor is 80, 85, 90, 100 mentioned for some of the indexes. So, when should we specify fill factor as 80 or 90?

    Select fill_factor,* from sys.indexes where fill_factor <> 0

Upvotes: 1

Views: 1379

Answers (1)

clairestreb
clairestreb

Reputation: 1292

THe default fillfactor is 100 out of the box (if it hasn't been changed). When fillfactor is 0, DBCC DBREINDEX uses the last value specified for the index.

Fillfactor is designed for improving index performance and data storage when the index is created, rebuilt, or defragged. By setting the fillfactor, you specify the percentage of space on each page to be filled with data, reserving free space on each page for future table growth. For example, if fillfactor is 80, then 20% of each page is left empty, providing space for new records. When that space is used up, a page split occurs.

Microsoft recommends us to use the default fillfactor in most cases. However, if you know how your table will be used, you can modify it. A fillfactor of 100 for tables that are not read-only would immediately cause a page split on an INSERT/UPDATE, so 100 is only suitable for read-only tables. Tables that have a high amount of writing should be somewhere between 50 and 70%. All other tables should be around 80 and 90, if they are mostly SELECTed rather than INSERTed and UPDATEd.

You should read up on page splitting. Also, establish a schedule for rebuilding your indexes. You also need to consider whether to cluster each index or not. For example, a clustered index with a low fragmentation percentage can be excluded from the schedule to save some time.

Some references:

Upvotes: 1

Related Questions