Best way to deal with Index Fragmentation

Fragmentation can hurt the performance of your query very badly. It is one of the key factor behind poor performing database. Many a times, DBA takes it very easy and respond when there is complete slowdown of the application. DBA respond to the situation in reactive manner instead of the proactive manner.

It is essential to removes fragmentation and reclaims disk space. Heavily fragmented indexes can degrade query performance and cause your application to respond slowly. Heavily fragmented indexes could cause increase in IO. An increased IO will cause increase in physical and logical reads as data is scattered across the multiple pages, which could actually accommodate in comparatively less number of pages if there is no fragmentation.

The solution to this could be :

  1. Reorganize Index : Index should be reorganized when index fragmentation is between 5% to 30%
  2. Rebuild Index : Index should be rebuild when index fragmentation is greater  than 30%

Index rebuild is one of the costly operation in SQL Server. It requires more CPU and it locks the database resources. Although, SQL Server has provided ONLINE option while rebuilding the indexes. Choosing ONLINE option will make the index available during the rebuild. But, we can’t ignore the cost associated with index rebuild.

Do we really need to wait till the fragmentation reaches 30%? If you ask me then NO. We should not wait and allow the fragmentation to reach the 30%. We should take the proactive measure and reorganize the indexes if the fragmentation reaches the 5%. We can implement some SQL Job which should run daily and check all the indexes with fragmentation 5% or more and reorganize them.

Query to check the index fragmentation :

SELECT dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent desc

References

  1. https://docs.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes
  2. http://www.schneider-electric.com/en/faqs/FA234246/

 

Disclaimer

Evaluate the suggestions given in this blog post wisely before applying it in Production environment.

2 thoughts on “Best way to deal with Index Fragmentation

  1. Hi, Brahmanand,

    Here’s something that most people don’t know… It’s “Death by SQL” to REORGANIZE most indexes that fragment. On exception is when fixing the aftermath of updatex to the “hotspot” on ever-increasing indexes.

    Please see the following video. Not only does the video destroy the myth of Random GUID fragmentation, but it also proves that REORGANIZE is the reason for the fragmentation and why along with what the “ExpAnsive” update issue is and how REORGANIZE will also mess up other indexes that fragment. My recommendation is to simply not use REORGANIZE except to compress LOBs. If you only have the Standard Edition, then it’s better to do no index maintenance than it is to do it wrong and using REORGANIZE is almost always going to do it wrong. It just doesn’t work the way most people think it does and, yeah, I prove that both in the documentation and in a demonstration.

    Here’s the link to a YouTube on the subject.https://www.youtube.com/watch?v=qfQtY17bPQ4

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s