How to Find Size of Index(es) in a Table
Sybase ASE  

How to Find Size of Index(es) in a Table

There could be many indexes on a table other than clustered index, as we are aware – one table can have only one clustered index however can have many non-clustered index. Ever wondered – why do we have only one clustered index and why not multiple like non-clustered one? This is for the simple reason that clustered index is nothing but the logical organization of data inside a table and we can arrange data only in one way. Clustered index doesn’t have any separate storage unlike non-clustered index. Non-clustered index is basically copy of those fields of a table on which this index is built.

At times, we need to find out the size of these indexes in a table in different units. Units could be in number of pages, KB, MB or GB. Let’s start with first approach on finding size of indexes of a table. We may use below command inside a database:

sp_spaceused Table_Name, 1

Let’s see how this command returns the result in the grid format:

index_namesizereservedunused
PK_TAB2737456 KB2737728 KB272 KB
TAB_IDX1678632 KB681832 KB3200 KB
TAB_IDX2650912 KB654168 KB3256 KB
IDX_3400568 KB401216 KB648 KB
TAB_IDX_REAL431936 KB433088 KB1152 KB
IDX_TABAVAIL432240 KB432832 KB592 KB
TAB_IDX4387872 KB388160 KB288 KB
TAB_IDX_SENT624096 KB624592 KB496 KB
IDX_TAB_TEMP_VALUE395512 KB395880 KB368 KB
IDX_TAB_TEMP_TRANS395528 KB395880 KB352 KB
TAB_RIPD2971136 KB2971816 KB680 KB
IDX_TAB_TS607056 KB607352 KB296 KB
TAB_IDX9429576 KB436112 KB6536 KB
TAB_IDX6387888 KB388448 KB560 KB
IDX_TAB_INT_BR583128 KB620184 KB37056 KB
IDX_TAB_PST386992 KB387168 KB176 KB
TAB_IDX10553184 KB559824 KB6640 KB

Here, as you can see the sizes are in KB. We can convert them into MB and GB by dividing 1024 respectively.

Now, let’s see another method of finding size of index in a table:

sp_spaceusage ‘display using unit=MB’, ‘index’, ‘Table_Name.%’
go

Above will list out all the indexes and its sizes in MB for the table with name Table_Name.

sp_spaceusage ‘display using unit=GB’, ‘index’, ‘Table_Name.%’
go

Above will list out all the indexes and its sizes in GB for the table with name Table_Name.

sp_spaceusage ‘display using unit=pages’, ‘index’, ‘Table_Name.%’

Above will list out all the indexes and its sizes in pages for the table with name Table_Name.

Here, you can also specify any specific index name say index name is IDX_Customer_Name on Table Customer and we are interested to know size of this index in MB. we can execute below command:

sp_spaceusage ‘display using unit=MB’, ‘index’, ‘Customer.IDX_Customer_Name’
go

Leave A Comment