Start preparing for your Next Exam | Use coupon – TOGETHER | Avail 30% discount

Assess fragmentation of databases and logs

  1. Home
  3. Assess fragmentation of databases and logs

Go back to DP-300 Tutorials

In this we will learn how to assess fragmentation of databases and logs. However, once you determine the amount of fragmentation that exists for an index, you can defragment an index by either reorganizing an index or rebuilding an index by running Transact-SQL commands in your tool of choice or by using SQL Server Management Studio.

Index fragmentation

What is index fragmentation and why should I care about it:

  • Firstly, fragmentation exists when indexes have pages in which the logical ordering within the index, based on the key value of the index, does not match the physical ordering inside the index pages.
  • Secondly, the Database Engine automatically modifies indexes whenever insert, update, or delete operations are made to the underlying data.
  • Lastly, heavily fragmented indexes can degrade query performance because additional I/O is required to locate data to which the index points. More I/O causes your application to respond slowly, especially when scan operations are involved.

Detecting the amount of fragmentation

The first step in deciding which index defragmentation method to use is to analyze the index to determine the degree of fragmentation. You detect fragmentation differently for rowstore indexes and columnstore indexes.

Detecting fragmentation of rowstore indexes

By using sys.dm_db_index_physical_stats, you can detect fragmentation in a specific index, all indexes on a table or indexed view, all indexes in a database. However, for partitioned indexes, sys.dm_db_index_physical_stats also provides fragmentation information for each partition.

The result set returned by sys.dm_db_index_physical_stats includes the following columns:

Assess fragmentation of databases and logs
Image Source: Microsoft
  • Firstly, these values provide a rough guideline for determining the point at which you should switch between ALTER INDEX REORGANIZE and ALTER INDEX REBUILD. However, the actual values may vary from case to case. It is important that you experiment to determine the best threshold for your environment.
  • Secondly, rebuilding an index can be executed online or offline. Reorganizing an index is always executed online. To achieve availability similar to the reorganize option, you should rebuild indexes online. For more information, see INDEX and Perform Index Operations Online.

Detecting fragmentation of columnstore indexes

By using sys.dm_db_column_store_row_group_physical_stats, you can determine the percentage of deleted rows in an index, which is a reasonable measure for fragmentation in a rowgroup of a columnstore index.

However, the result set returned by sys.dm_db_column_store_row_group_physical_stats includes the following columns:

detecting fragmentation
Image Source: Microsoft

To check the fragmentation of a rowstore index using Transact-SQL

The following example finds the average fragmentation percentage of all indexes in the HumanResources.Employee table in the AdventureWorks2016 database.

transact sql
Image Source: Microsoft

To check the fragmentation of a columnstore index using Transact-SQL

The following example finds the average fragmentation percentage of all indexes in the dbo.FactResellerSalesXL_CCI table in the AdventureWorksDW2016 database.

columnstore index
Image Source: Microsoft

Defragmenting indexes by rebuilding or reorganizing the index

You defragment a fragmented index by using one of the following methods:

  • Firstly, Index reorganization
  • Secondly, Index rebuild
Dp-300 practice tests

Reorganize an index

Reorganizing an index uses minimal system resources and is an online operation. This means long-term blocking table locks are not held and queries or updates to the underlying table can continue during the ALTER INDEX REORGANIZE transaction.

  • Firstly, for rowstore indexes, the Database Engine defragments the leaf level of clustered and nonclustered indexes on tables and views by physically reordering the leaf-level pages to match the logical order of the leaf nodes (left to right).
  • Secondly, when using columnstore indexes, the delta store may end up with multiple small rowgroups after inserting, updating, and deleting data over time. Reorganizing a columnstore index forces all of the rowgroups into the columnstore, and then combines the rowgroups into fewer rowgroups with more rows.

Rebuild an index

Rebuilding an index drops and re-creates the index. Depending on the type of index and Database Engine version, a rebuild operation can be done online or offline. For the T-SQL syntax, see ALTER INDEX REBUILD

  • Firstly, for rowstore indexes, rebuilding removes fragmentation, reclaims disk space by compacting the pages based on the specified or existing fill factor setting, and reorders the index rows in contiguous pages. When ALL specify then, all indexes on the table get drop and rebuilt in a single transaction. Foreign key constraints do not have to be dropped in advance.
  • Secondly, for columnstore indexes, rebuilding removes fragmentation, moves all rows into the columnstore, and reclaims disk space by physically deleting rows that have been logically deleted from the table.

Remove fragmentation using SQL Server Management Studio

To reorganize or rebuild an index

  • Firstly, in Object Explorer, Expand the database that contains the table on which you want to reorganize an index.
  • Secondly, expand the Tables folder.
  • Thirdly, expand the table on which you want to reorganize an index.
  • Then, expand the Indexes folder.
  • Right-click the index you want to reorganize and select Reorganize.
  • After that, in the Reorganize Indexes dialog box, verify that the correct index is in the Indexes to be reorganized grid and click OK.
  • Select the Compact large object column data check box to specify that all pages that contain large object (LOB) data are also compacted.
  • Lastly, click OK.
To reorganize all indexes in a table
  • Firstly, in Object Explorer, Expand the database that contains the table on which you want to reorganize the indexes.
  • Secondly, expand the Tables folder.
  • Thirdly, expand the table on which you want to reorganize the indexes.
  • Then, right-click the Indexes folder and select Reorganize All.
  • In the Reorganize Indexes dialog box, verify that the correct indexes are in the Indexes to be reorganized. To remove an index from the Indexes to be reorganizing type grid, select the index and then press the Delete key.
  • Then, select the Compact large object column data check box to specify that all pages that contain large object (LOB) data are also compacted.
  • Lastly, click OK.

Remove fragmentation using Transact-SQL

To reorganize a fragmented index

The following example reorganizes the IX_Employee_OrganizationalLevel_OrganizationalNode index on the HumanResources.Employee table in the AdventureWorks2016 database.

SQL
ALTER INDEX IX_Employee_OrganizationalLevel_OrganizationalNode
ON HumanResources.Employee
REORGANIZE;

To reorganize all indexes in a table

The following example reorganizes all indexes on the HumanResources.Employee table in the AdventureWorks2016 database.

SQL
ALTER INDEX ALL ON HumanResources.Employee
REORGANIZE;

To rebuild a fragmented index

The following example rebuilds a single index on the Employee table in the AdventureWorks2016 database.

SQL
ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee
REBUILD
;

To rebuild all indexes in a table

The following example rebuilds all indexes associated with the table in the AdventureWorks2016 database using the ALL keyword. Three options are specified.

SQL
ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON)
;

Assess fragmentation of databases and logs DP-300 online course

Reference: Microsoft Documentation

Go back to DP-300 Tutorials

Menu