Sql-server – the cause of high index fragmentation in SQL server

fragmentationindexsql server

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?

Best Answer

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
Related Question