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_name | size | reserved | unused |
| PK_TAB | 2737456 KB | 2737728 KB | 272 KB |
| TAB_IDX1 | 678632 KB | 681832 KB | 3200 KB |
| TAB_IDX2 | 650912 KB | 654168 KB | 3256 KB |
| IDX_3 | 400568 KB | 401216 KB | 648 KB |
| TAB_IDX_REAL | 431936 KB | 433088 KB | 1152 KB |
| IDX_TABAVAIL | 432240 KB | 432832 KB | 592 KB |
| TAB_IDX4 | 387872 KB | 388160 KB | 288 KB |
| TAB_IDX_SENT | 624096 KB | 624592 KB | 496 KB |
| IDX_TAB_TEMP_VALUE | 395512 KB | 395880 KB | 368 KB |
| IDX_TAB_TEMP_TRANS | 395528 KB | 395880 KB | 352 KB |
| TAB_RIPD | 2971136 KB | 2971816 KB | 680 KB |
| IDX_TAB_TS | 607056 KB | 607352 KB | 296 KB |
| TAB_IDX9 | 429576 KB | 436112 KB | 6536 KB |
| TAB_IDX6 | 387888 KB | 388448 KB | 560 KB |
| IDX_TAB_INT_BR | 583128 KB | 620184 KB | 37056 KB |
| IDX_TAB_PST | 386992 KB | 387168 KB | 176 KB |
| TAB_IDX10 | 553184 KB | 559824 KB | 6640 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
I have covered this command in one of my video related to Index
Please leave your comment if you liked this post or helped you.