Monday, February 4, 2019

Check Out How to Get Index Fragmentation Report in SQL Server

SQL Server indexes are quite a useful component that speeds up the query execution of the database. But at the same time, it has its own demerits. The indexes utilizes extra disk space and take more time to get updated with the changes made in the tables. When we are applying any changes, index fragmentation can take place. Index fragmentation can cause you much trouble and hence it is important to get index fragmentation report in SQL server. In this post, we will be discussing ways to get the fragmentation report and the steps you should take if the fragmentation rate is high. Let us learn about Index Fragmentation in detail first.

What is Index Fragmentation in SQL Server

When the indexes are created, there should be no fragmentation or little fragmentation in these. In course of time, index fragmentation rate increases along with the increase of data insertion, update, and deletion. Wondering how and why? Here is the reason. When a page is completely filled with data, page split happens to give space to more data. During page split, some data of the full page is moved to the new page, which is being created after all other pages of the database. As a result, Server does not move from one page to the next for data. Rather, it searches for the data in a page located elsewhere in the database. This process is known as index fragmentation.

Importance of Checking Index Fragmentation Report in SQL Server

As mentioned earlier, index fragmentation will be there as the index grows bigger but fragmentation level has to be under control. In case of high index fragmentation, data stored in the indexes will get dispersed all over the database, SQL Server will do unnecessary data reads and page switching, and query performance will become poor in case of tables. To understand whether the dull performance of your database is caused by index fragmentation, you need to have index fragmentation report.

Types of Index Fragmentation

Fragmentation can be categorized into two types: internal fragmentation and external fragmentation. Let’s know about these categories in details.

Internal Fragmentation: This type of fragmentation take place when the Index pages have too much free space. During the creation of index or at the time of index rebuilding, some free space is preferred. At the time of index creation and rebuilding, users can determine the percentage of free space in index page by adjusting the Fill Factor setting. If there is too much fragmentation in index pages, not only the user query results will come late, but also the indexes will become unnecessarily large. No space for index data pages will result in page split during data changes, and this process will utilize additional system resources.

External Fragmentation: External fragmentation is also known as logical fragmentation. When the logical ordering of the index mismatches its physical ordering, external fragmentation happens. In simple language, it means that one or more index leaf pages are not placed in the logical order. When it occurs in SQL Server, the Server has to put extra effort to provide the results of the queries. In most cases, this type of fragmentation does not cause much trouble, especially for the searches that bring only a few records and for the searches where the results do not have to be in ordered form.

How to Get Index Fragmentation Report in SQL Server

In order to get a report on the level of index fragmentation, one Database Management Function (DMF) “sys.dm_db_index_physical_stats” can be used. It will report about a particular index or partition, or all indexes of a table, database, and even an entire SQL Server. One example is below where a query has been made to know the fragmentation information of “AdventureWorks” database’s object “Sales.SalesOrderDetail.”

select a.index_id, name, avg_fragmentation_in_percent, fragment_count, avg_fragment_size_in_pages
from sys.dm_db_index_physical_stats (DB_ID('AdventureWorks'), object_id('Sales.SalesOrderDetail'), NULL, NULL, NULL) as a
join sys.indexes as b on a.object_id = b.object_id and a.index_id = b.index_id

What to Do If SQL Server Index Fragmentation Rate is High

Using the query mentioned here, you can know the fragmentation percentage from the index fragmentation report in SQL Server. The rate will determine what to do next. If the fragmentation percentage is less than 5%, you do not have to do anything. If the rate is between 05%-30%, you have to reorganize the index by using “ALTER INDEX REORGANIZE” script. If the fragmentation level is more than 30%, you have to rebuild the indexes. For this, you have to use “ALTER INDEX REBUILD” script. Following these approaches, you can reduce the rate of index fragmentation and increase the overall performance of SQL Server.

Note: Check out How to Find Fragmentation in SQL Server: (https://www.sqlserverlogexplorer.com/how-to-find-database-fragmentation/ )

Final Words

Index fragmentation is inevitable, but a high percentage of fragmentation will hamper the SQL Server performance. To avoid it, users must know the fragmentation level from index fragmentation report in SQL Server. In this post, we have discussed the way to obtain index fragmentation report and what to do if the fragmentation rate is high. Users can follow the methods described here and resolve their problems.

About the Author

Andrew Jackson is a SQL Server blogger. Check more of his work at: http://sqlserveroverview.blogspot.com/

0 comentarios:

Post a Comment