Find and Reduce Database Fragmentation in SQL Server
Summary: This blog is all about the fragmentation in SQL server & various ways to get rid of such issues. Types of index fragmentation in SQL server is also discussed here. In addition, users will get to know how to find this fragmentation & reduce it to make the server run smoothly.
Table of Content
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 this is just another issue like SQL server error 824 or any other error. 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. This 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. We can say that this 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 Check Database Fragmentation in SQL Server Easily?
In order to clear the problem of both fragmentation types the user has to use the DMV command sys.dm_db_index_physical_stats.
Two important columns helpful in detecting the database fragmentation or to find fragmentation in SQL Server are:
- avg_fragmentation_in_percent:
This query to check fragmentation of table in SQL server 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.
- avg_page_space_used_in_percent:
This query calculates the percentage use of pages and it represents as Internal Fragmentation. Higher the value better the results. If the value is less than 75%, corrections are required in the database.
How to Reduce Fragmentation in SQL Server?
After finding the database fragmentation or table fragmentation in SQL server one can use the following procedures and methods in order to reduce fragmentation in SQL Server 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.
Also Read: SQL Server Error 4014 Best Solution
Conclusion
This blog discusses about the SQL Server Database Fragmentation and its types. It also covers, how to find fragmentation in SQL Server and ways to reduce fragmentation in SQL Server. We are sure that by reading the entire article, users can easily check SQL server fragmentation & even reduce it as they want.