How to reduce fragmentation in SQL server? Let’s discuss about it in this article. We at Bobcares can take care of your server troubles with our Server Management Service.

How to reduce fragmentation in SQL server?

When considering the performance of database queries, reducing data fragmentation is really essential.

While we add, modify and remove records over time, it leads to progressive fragmentation of database’s tables and indexes. The SELECT queries as well as the INSERT, UPDATE, and DELETE actions could all suffer from this fragmentation. In this article, we will see some of the methods to reduce fragmentation in SQL servers.

Dos and Don’ts to reduce Index Fragmentation

Knowing the dos and don’ts is crucial when thinking about fragmentation minimization. Let’s look more closely into each of the category.

Dos

• Select cluster keys with either increasing or decreasing values. As entries insert into the start or end of the logical page chain, this will reduce the number of page splits when we do an insert operation.
• Watch out for features that can split pages. When changing any index-related feature, be mindful of the potential outcomes. updating columns with varying widths, as an example.
• Use the proper index fill factors. To reduce excessive fragmentation under normal load, analyze fragmentation at the default fill factor setting and make any necessary adjustments.

Don’ts

• Adding records with variable key values: Select static key values so that records won’t ever need to move and won’t go out of order after they are set.
• Increased length of records due to an update: While adding records to an index during an update, SQL server might have to move some of the data to a new page. Performance issues may eventually arise as a result of non-sequential leaf pages.
• Updating the index’s key columns: Updating a key column necessitates deleting all existing rows, then inserting all existing rows with the updated key value. Adding a new row on a page that is too crowded will result in a page split.

Methods to reduce Index Fragmentation

By Dropping and Re-creating an Index: Slowest, but provide the best performance. When we create a clustered index on a table with several secondary indexes, all of the secondary indexes must be rebuilt so that the leaf nodes contain the clustering key value instead of the row identifier. This can take significant time on a large table. Therefore, if we need to drop and re-create both clustered and non-clustered indexes, drop the non-clustered indexes first followed by clustered index. Then create clustered indexes first and the non-clustered indexes last.

When we construct a clustered index, it leads to copying of the the original table, sorting of the data, and then the removal of original table.

Rebuilding an Index: As rebuilding an index only requires one line, rather than several DROP INDEX and CREATE INDEX commands, it is a more effective technique to eliminate fragmentation than dropping and re-creating an index. We can use the DBCC DBREINDEX statement to rebuild indexes. We can use the below code to rebuilds all indexes with a fill-factor of 80 in every table in the current database:

CREATE PROC ind_rebuild
AS
DECLARE @TableName sysname
DECLARE cur_reindex CURSOR FOR
SELECT table_name
  FROM information_schema.tables
  WHERE table_type = ‘base table’
OPEN cur_reindex
FETCH NEXT FROM cur_reindex INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
  PRINT ‘Reindexing ‘ + @TableName + ‘ table’
  DBCC DBREINDEX (@TableName, ‘ ‘, 80)
  FETCH NEXT FROM cur_reindex INTO @TableName
END
CLOSE cur_reindex
DEALLOCATE cur_reindex
GO

De-fragmenting an Index: Clustered and non-clustered indexes can be de-fragmented using a new SQL Server 2000 statement called DBCC INDEXDEFRAG. De-fragmenting the index’s leaf level occur in such a way that the physical order of the index pages is in alignment with the leaf node’s logical left-to-right ordering. Every five minutes, the user will receive a report from the DBCC INDEXDEFRAG statement. We can stop it anytime to keep the work that has already been accomplished. The fact that the DBCC INDEXDEFRAG is an online procedure is its key benefit.

[Looking for a solution to another query? We are just a click away.]

Conclusion

In this article, we have seen some of the methods to reduce fragmentation in SQL server, provided by our Support team.