2 Mayıs 2014 Cuma

SQL SERVER INDEX SIZE AND USAGE

 

SELECT object_name(i.object_id) as table_name

,COALESCE(i.name, space(0)) as index_name

,ps.partition_number

,ps.row_count

,Cast((ps.reserved_page_count * 8)/1024. as decimal(12,2)) as size_in_mb

,COALESCE(ius.user_seeks,0) as user_seeks

,COALESCE(ius.user_scans,0) as user_scans

,COALESCE(ius.user_lookups,0) as user_lookups

,i.type_desc

FROM sys.all_objects t

INNER JOIN sys.indexes i ON t.object_id = i.object_id

INNER JOIN sys.dm_db_partition_stats ps ON i.object_id = ps.object_id AND i.index_id = ps.index_id

LEFT OUTER JOIN sys.dm_db_index_usage_stats ius ON ius.database_id = db_id() AND i.object_id = ius.object_id AND i.index_id = ius.index_id

ORDER BY object_name(i.object_id), i.name