3

We have an application which is using a SQL Server database. As the application is in use for many years, its database is of huge size, resulting in slowness of application. It was noticed that the data has high fragmentation. So we defragment and build indexes twice a week. After the defragmentation, the following is the fragmentation report of our db.

enter image description here

Results still shows high fragmentation, even the application is only used for 2 to 3 days after defragmentation. Can any expert tell if the results are normal and satisfactory? Can some one suggest some reason of this results?

Improve this question
4
  • What you are seeing is not abnormal this can happen. What is fill factor you specify, if any, while rebuilding index. Last few rows show lesser fragmentation which should not cause you to worry.
    – Shanky
    Commented Nov 14, 2015 at 6:00
  • You mean this result is OK? We should not have to worry about fragmentation and it will not affecting performance?
    – Umair
    Commented Nov 14, 2015 at 7:46
  • 4
    I am saying I have seen cases where even after rebuilding indexes fragmentation comes in after couple of days. This was because of workload and incorrect fill factor. Also make sure Auto Shrink is not enabled for the databases
    – Shanky
    Commented Nov 14, 2015 at 11:11
  • I'm not sure you have enough indexes on t_track. Some of them seem redundant (though inconsistent naming scheme might be throwing me off), and what do you suppose the ALL_COLUMNS index can do that the clustered index can't?
    – Aaron Bertrand
    Commented Nov 15, 2015 at 9:29

4 Answers 4

Reset to default
2

Things to consider are :

  1. Fill factor
  2. Sort order on the index/table. If table cluster index is not based on identity. When new record is being inserted, the new record could enter in the middle of table. It will screw up the cluster index
  3. Update and delete operations
  4. The last time you rebuilt the index
Improve this answer
1

As above and could be a case of Page Splits/Incorrect Fillfactor, SQLSkills has a great article on monitoring page splits with extended events and reducing page spiits definitely worth a look into >> tracking problematic pages splits

Improve this answer
1

I have seen cases where rebuilding the indexes in parallel (or without specifying MAXDOP) can cause fragmentation.

Try rebuilding the index with the option MAXDOP = 1.

ALTER INDEX (Transact-SQL)

This will result in the rebuild jobs taking longer, but it might help to reduce fragmentation. If you are using the Standard Edition, then the index will be unavailable until it has been rebuilt. On Enterprise you can specify the ONLINE = ON parameter to reduce the impact.

I have observed this on heavily used database servers.

Improve this answer
1

That's some pretty high fragmentation especially for those clustered indexes. Are these ID columns GUIDs? An auto-incrementing numeric PK really doesn't get fragmented since all data in that index is being inserted in order. Since GUIDs are random they are the absolute worst case for index fragmentation.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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