Get index fragmentation information

 Get index fragmentation information

Here's a handy SQL query that can help you identify fragmented indexes within your database. This script provides valuable insights into the fragmentation level of your indexes, allowing you to pinpoint which ones might be impacting performance. By proactively addressing index fragmentation, you can significantly improve the efficiency of your database operations.

SELECT DB_NAME(ps.database_id) AS [Database Name]
 ,SCHEMA_NAME(o.[schema_id]) AS [Schema Name]
 ,OBJECT_NAME(ps.OBJECT_ID) AS [Object Name]
 ,i.[name] AS [Index Name]
 ,ps.index_id
 ,ps.index_type_desc
 ,ps.avg_fragmentation_in_percent
 ,ps.fragment_count
 ,ps.page_count
 ,i.fill_factor
 ,i.has_filter
 ,i.filter_definition
 ,i.[allow_page_locks]
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, N'LIMITED') AS ps
INNER JOIN sys.indexes AS i WITH (NOLOCK) ON ps.[object_id] = i.[object_id]
 AND ps.index_id = i.index_id
INNER JOIN sys.objects AS o WITH (NOLOCK) ON i.[object_id] = o.[object_id]
WHERE ps.database_id = DB_ID()
 AND ps.page_count > 2500
ORDER BY ps.avg_fragmentation_in_percent DESC
OPTION (RECOMPILE);

This query specifically targets indexes within the current database (DB_ID()) and filters for those with more than 2500 pages, as smaller indexes are less likely to benefit from defragmentation. The results are ordered by the average fragmentation percentage in descending order, making it easy to see your most fragmented indexes right at the top. Understanding and managing index fragmentation is a key aspect of database performance tuning!