size of a table and its indexes

Samuel

unread,

Dec 22, 2008, 1:45:30 PM12/22/08

to

hello

Honestly the output of sp_spaceused is not clear and
confusing.

how to get the size of the table from this output?
how to get the size of the indexes from this output?
what is index_size in the last line? it is < (total indexes
size column)
how to get the 10 largest tables? 10 largest indexes?

thank you very much.

sp_spaceused 'MY_TABLE',1
go
index_name size reserved unused
------------------- ---------- ---------- ----------
MY_TABLE 0 KB 105952 KB 10148 KB
MY_TABLE_INDX 26488 KB 27952 KB 1464 KB
tMY_TABLE_INDX 46839048 K 49802000 K 2962952 KB

(1 row affected)
name rowtotal reserved data
index_size unused
---------------- ----------- ---------------
--------------- --------------- ---------------
MY_TABLE 1138318 49935904 KB 95804 KB
46865536 KB 2974564 KB

Bret Halford [Sybase]

unread,

Dec 22, 2008, 3:02:34 PM12/22/08

to

The bottom line is the summary for the entire table. The total amount
of space allocated to this table is 49935904 KB, of which 1138318 KB is
not currently being used. (space is allocated in 8-page extents, a page
is used if anything is being stored on it, so you can have extents with
1 used page and 7 unused, etc.)

The table has only one real index, "MY_TABLE_INDX". It has 27952 KB
reserved, of which 26488 is used and 1464 is unused. ("(used) size" +
"unused" = "total reserved"

TEXT/IMAGE data is stored under index-id (indid) 255. That is the
tMY_TABLE_INDX line.

The index_size value on the last line is the sum of the "used" values
for all indexes including the text/image columns.

To get the 10 largest tables, use a query something like

select
top 10
object_name(id) as "table_name",
reserved_pages(db_id(), id)
from
sysindexes
where
indid in (0,1)
order by
reserved_pages(db_id(),id)
desc
go

To get the 10 largest indexes, excluding text/image:

select
top 10
object_name(id) as "table_name",
name as "index_name",
reserved_pages(db_id(), id, indid)
from
sysindexes
where
-- exclude heap data (0) and text/image (255)
indid between 1 and 254
order by
reserved_pages(db_id(), id, indid)

desc
go

hero...@hotmail.com

unread,

Dec 31, 2008, 3:48:24 AM12/31/08

to

> go- 隐藏被引用文字 -
>
> - 显示引用的文字 -

Hi, which version has function 'reserved_pages', I got an error on my
envrioment.

Msg 14216, Level 16, State 1:
Server 'S01ABC1', Line 1:
Function 'reserved_pages' not found. If this is a SQLJ function, use
sp_help to
check whether the object exists (sp_help may produce a large amount of
output).

Carl Kayser

unread,

Dec 31, 2008, 6:21:59 AM12/31/08

to

<hero...@hotmail.com> wrote in message
news:23bd3b52-01c9-4308...@n41g2000yqh.googlegroups.com...

Brets code appears to be for 15.x and I suspect that you are on an earlier
version. (Suggestion: always provide the server version.) Pre-15.x would
use "reserved_pgs (id,ioampg)". However, I don't see an indid argument (nor
a dbid argument) for reserved_pgs ().

Mark A. Parsons

unread,

Dec 31, 2008, 10:08:33 AM12/31/08

to

Several of the space (as well as the row count) related functions were changed in ASE 15.x.

The 'changes' consist of a) different spelling and b) different input parameters. Details can be found in the New
Features and Reference manuals.

Derek Asirvadem

unread,

Jan 1, 2009, 1:00:32 AM1/1/09

to

> On 2008-12-31 22:21:59 +1100, "Carl Kayser" <kays...@bls.gov> said:
>
> Brets code appears to be for 15.x and I suspect that you are on an earlier
> version. (Suggestion: always provide the server version.)

Yes, yes, and yes.

Bret's code is an excellent starting point, but I doubt he was
providing something that works in all conditions, or that is intended
to handle the data/index issues. One has to understand the query and
the sysobjects/sysindexes structures.

> Pre-15.x would use "reserved_pgs (id,ioampg)". However, I don't see
> an indid argument (nor
> a dbid argument) for reserved_pgs ().

For 15.0:
select ... reserved_pages (dbid, id [,indid [,ptnid] ] ) from sysindexes

For pre- 15.0:
select ... reserved_pgs (id, { doampg | ioampg } ) from
dbname..sysindexes where indid = xyz

Choose indid 0 or 1 and Doampg to get reserved pages for DATA
Choose indid > 0 and Ioampg to get reserved pages for each INDEX

Remember indid = 1 is a true APL/CI (DPL or DRL "clustered" indices are
not clustered as term existed before their advent and meant something
rather specific; this is reflected in the fact that such indids are NOT
1). The 15.0 function may look "cleaner" but it is not, you still have
to mess with the returns (ala pre-15.0) to separate the data/index
values.
--
Cheers
Derek
Senior Sybase DBA / Information Architect
Copyright © 2008 Software Gems Pty Ltd
--
Tired of databases that are more cost than benefit ? Wondering why you
cannot get Sybase performance from Sybase ? Find out

Samuel

unread,

Jan 14, 2009, 9:32:20 AM1/14/09

to

hi and thank you all.

I am using 12.5.4
can we translate the sql query from 15 to 12.5.4?

thanks

Sherlock, Kevin [TeamSybase]

unread,

Jan 14, 2009, 9:38:01 AM1/14/09

to

That's what Derek meant by "Pre-15.x" below. Follow that line of code and
his instructions.

<Samuel> wrote in message news:496df774.431...@sybase.com...

thanks