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
      , 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
Advertisements

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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

 
%d bloggers like this: