Performance Tunning:- Find Index fragmentation in SQL Server Databases

The following script provide the details of the name of each index, the name and schema of the table for which it was created, as well as the degree of fragmentation and the number of index pages.
This script should be executed within a maintenance window, especially for large databases that are used frequently, as the value for average fragmentation is determined in run time.

USE [<Db_Name>]
go

SELECT s.name [schema], t.name [table], i.name [index],ips. avg_fragmentation_in_percent [fragmentation],ips.page_count [pages]
FROM sys.dm_db_index_physical_stats(DB_ID(),DEFAULT,DEFAULT,DEFAULT,DEFAULT) ips JOIN
sys.indexes i ON i.index_id = ips.index_id AND i.object_id = ips.object_id JOIN
sys.tables t ON t.object_id = ips.object_id JOIN sys.schemas s on s.schema_id = t.schema_id WHERE ips.page_count > 50 AND
ips.avg_fragmentation_in_percent > 5 AND ips.index_type_desc in ('CLUSTERED INDEX','NONCLUSTERED INDEX');

Leave a Reply Cancel reply