Check Database Fragmentation Percentage

Whenever a database is frequently updated via INSERT, UPDATE, or DELETE statements, over time these modifications can cause the information in the index to become scattered in the database (fragmented).

As data is modified in a database, the database and its indexes become fragmented. As indexes become fragmented, ordered data retrieval becomes less efficient and reduces database performance.

If database indexes are fragmented, the SQL Server query optimizer may choose a non-optimal execution plan when using an index to resolve a query. Heavily fragmented indexes can degrade query performance and cause your application to respond slowly.

The below script helps to provide every index on every table in your database, ordered by percentage of index fragmentation.

SELECT dbschemas.[name] as 'Schema', 
dbtables.[name] as 'Table', 
dbindexes.[name] as 'Index',
indexstats.alloc_unit_type_desc,
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
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