Microsoft – SQL Server – Index Size


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:

exec sp_spaceused <object-name>

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)

          OBJECT_NAME(tblIndex.object_id) as [objectName]
        , 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.index_id
 order by 

Leave a Reply

Please log in using one of these methods to post your comment: 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