SQL DBA Tutorial

How to Find Index Fragmentation on SQL Server

How to Find Index Fragmentation Report on SQL Server Database

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.

Get SQL Dba Tutorial Updates!

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

Thank you for subscribing.
Something went wrong.
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.

Exit mobile version