Help With Index Size

Question

Post reply

Help With Index Size

tgarland

Hall of Fame

Points: 3367
More actions
February 10, 2009 at 9:55 am

#128678

I have created the following statement to retrieve a list of unused indexes. I now want to add a column that has the size of the index so I know how much wasted space it is taking up. I have searched but have have been unable to find in information on where to find the index size. I would appreaciate any help you could provide. Thanks in advance.
select c.name, b.name, c.create_date, c.modify_date, a.user_seeks, a.user_scans,
a.user_lookups, a.user_updates, a.last_user_seek, a.last_user_scan,
a.last_user_lookup, a.last_user_update
from sys.dm_db_index_usage_stats a
inner join sys.indexes b
on a.object_id = b.object_id and a.index_id = b.index_id
inner join sys.tables c
on b.object_id = c.object_id
where a.index_id <> 0
and (a.user_seeks + a.user_scans + a.user_lookups) = 0

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 1

Take a look at the output of:

sp_helptext sp_spaceused

It should get you there pretty quickly.

tgarland Hall of Fame Points: 3367 More actions · Answer 2

Is the index size contained anywhere in a table or view that I can join to the query?

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 3

Yes, and the right ones and how to get index only information is contained in the output of the query I provided above. Did you actually run the query and look at what it provided you?

tgarland Hall of Fame Points: 3367 More actions · Answer 4

Sorry for the confusion. I misunderstood your post. I thought you wanted me to execute the two separately. I will take a look at the code behind the "sp_spaceused" stored procedure now. Thanks.

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 5

You will get it from sys.dm_db_partition_stats

select * from sys.dm_db_partition_stats

where object_id='Enter the ID here'

and Index_id='Enter the ID here'

MJ

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 6

MANU (2/11/2009)
You will get it from sys.dm_db_partition_stats
select * from sys.dm_db_partition_stats
where object_id='Enter the ID here'
and Index_id='Enter the ID here'
MJ

Look at the output of sp_helptext for sp_spaceused, and you will see that it is much more complex than that. It's not just a simple query answer.

Mike - CI SSCarpal Tunnel Points: 4146 More actions · Answer 7

Jonathan Kehayias (2/11/2009)
MANU (2/11/2009)
You will get it from sys.dm_db_partition_stats
select * from sys.dm_db_partition_stats
where object_id='Enter the ID here'
and Index_id='Enter the ID here'
MJ
Look at the output of sp_helptext for sp_spaceused, and you will see that it is much more complex than that. It's not just a simple query answer.

Just to say, I completely agree with the above that if you want to get the data completely accurate that it will take more than a simple query.

However, if you are just looking to get approximations (for data savings, things of that nature) I have been using this since SQL2000 and it still works for 2008 (thanks to the view of sysindexes that they have kept around).

select object_name (id) as tablename,

name,

reserved * 8.192 / 1024. as Reserved_MB,

dpages * 8.192 / 1024. as PagesUsed_MB

from sysindexes

It is not perfect and it is not 100% accurate but you will find that it comes in almost exactly with sp_spaceused and I have always found it to be "good enough" for the sorts of things I need this information for.