Calculate total size of clustered and non-clustered index for database in SQL Server 2008/2005

It is often useful to keep an eye on the size of your database, indexes always plays an important role in SQL Server databases so it is really good to keep an eye on that as well. We used to do rebuild, defrag indexes sooner or later but have you ever think about how much space those indexes consumes? Let us try to find out with “SysIndexes”.

If you observe SysIndexes table than you came to know that it has lots of crucial information about clustered and non-clustered indexes. How can you differentiate clustered and non-clustered indexes from “SysIndexes” ? well, it is really very easy, Clustered index always have “1” in IndID field and non-clustered index always have >1 in IndID field.

You might even know that data used to get stored in page of 8KB in SQL Server so we can calculate those pages of 8KB (8192 byte) to get values in MB.

Now let us have a look at simple yet useful T-SQL statement.

–for non-clustered index

select sum(cast(reserved as bigint))*8192/(1024*1024) AS TotalMegabytes_Non_Clust

from sysindexes

where indid > 1;

go

–for clustered index

select sum(cast(reserved as bigint))*8192/(1024*1024) AS TotalMegabytes_Clust

from sysindexes

where indid = 1;

go

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah