Recently the ETL load times of my Datawarehouse are increasing and so I decided to check the Indexes status on the tables . I found some fragmentation on the indexes and also some Statistics that are not being updated regularly . I searched online and found this blog by Ben Nevarez(SQL Blog).
I decided to check the fragmentation levels of the Index after the ETL load is done daily on a regular basis. So I digged deep into the DMV’s and got this script below
select OBJECT_NAME(ps.object_id) as TableName ,si.Name ,ps.Avg_Fragmentation_in_percent ,STATS_DATE(ss.object_id,ss.stats_id) as LastUpdatedStatistics from sys.dm_db_index_physical_stats(DB_ID(DB_NAME()),null,null,null,null) ps join sysindexes si on ps.object_id = si.id and ps.index_id = si.indid left outer join sys.stats ss on ss.object_id = ps.object_id and ss.name = si.name where ps.avg_fragmentation_in_percent > 10 order by ps.avg_fragmentation_in_percent desc
I published the above query in my Reporting services suite of Operational Reports and check it daily morning. The report shows all the Indexes with more than 10% percent fragmentation along with Last updated Statistics time . If you want to go further.You may want to rebuild all the indexes that are above 20% fragmented in TSQL. If there is a better way of doing it please comment.
