Querying index sizes

Question

Querying index sizes

Adam Bean

One Orange Chip

Points: 26631
More actions
March 10, 2008 at 3:05 pm

#121057

Ok, I'm creating a couple custom proc's (one for 2000, one for 2005+), and I'm trying to figure out once and for all, how SQL determines the size of an index.
2005 changed quite a bit in terms of which tables to hit (sys.dm_db_partition_stats, sys.allocation_units, etc.) so before I get to that one, I'd like to finish the 2000 version first.
Non-Clustered Index = sysindexes.used * PageSize (v.low / 1024 FROM master..spt_values v WHERE v.number=1 AND v.type='E') [basically, 8]
This value seems to match up perfectly with sys.sp_MSindexspace, is the above formula correct?
Clustered Index ... this is where I'm having some difficulties. The output of sp_MSindexspace has a column that says "Size excludes actual data", but I want to determine the full size of the index on disk. So would I be correct in assuming that I'd simply want to take sysindexes.dpages * PageSize (see above for pagesize value)?
Yet in M$'s proc, they subtract used pages from dpages and then subtract the sum of used from all of sysobjects based on this index. I don't follow their logic at all in that ...
Anyways, long story short, what is the correct way to query index size in 2000? I'll be back shortly with my 2005 query ... which is almost done.
Thanks

Viewing 15 posts - 1 through 15 (of 26 total)

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

Shouldn't the Clustered index size match the table size since the clustered index is the data at the leaf level?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Adam Bean One Orange Chip Points: 26631 More actions · Answer 2

Jack Corbett (3/10/2008)
Shouldn't the Clustered index size match the table size since the clustered index is the data at the leaf level?

That's why I was thinking that should I simply take pages * pagesize? Yet as stated, M$ has a different way of doing it ... but they also state that "size excludes actual data". So I'm trying to determine what the proper way of querying the size would be.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

It sounds like MS is showing the size of the non-leaf levels of the index which should be a relatively low number according to Kalen Delaney in Inside Sql Server 2000 (she uses 1% of the data size as a guideline).

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Adam Bean One Orange Chip Points: 26631 More actions · Answer 4

So would it in fact be dpages * pagesize?

In 2k5, I took a different approach, and ended up with the same results as the M$ proc ...

This is what I have so far for each version ...

2000

SELECT

o.[name]AS [TableName]

,i.[name]AS [IndexName]

,CASE i.[indid]

WHEN 0 THEN 'HEAP'

WHEN 1 THEN 'CLUSTERED'

ELSE'NON CLUSTERED'

ENDAS [IndexType]

,CASE

WHEN i.indid > 1 AND i.indid < 255 THEN i.used * 8

WHEN i.indid = 1 THEN i.dpages * 8

ENDAS [IndexSize]

FROM sysobjects o

INNER JOIN sysindexes i

ON o.[id] = i.[id]

WHERE o.[type] = 'U'

AND i.[indid] > 0

AND i.name not like '_WA%'

ORDER BY 2,4

2005

SELECT

t.[name]AS [TableName]

,i.[name]AS [IndexName]

,i.[type_desc]AS [IndexType]

,CASE

WHEN i.[index_id] > 1 AND i.[index_id] < 255 THEN a.[used_pages] * 8

WHEN i.[index_id] = 1 THEN a.[data_pages] * 8

ENDAS [IndexSize]

FROM sys.tables t

INNER JOIN sys.indexes i

ON i.[object_id] = t.[object_id]

INNER JOIN sys.partitions p

ON p.object_id = i.object_id

AND p.index_id = i.index_id

JOIN sys.allocation_units a

ON a.container_id = p.partition_id

WHERE i.[index_id] > 0

ORDER BY 2,4

Jack Corbett SSC Guru Points: 184394 More actions · Answer 5

Based on the reading I have done that seems like it would be correct. I wouldn't guarantee my accuracy though. Have you compared it to the results from sp_spaceused?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Adam Bean One Orange Chip Points: 26631 More actions · Answer 6

sp_spaceused sums up the value for the whole value of the tables indexes, and as stated, does some different logic for clustered indexes (same as sys.sp_msindexsize - which both "exclude actual data size") ... so I'm not sure which way is correct at this point.

Jeff Moden SSC Guru Points: 1004436 More actions · Answer 7

Adam Bean (3/10/2008)
Ok, I'm creating a couple custom proc's (one for 2000, one for 2005+), and I'm trying to figure out once and for all, how SQL determines the size of an index.

If you really want to know, study the code for sp_SpaceUsed. Like Ragu, "It's in there!". 😉

--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)

Adam Bean One Orange Chip Points: 26631 More actions · Answer 8

Jeff Moden (3/11/2008)
Adam Bean (3/10/2008)
Ok, I'm creating a couple custom proc's (one for 2000, one for 2005+), and I'm trying to figure out once and for all, how SQL determines the size of an index.
If you really want to know, study the code for sp_SpaceUsed. Like Ragu, "It's in there!". 😉

Right, as stated, I have looked at the code for this proc and the other ... but ... it does not appear that they are calculating the 'actual' size of a clustered index ... thus my dilemma, who is right?

Matt Stanford SSC Rookie Points: 43 More actions · Answer 9

Isn't a clustered index just physically ordering a table based on the key fields? Does this actually add any size to the table?

Jack Corbett SSC Guru Points: 184394 More actions · Answer 10

Matt Stanford (3/11/2008)
Isn't a clustered index just physically ordering a table based on the key fields? Does this actually add any size to the table?

It does add some size for the index pages above the leaf level. This is minimal. As in an earlier post, Kalen Delaney in Inside Sql Server 2000 estimates this to to be less than 1% of the size of the table.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Adam Bean One Orange Chip Points: 26631 More actions · Answer 11

Jack Corbett (3/11/2008)
Matt Stanford (3/11/2008)
Isn't a clustered index just physically ordering a table based on the key fields? Does this actually add any size to the table?
It does add some size for the index pages above the leaf level. This is minimal. As in an earlier post, Kalen Delaney in Inside Sql Server 2000 estimates this to to be less than 1% of the size of the table.

Is there any documented material from M$ about how to calculate the actual size then?

Jeff Moden SSC Guru Points: 1004436 More actions · Answer 12

Yeay... sorry Adam... I see that now.

--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)

Matt Stanford SSC Rookie Points: 43 More actions · Answer 13

Adam Bean (3/11/2008)
Jack Corbett (3/11/2008)
Matt Stanford (3/11/2008)
Isn't a clustered index just physically ordering a table based on the key fields? Does this actually add any size to the table?
It does add some size for the index pages above the leaf level. This is minimal. As in an earlier post, Kalen Delaney in Inside Sql Server 2000 estimates this to to be less than 1% of the size of the table.
Is there any documented material from M$ about how to calculate the actual size then?

So are you trying to calculate the size of the table plus the extra size that the clustered index adds? or do you want to just calculate what amount of space the clustered index adds?

Or am I missing something?

Adam Bean One Orange Chip Points: 26631 More actions · Answer 14

Adam Bean

One Orange Chip

Points: 26631

March 11, 2008 at 9:32 am

#788069

Just the size of the actual clustered index.