How to check index fragmentation on all indexes in a SQL Server database

Check Index Fragmentation on ALL Indexes in a Database

I use to make note for my self so that I can recall it quickly!
when I am using a development or test environment and I run into a situation where a query is behaving slower than normal, first thing I use to check, is there any fragmented indexes?. this is a simple query that will present every index on every table in your database, ordered by percentage of index fragmentation. This query will work on SQL2005 or newer versions.

Basically all we need to do is the following:

1-Open SQL Server Management Studio “SSMS” and connect to your Required server.
2-Right click on the database in Object Explorer for which DB you have to get all the index fragmentation.
3-Run the following query to get index fragmentation for all the indexes in a respected database.

SELECT dbschemas.[name] AS 'Schema', 
dbtables.[name] AS 'Table',
dbindexes.[name] AS 'Index',
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables ON dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas ON dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent DESC;

Leave a Reply

Your email address will not be published. Required fields are marked *