Simon's SQL

SQL,DBA,tuning,Trouble Shooting,Performance

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
      , i.index_id
      , 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

One Response to “object size each table and index (update for partitioning table)”

  1. Simon Cho said

    This is very good to check size of partitioning table for each partitioning group and all other indexes.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s