Microsoft – SQL Server – Index Size

Background

Microsoft does not make it very easy to deduce index sizes.  And, so one ends up wandering the trenches to discover ways of doing so:

Here are some ways that we have discovered:

To get a summary of data and index used by a particular object:

Syntax:
exec sp_spaceused <object-name>

Sample
exec sp_spaceused ‘contacts’

Find Index Sizes (sp_MSindexspace):


declare @indexStat TABLE
(
      [IndexID] smallint not null
    , [IndexName] sysname not null
    , [Size] int not null
    , [Comments] sysname null	  
)

insert into @indexStat	  
( [IndexID], [IndexName], [Size], [Comments])
exec sp_MSindexspace 'contacts'	

select *
from   @indexStat
where  (
          (IndexName not like '_WA_Sys_%')
        )

 

Find Index Sizes by Querying DMV  Table (sys.dm_db_partition_stats)


SELECT        
          OBJECT_NAME(tblIndex.object_id) as [objectName]
        , tblIndex.name
        , sum(tblPS.used_page_count ) as used_page_count
        , sum(tblPS.reserved_page_count) as reserved_page_count
        , sum(tblPS.used_page_count * tblSptValues.low) as usedPages
        , (sum(tblPS.used_page_count * tblSptValues.low) / 1024) as usedPagesInKB                         
 FROM sys.indexes tblIndex
          inner join sys.dm_db_partition_stats tblPS
		on  tblIndex.object_id = tblPS.object_id
	        and tblIndex.index_id = tblPS.index_id
	  cross apply master..spt_values tblSptValues
  where  (
                (tblSptValues.number = 1)
	    and (tblSptValues.type = 'E')
	  )
 and     (tblPS.object_id = object_id('dbo.contacts'))
 group by
	  tblIndex.object_id
	, tblIndex.index_id
	, tblIndex.name
 order by 
          object_name(tblIndex.object_id)
	, tblIndex.name



Leave a Reply

Please log in using one of these methods to post your comment:

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