Simon's SQL

SQL,DBA,tuning,Trouble Shooting,Performance

Posts Tagged ‘index size’

object size each table and index (update for partitioning table)

Posted by Simon Cho on 08/04/2011

This query can check each table and index
And also, if it is partitioning table, it will show up partition # and each partition # size.

select FILEGROUP_NAME(au.data_space_id) as file_group
      , o.name
      , i.index_id
      , i.name as index_name
      , au.partition_number
      , au.size_MB
      , case when i.data_space_id > 65000 then 'Y' else 'N' end IsPartitionedTable
  from 	(
		select object_id, index_id, partition_number, data_space_id
			 , convert(numeric(10,2)
			 , SUM(in_row_data_page_count
				+ lob_used_page_count
				+ row_overflow_used_page_count) * 8/1024.0) size_MB 
		  from sys.allocation_units au
		  join sys.dm_db_partition_stats  ps
			on au.container_id = ps.partition_id and au.type=1
		 group by object_id, index_id, partition_number, data_space_id
		) au
  join sys.indexes i 
    on i.object_id = au.object_id and i.index_id = au.index_id
  join sys.all_objects o
    on i.[object_id] = o.[object_id] and o.type='U'
 order by 1,2,3,4

Posted in Common | Tagged: , , , | 1 Comment »