Index Re organization and Index Rebuild

1. How to find index fragmentation % in SQL Server?

Use below script:

SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName,
ind.name AS IndexName, indexstats.index_type_desc AS IndexType,
indexstats.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats
INNER JOIN sys.indexes ind 
ON ind.object_id = indexstats.object_id
AND ind.index_id = indexstats.index_id
WHERE indexstats.avg_fragmentation_in_percent > 10
ORDER BY indexstats.avg_fragmentation_in_percent DESC
2. What is Index  rebuild and re organization and when to use?

The SQL Server Database Engine automatically maintains 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.

Index should be reorganized when index fragmentation is between 10% to 40%

Index should be rebuild when index fragmentation is great than 40%

3. what is SQL Script for rebuild and re organization?


Index Rebuild : This process drops the existing Index and Recreates the index.

USE AdventureWorks2012;
GO
ALTER INDEX ALL ON Production.Product REBUILD
Go

Index Reorganize : This process physically reorganizes the leaf nodes of the index.
USE AdventureWorks2012;
GO
ALTER INDEX ALL ON Production.Product REORGANIZE
GO