5

I am trying to reduce the fragmentation of my indexes.

First of all I rebuild all indexes with fragmentation higher than 30%, but the most of my indexes still with high fragmentation or does not even change 1%.

Looking deeply, when I reduce the fill factor on the rebuild statement then reduces the fragmentation too.

Now, what is the best option?

A higher fragmentation with a high fill factor or reduce the fill factor and then gets lower fragmentation?

Thanks for now.

  • 2
    How many pages are in these indexes? – Aaron Bertrand Dec 23 '15 at 19:24
  • For most of my tables that does't affect the rebuild, are among 1000 to 2000 pages. – FpontoDesenv Dec 23 '15 at 19:34
  • 1
    You should ignore the tables less than 5000 pages. No benefit of rebuilding the indexes on small tables. – Kin Shah Dec 23 '15 at 19:42
  • In regards to your question, do you want to take up more disk space and RAM to reduce fragmentation, or do you want more fragmentation but smaller disks and RAM footprint with faster reads? – Ali Razeghi Dec 23 '15 at 20:34
5

From this connect item Index Rebuild Doesn't Affect Fragmentation

For small tables, usually performance impact on fragmentation is undectable. The first 8 page allocation would be from mixed extents and mixed extents could be anywhere in database files. Rebuilding indexes would not change this nature.

If you have a small table, those mixed pages weight a lot during fragmentation calculation; therefore, rebuilding index may not reduce fragmentation. (As matter of fact, I could easily construct a case that fragmentation increases after rebuild.) Those fragmentation would not be a pain for your query performance; so basically you can ingore them.

When page counts of an index reaches to certain big size (for example, 1000 pages), then fragmentation may start to impact performance. Rebuilding index should reduce fragmentation.

Therefore probable reason rebuilding does not lower the fragmentation value due to fact that after rebuild the pages which are allocated to index is from Mixed extent.

Also, @Shanky has written a great article on same with in-depth analysis showing this behavior with a test scenario in What can Cause Index to be Still Fragmented After Rebuild

| improve this answer | |
0

Here is a quick example:

USE [AdventureWorks]
GO

ALTERINDEX [PK_Product_ProductID] ON [Production].[Product]
REBUILDPARTITION=ALLWITH (FILLFACTOR= 90)
GO

The best value for the fill factor

  1. Static tables – set fill factor at 100 (or default server fill factor)

  2. Tables updated less often – set fill factor at 95.

  3. Frequently updated tables – set fill factor between 70 and 90.

  4. Tables with clustered index on identity column – set fill factor at 100.

If the fill factor is 100, I usually say it needs to be lowered for the frequently updated tables. If fill factor is lower than 100, I usually say, it needs to be set to 100 for identity columns and master tables.

| improve this answer | |
  • thank you. When you say :"Tables with Clustered Index on Identity Column – Set Fill Factor at 100." Imagine a orders table, Could I set the fill factor for then clustered index in the identity column to 100 and the noncluster index to something between 70 and 90? – FpontoDesenv Dec 23 '15 at 19:22
  • Can you elaborate WHY you recommend those settings?? Most definitely #4 seems very much disputed ..... (e.g. the fact that a clustered index exists on an identity column really doesn't make much difference - if such a table is updated frequently, you would still want to have a lower fill factor!) – marc_s Dec 23 '15 at 19:27
  • 1
    #4 setting for clustered index. In this case, all the new data is always inserted at the end of table and a new row is never inserted in the middle of the table. In this situation, the value of Fill Factor does not play any significant role and it is advisable to have the Fill Factor set to 100. – Sudhir Panda Dec 23 '15 at 19:34
  • #4 setting for clustered index on identity column so.. – Sudhir Panda Dec 23 '15 at 19:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.