How to Find Index Fragmentation on SQL Server

In this Article, I am sharing T-SQL Script to find the Index Fragmentation report for SQL Server User database.

USE DatabaseName  –Need to Specify the User Database Name
go
SELECT OBJECT_NAME(fq.OBJECT_ID) AS TableName,

fq.name AS IndexName, qs.index_type_desc AS IndexType,

qs.avg_fragmentation_in_percent

FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) qs

INNER JOIN sys.indexes fq

ON fq.object_id = qs.object_id

AND fq.index_id = qs.index_id

WHERE qs.avg_fragmentation_in_percent > 0

ORDER BY qs.avg_fragmentation_in_percent DESC

After you run the script, you will get the Below result.

How to Find Index Fragmentation on SQL Server

Get SQL Dba Tutorial Updates!

Signup now and receive an email once I publish new content.

I agree to have my personal information transfered to MailChimp ( more information )

I will never give away, trade or sell your email address. You can unsubscribe at any time.

Add Comment