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 :
- Reorganize Index : Index should be reorganized when index fragmentation is between 5% to 30%
- 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
- https://docs.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes
- http://www.schneider-electric.com/en/faqs/FA234246/
Disclaimer
Evaluate the suggestions given in this blog post wisely before applying it in Production environment.
SQL Server Carpenter 
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
LikeLike
Yes Jeff, I’ll do. By the way this myth has been already resolved long way back after having your responses on several threads of SSC on the similar subject.
LikeLike