How to find the Index health status by running the query in SQL Server
Indexs created on the table of a database plays the most important role in the performance of queries returning the ouputs.
A well structured query will definetly use indexes to make the query run faster than it runs on Heap structure table.
Index health monitoring should be a weekly task for a DBA to maintain hassle free environment.
Check the index health status by running the below query:
Use DBNAME
SELECTOBJECT_NAME(i.object_id) AS TableName ,i.name AS TableIndexName ,
phystat.avg_fragmentation_in_percent FROM
sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') phystat inner JOIN sys.indexes i ON i.object_id = phystat.object_id AND i.index_id = phystat.index_id WHERE phystat.avg_fragmentation_in_percent > 10 AND phystat.avg_fragmentation_in_percent < 40
Above query return the output whose index fragmentation level is less than 40 percent.
These indexes will not impact much on the performance, But still we have to "Re-org" the above indexes.
Use DBNAME
GO
SELECTOBJECT_NAME(i.object_id) AS TableName ,i.name AS TableIndexName ,
phystat.avg_fragmentation_in_percent FROM
sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') phystat inner JOIN sys.indexes i ON i.object_id = phystat.object_id AND i.index_id = phystat.index_id WHERE phystat.avg_fragmentation_in_percent > 10 AND phystat.avg_fragmentation_in_percent > 40
Above query will help in findout the indexes whose fragmentation level is greater than 40
percent and these indexes will definetly impact the performance of a query.
Above indexes should be "Rebuild".
More detailed query:
SELECT db_name(a.database_id) [Db Name]
,a.index_id
,b.name
,a.avg_fragmentation_in_percent
,record_count,a.avg_fragment_size_in_pages,page_count,fragment_countFROM sys.dm_db_index_physical_stats (DB_ID(), null,NULL, NULL, 'DETAILED') AS a
JOIN sys.indexes AS b
ON a.object_id = b.object_id
AND a.index_id = b.index_id
where a.database_id = db_id()and
a.avg_fragmentation_in_percent>20/*and (OBJECT_NAME(b.object_id) like 'PX%' or OBJECT_NAME(b.object_id) like 'PM%')*/order by a.object_id
Thanks
