Identifying and/or calculating individual index sizes

Question

Identifying and/or calculating individual index sizes

UNCRushFan

SSC Veteran

Points: 281
More actions
February 26, 2009 at 1:20 pm

#200820

I'm a relatively new DBA. I have a table that is about 300GB in size and it has 26 different indexes on it (the DB is a monster; 1.27 TB). I am building a procedure that online re-indexes individual indexes during off-peak hours and after the size of the last log backup drops below a certain level. Our DB cannot be taken offline.
In case the obvious question jumps out: yes - I have plans in the works to partition and eventually split this table up, but it's a long way off from soon (pending a SAN upgrade).
I was wondering if anyone knew of a good way to EXACTLY calculate the size of an index through code? I can "guesstimate" it with sp_spaceused, and some algebra, but I'd prefer to do it through code if possible. Wish they had 'sp_foreachindex'.
Thanks much,
Tim
PS: I have read BOL and http://msdn.microsoft.com/en-us/library/ms190620.aspx calculating the size of a non-clustered index. I was hoping a sage DBA would be able to suggest a simpler way.
Membrane.Inside("Insane");

Viewing 4 posts - 1 through 3 (of 3 total)

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

Jeff Moden SSC Guru Points: 1004425 More actions · Answer 1

Keeping in mind that a "Page" is 8KB, you could try this and get some pretty good information about the table and all of it's indexes... all at once...

DBCC SHOWCONTIG (yourtablename) WITH ALL_INDEXES, TABLERESULTS

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

UNCRushFan SSC Veteran Points: 281 More actions · Answer 2

Great stuff Jeff; thanks!

Membrane.Inside("Insane");

Jeff Moden SSC Guru Points: 1004425 More actions · Answer 3

You're welcome, Tim... thanks for taking the time to post the feedback.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)