How to check Index Fragmentation and when to perform Index Rebuild OR Index Reorganize?

This topic describes how to reorganize or rebuild a fragmented index in SQL Server by using SQL Server Management Studio or Transact-SQL. The SQL Server Database Engine automatically modifies indexes whenever insert, update, or delete operations are made to the underlying data. Over time these modifications can cause the information in the index to become scattered in the database (fragmented). Fragmentation exists when indexes have pages in which the logical ordering, based on the key value, does not match the physical ordering inside the data file. Heavily fragmented indexes can degrade query performance and cause your application to respond slowly, especially scan operations. You can remedy index fragmentation by reorganizing or rebuilding an index. For partitioned indexes built on a partition scheme, you can use either of these methods on a complete index or a single partition of an index. Rebuilding an index drops and re-creates the index. This 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 is specified, all indexes on the table are dropped and rebuilt in a single transaction. Reorganizing an index uses minimal system resources. It defragments the leaf level of clustered and nonclustered indexes on tables and views by physically reordering the leaf-level pages to match the logical, left to right, order of the leaf nodes. Reorganizing also compacts the index pages. Compaction is based on the existing fill factor value. link https://docs.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-2017

We need to use the Dynamic ManagementObject(DMO) sys.dm_db_index_physical_stats. This DMO accepts 5 parameters : DatabaseID, ObjectID, IndexID, PartitionNumber and Mode. Generally when we need to check fragmentation we use the DB_ID with Detailed mode. There are 3 Columns to look out for 1. avg_fragmentation_in_percent : Number of out of order pages in the index - Lower value is better 2. fragment_count : Number of fragments in an index(Continuous Pages) - Lower value is better 3. avg_fragment_size_in_pages : Average Number of pages in one fragment - Larger value is better If the avg_fragmentation_in_percent is greater than 30, then we should use Rebuild Index and if the avg_fragmentation_in_percent is less than 30, then we should use Reorganize Index. SELECT D.NAME AS DBNAME,OBJECT_NAME(A.object_id) AS TABLENAME, B.name AS INDEXNAME, * FROM SYS.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('TABLE_NAME'),NULL,NULL,'DETAILED') A CROSS APPLY( SELECT NAME FROM SYS.INDEXES WHERE OBJECT_ID = A.object_id AND index_id IN(A.index_id))B INNER JOIN SYS.DATABASES D ON A.database_id= D.database_id In the above query you can replace Object_ID parameter with NULL to get the details about all the tables. And then can run the Alter Index query for the individual Index or for all the Indexes. ALTER INDEX INDEX_NAME ON TABLE_NAME REORGANIZE/REBUILD ALTER INDEX ALL ON TABLE_NAME REORGANIZE/REBUILD