HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

What is the cause of high index fragmentation in SQL server?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
thefragmentationwhatserversqlhighcauseindex

Problem

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.

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?

Solution

Things to consider are :

  • Fill factor



  • 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



  • Update and delete operations



  • The last time you rebuilt the index

Context

StackExchange Database Administrators Q#121068, answer score: 2

Revisions (0)

No revisions yet.