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
where indid > 1;
–for clustered index
select sum(cast(reserved as bigint))*8192/(1024*1024) AS TotalMegabytes_Clust
where indid = 1;
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah