Calculate Index Size problems

[Jay]

Hi,

I'm trying to calculate the number of pages a index takes up, but keep
coming up short. The table I'm woring with was just created (i also altered
it to cluster),


oncheck output


TBLspace Report for test:pen.acc_bal

Physical Address a009a4
Creation date 07/11/03 09:50:05
TBLspace Flags 801 Page Locking
TBLspace use 4 bit bit-maps
Maximum row size 71
Number of special columns 0
Number of keys 1
Number of extents 2
Current serial value 1
First extent size 8
Next extent size 8
Number of pages allocated 2120
Number of pages used 2117
Number of data pages 1827
Number of rows 98619
Partition partnum 5243161
Partition lockid 5243161


Extents
Logical Page Physical Page Size
0 a250af 1280
1280 a255b7 840

TBLspace Usage Report for test:pen.acc_bal


Type Pages Empty Semi-Full Full Very-Full
---------------- ---------- ---------- ---------- ---------- ----------
Free 5
Bit-Map 1
Index 287
Data (Home) 1827
----------
Total Pages 2120

Unused Space Summary

Unused data slots 39
Unused bytes per data page 18
Total unused bytes in data pages 32886


Home Data Page Version Summary

Version Count

0 (current) 1827

Index Usage Report for index accbal_idx on test:pen.acc_bal


Average Average
Level Total No. Keys Free Bytes
----- -------- -------- ----------
1 1 286 1084
2 286 344 1827
----- -------- -------- ----------
Total 287 344 1824

I have used the following to calculate the index size

1.. Key value = (SUM(columns) + 4) * 1.5
2.. propunique = nrows / nunique
3.. entry size =(length of row pointer * average number of rows per unique
index) + key value
4.. # of index entries per page = 4068 / entry size
5.. Estimated # of leave pages = # of index entries per page / nunique
6.. Add 5 percent for branch and root nodes

1.. (SUM(4)+4)*1.5 = 12
2.. 98619 / 18359 = 5.37 -- got nunique from sysindexes
3.. (5 * 5.37) + 12 = 38.85
4.. 4068 / 38.85 = 104.71
5.. 18359 / 105 = 174
6.. 174 * 1.5 = 183

183 is quite off from onchecks 287. Can anyone point me in the right
direction?

Thank you



J

[Jay]

"Jay" <remove:jay@td.ca> wrote in message
news:X_zPa.9672$ru2.1027767@news20.bellglobal.com. ..

>
>
> Hi,
>
> I'm trying to calculate the number of pages a index takes up, but keep
> coming up short. The table I'm woring with was just created (i also

altered

> it to cluster),
>
>
> oncheck output
>
>
> TBLspace Report for test:pen.acc_bal
>
> Physical Address a009a4
> Creation date 07/11/03 09:50:05
> TBLspace Flags 801 Page Locking
> TBLspace use 4 bit bit-maps
> Maximum row size 71
> Number of special columns 0
> Number of keys 1
> Number of extents 2
> Current serial value 1
> First extent size 8
> Next extent size 8
> Number of pages allocated 2120
> Number of pages used 2117
> Number of data pages 1827
> Number of rows 98619
> Partition partnum 5243161
> Partition lockid 5243161
>
>
> Extents
> Logical Page Physical Page Size
> 0 a250af 1280
> 1280 a255b7 840
>
> TBLspace Usage Report for test:pen.acc_bal
>
>
> Type Pages Empty Semi-Full Full

Very-Full

> ---------------- ---------- ---------- ---------- ---------- ---------

-

> Free 5
> Bit-Map 1
> Index 287
> Data (Home) 1827
> ----------
> Total Pages 2120
>
> Unused Space Summary
>
> Unused data slots 39
> Unused bytes per data page 18
> Total unused bytes in data pages 32886
>
>
> Home Data Page Version Summary
>
> Version Count
>
> 0 (current) 1827
>
> Index Usage Report for index accbal_idx on test:pen.acc_bal
>
>
> Average Average
> Level Total No. Keys Free Bytes
> ----- -------- -------- ----------
> 1 1 286 1084
> 2 286 344 1827
> ----- -------- -------- ----------
> Total 287 344 1824
>
> I have used the following to calculate the index size
>
> 1.. Key value = (SUM(columns) + 4) * 1.5
> 2.. propunique = nrows / nunique
> 3.. entry size =(length of row pointer * average number of rows per

unique

> index) + key value
> 4.. # of index entries per page = 4068 / entry size
> 5.. Estimated # of leave pages = # of index entries per page / nunique
> 6.. Add 5 percent for branch and root nodes
>
> 1.. (SUM(4)+4)*1.5 = 12
> 2.. 98619 / 18359 = 5.37 -- got nunique from sysindexes
> 3.. (5 * 5.37) + 12 = 38.85
> 4.. 4068 / 38.85 = 104.71
> 5.. 18359 / 105 = 174
> 6.. 174 * 1.5 = 183
>
> 183 is quite off from onchecks 287. Can anyone point me in the right
> direction?
>
> Thank you
>
>
>
> J

Oops, i made a mistake in step 2, should have been the other way around.
Still get a wrong number of 331 pages though.

[Neil Truby]

I don't mean to be unhelpful - because, straight up, I don't know the
answer.
But I'm really interested to know why anyone ever bothers to do this.

I can remember in every DBMS course I've ever done we've done endless
tedious space calculation exercises. And never used it - not since the
invention of disks bigger than 512M anyway.

Just guess and then triple it - that's my advice!

"Jay" <remove:jay@td.ca> wrote in message
news:X_zPa.9672$ru2.1027767@news20.bellglobal.com. ..

>
>
> Hi,
>
> I'm trying to calculate the number of pages a index takes up, but keep
> coming up short. The table I'm woring with was just created (i also

altered

> it to cluster),
>
>
> oncheck output
>
>
> TBLspace Report for test:pen.acc_bal
>
> Physical Address a009a4
> Creation date 07/11/03 09:50:05
> TBLspace Flags 801 Page Locking
> TBLspace use 4 bit bit-maps
> Maximum row size 71
> Number of special columns 0
> Number of keys 1
> Number of extents 2
> Current serial value 1
> First extent size 8
> Next extent size 8
> Number of pages allocated 2120
> Number of pages used 2117
> Number of data pages 1827
> Number of rows 98619
> Partition partnum 5243161
> Partition lockid 5243161
>
>
> Extents
> Logical Page Physical Page Size
> 0 a250af 1280
> 1280 a255b7 840
>
> TBLspace Usage Report for test:pen.acc_bal
>
>
> Type Pages Empty Semi-Full Full

Very-Full

> ---------------- ---------- ---------- ---------- ---------- ---------

-

> Free 5
> Bit-Map 1
> Index 287
> Data (Home) 1827
> ----------
> Total Pages 2120
>
> Unused Space Summary
>
> Unused data slots 39
> Unused bytes per data page 18
> Total unused bytes in data pages 32886
>
>
> Home Data Page Version Summary
>
> Version Count
>
> 0 (current) 1827
>
> Index Usage Report for index accbal_idx on test:pen.acc_bal
>
>
> Average Average
> Level Total No. Keys Free Bytes
> ----- -------- -------- ----------
> 1 1 286 1084
> 2 286 344 1827
> ----- -------- -------- ----------
> Total 287 344 1824
>
> I have used the following to calculate the index size
>
> 1.. Key value = (SUM(columns) + 4) * 1.5
> 2.. propunique = nrows / nunique
> 3.. entry size =(length of row pointer * average number of rows per

unique

> index) + key value
> 4.. # of index entries per page = 4068 / entry size
> 5.. Estimated # of leave pages = # of index entries per page / nunique
> 6.. Add 5 percent for branch and root nodes
>
> 1.. (SUM(4)+4)*1.5 = 12
> 2.. 98619 / 18359 = 5.37 -- got nunique from sysindexes
> 3.. (5 * 5.37) + 12 = 38.85
> 4.. 4068 / 38.85 = 104.71
> 5.. 18359 / 105 = 174
> 6.. 174 * 1.5 = 183
>
> 183 is quite off from onchecks 287. Can anyone point me in the right
> direction?
>
> Thank you
>
>
>
> J
>
>

[Jay]

"Neil Truby" <neil.truby@ardenta.com> wrote in message
news:bems0s$6ukns$1@ID-162943.news.uni-berlin.de...

> I don't mean to be unhelpful - because, straight up, I don't know the
> answer.
> But I'm really interested to know why anyone ever bothers to do this.
>
> I can remember in every DBMS course I've ever done we've done endless
> tedious space calculation exercises. And never used it - not since the
> invention of disks bigger than 512M anyway.
>
> Just guess and then triple it - that's my advice!
>

Hi Neil,

The reason why I am trying to calculate the index size is because one of our
production servers has serious extent interleaving. I am planning to unload
/ load all databases within the instance, and include extent sizing. I need
to know how much disk space to expect after the extent allocation. Becuase
these databases have never been rebuilt, I expect there will be space to be
reclaimed after the unload / load. I just want to know how much space will
be reclaimed (becuase of the unload / load) or additional space to be taken
up after the NEXT EXTENT kicks in (since I only have 6 gigs left).

[David Williams]

"Jay" <remove:jay@td.ca> wrote in message
news:AdDPa.9770$ru2.1056797@news20.bellglobal.com. ..

>

> The reason why I am trying to calculate the index size is because one of

our

> production servers has serious extent interleaving. I am planning to

unload

> / load all databases within the instance, and include extent sizing. I

need

> to know how much disk space to expect after the extent allocation.

Becuase

> these databases have never been rebuilt, I expect there will be space to

be

> reclaimed after the unload / load. I just want to know how much space

will

> be reclaimed (becuase of the unload / load) or additional space to be

taken

> up after the NEXT EXTENT kicks in (since I only have 6 gigs left).
>

unload the table to a text file and rebuild it on a test server. Then you
get the exact size.

>
>

[Jay]

"David Williams" <djw@smooth1.fsnet.co.uk> wrote in message
news:benncu$iad$1@newsg4.svr.pol.co.uk...

>
> "Jay" <remove:jay@td.ca> wrote in message
> news:AdDPa.9770$ru2.1056797@news20.bellglobal.com. ..

> >

>

> > The reason why I am trying to calculate the index size is because one of

> our

> > production servers has serious extent interleaving. I am planning to

> unload

> > / load all databases within the instance, and include extent sizing. I

> need

> > to know how much disk space to expect after the extent allocation.

> Becuase

> > these databases have never been rebuilt, I expect there will be space to

> be

> > reclaimed after the unload / load. I just want to know how much space

> will

> > be reclaimed (becuase of the unload / load) or additional space to be

> taken

> > up after the NEXT EXTENT kicks in (since I only have 6 gigs left).
> >

>
> unload the table to a text file and rebuild it on a test server. Then

you

> get the exact size.

I was hoping that someone would help me correct my calculation. Rebuilding
250 tables in 10 databases is not feasible, a simple query and calculation
(like how the table size ca be calculated entries/(4068/(rowsize+4)) would
be much more helpful. Can anyone help?