Query to show indexes fragmentation

sql server, T-SQL query that shows indexes fragmentation. Thanks to SQL Authority blog and Paul Randall.

And remember: Index should be rebuild when index fragmentation is great than 40%. Index should be reorganized when index fragmentation is between 10% to 40%. Index rebuilding process uses more CPU and it locks the database resources. SQL Server development version and Enterprise version has option ONLINE, which can be turned on when Index is rebuilt. ONLINE option will keep index available during the rebuilding.

Updated to show the fill factor and pages count. The idea is that if the page count is less then 1000 then in many cases the fragmentation doesn't matter - sql server may use the table itself instead of the index. Also read about  "mixed extents" and how it affects fragmentation.

SELECT

    ps.database_id,

    ps.OBJECT_ID,

    ps.index_id,

    b.name,

    ps.avg_fragmentation_in_percent,
--    ps.avg_page_space_used_in_percent, --uncomment it if the last parameter is 'SAMPLED'
    b.fill_factor,
    page_count,

    ps.index_type_desc,
    ps.index_level
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps

--FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'SAMPLED') AS ps

INNER JOIN sys.indexes AS b ON b.OBJECT_ID = ps.OBJECT_ID

AND ps.index_id = b.index_id

WHERE ps.database_id = DB_ID()

ORDER BY ps.avg_fragmentation_in_percent DESC

GO