Command to Find and Reduce Database Fragmentation in SQL Server
What is Fragmentation In SQL Server Database?
Fragmentation in SQL Server occurs when the data within a table is inserted, deleted or modified over time. There are two kinds of fragmentation that can occur in SQL Server:
1. Logical Fragmentation
Logical fragmentation in SQL Server Database occurs when logical ordering of pages containing indexes does not match the physical ordering of pages inside the data file. Logical fragmentation occur by the splitting of pages due to any insert or update operation or heavy deletion of pages that result in discontinuation in page chain.
2. Extent Fragmentation
Extent fragmentation occurs when the extent of indexes are not contiguous with the database leaving extents. Extent fragmentation occurs due to some random delete or interleaving table’s data extent with other objects.
As we keep on modifying our data in the database the indexes in the database gets fragmented. Because of this, it results in the degraded performance of the database and data becomes less efficient. Therefore,we need to identify the techniques, which will help in finding the database fragmentation in SQL Server.
How To Find Database Fragmentation In SQL Server
In order to clear the problem of both fragmentation types the user has to use the MDV command sys.dm_db_index_physical_stats.
Two important columns helpful in detecting the database fragmentation or to find table fragmentation in SQL Server are:
This query represents the External Fragmentation and is represented in percentage value. In Logical Fragmentation this query is suitable for the clustered table and for the heap it’s called Extent Fragmentation. Lower the value better will be the result. 10% value is good and less than 10% value is okay and more than 10% value corrections are required in the database.
This query calculates the percentage use of pages and it represents as Internal Fragmentation. Higher the value better the results. If Less than 75% value corrections are required in the database.
How to Reduce Fragmentation?
After finding the database fragmentation or table fragmentation in SQL server one can use the following procedures and methods in order to remove the fragmentation from the database. It will help the user to remove the fragmentation from table in their database and get accurate results from that. They are as follows:
1. Reducing Fragmentation in a heap
In order to reduce the fragmentation in a heap user must create clustered index on a table. After creating the clustered index, rearrange the values in the database and then place the pages contiguously on the disk. This method is only useful, whenever the data is in the heap and unable to verify the data.
2. Reducing Fragmentation in an Index
There are three methods which can be used by the user to reduce fragmentation in an index. It depends on the user which method they want to use according to the percentage value user has got from the detection of fragmentation of a database. User has to choose from one of the following methods:
- If avg_fragmentation_in_percent >5% and less than 30% then use ALTER INDEX REORGANIZE: This statement is replaced by DBCC INDEXDEFRAG and leaf order pages are reordered in a logical order. It is performed when the user is online as this is an online operation the index is available while the statement is running.
- If avg_fragmentation_in_percent >30% then use ALTER INDEX REBUILD: This statement is replaced by DBCC DBRINDEX and the index can be rebuild both online or offline mode. In that case, we can use drop or re-create index method.
- Drop or Re-Create the clustered Index: Re-creating the clustered index redistributes the data and full data pages are created. Fullness level can be done by replacing CREATE INDEX in place of FILLFACTOR.
NOTE: This method is less recommended then the previous methods
These methods must be known to the user if they want to reduce the fragmentation from their database.
This blog discusses about the SQL Server Database Fragmentation and its types. It also covers, how one can find fragmentation in the database and what are the various ways to reduce the SQL database fragmentation .