Ways to Know How to Find Database Fragmentation In SQL Server

Jason Clark | August 12th, 2016 | SQL |

What is Fragmentation In SQL Server Database?

Fragmentation in SQL Server occurs when the data within a table is inserted, deleted and modified over time. There are two kinds of fragmentation that can occur in SQL Fragmentation:

1. Logical Order Fragmentation

This issue in SQL Fragmentation is called as External Fragmentation in this when the data within a table is deleted, inserted and modified over time, index in the database can cause the pages to be out of order in which the next logical page is not same as the next physical page.

2. Page Density Fragmentation

This issue in SQL Fragmentation is called as Internal Fragmentation it occurs as page split and causes suboptimal use of the capacity of each page. Then, in this case the SQL Server has to read more number of pages then the necessary ones to satisfy user’s query.

As we keep on modifying our data in the database the indexes in the database gets fragmented. Because this it results in the degraded performance of the database and data becomes less efficient. Therefore, for that 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:

avg_fragmentation_in_percent:

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. The lower the value it comes for this query better 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 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 and rearrange the values in the database and then place the pages contiguously on the disk. This method is only useful whenever the data 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 perform 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.

CONCLUSION

In the end we would conclude by saying that fragmentation in a table of the database is a common problem as the user keeps on modifying and inserting the new records in a table of the database. There are two types of fragmentation which occurs in a database that user has to deal with when we talk about the fragmentation related to the database system. Then we discussed about the methods to deduct the fragmentation in the database. It is done by executing a simple SQL query and it will tell the percentage of fragmentation in the database. It will tell both page space used and fragmentation in a database based on the query executed by the user. Then we finally discussed about how to reduce the fragmentation from the database based on the fragmentation value detected by the user using various queries. Fragmentation is the most common problem in the computing industry.

Useful Links

Become a Writer
Do you have flair of writing for SQL Server & other Database related Technology
We welcome the technical post from bloggers & technical writers who can contribute interesting stories about SQL Server on regular basis. If you think you are a good fit, then this SQL Server Explorer is a Platform for you. Stay in Touch with us.