Finding fragmentation

Fragmentation is one of the common bottlenecks in performance, if indexes are not being maintained properly. Microsoft recommends going for a REORGANIZE index instead of the resource-consuming REBUILD index, if fragmentation percentage is between 5 and 30. If the fragmentation level is more than 30 percent, then go for the REBUILD index. It is recommended to treat these values as an approximation instead of considering them absolute. From the time of Microsoft SQL Server 2000, this recommended figure has not changed; at the same time, there are many things that have changed from Microsoft SQL Server 2000 to Microsoft SQL Server 2012.

"It depends!" is the favorite quote of all IT personnel, and it really depends on our environment. So, first check the server environment to decide what percentage level is good enough for us to go for REBUILD, because many other factors need to be considered while deciding to go for this resource-centric task. The following are a few...