You can often check the index defragmentation. It is useful if you start checking with the biggest table, because the biggest effect is there.
I like to check the indexes with the creation and modification date. If you rebuild the index your index modification date will be updated.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
USE UserDB -- with modifydate -order by rowcount GO SELECT s.[name] +'.'+t.[name] AS table_name ,i.NAME AS index_name ,index_type_desc ,ROUND(avg_fragmentation_in_percent,2) AS avg_fragmentation_in_percent ,record_count AS table_record_count ,create_date ,modify_date FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ips INNER JOIN sys.tables t on t.[object_id] = ips.[object_id] INNER JOIN sys.schemas s on t.[schema_id] = s.[schema_id] INNER JOIN sys.indexes i ON (ips.object_id = i.object_id) AND (ips.index_id = i.index_id) ORDER BY record_count desc, avg_fragmentation_in_percent DESC |
If you want to see for all database you can replace the
with
in the script.