http://goo.gl/EmKxy0

Home > ASE > Calculating Table & Index Usage in ASE

Calculating Table & Index Usage in ASE

October 17th, 2012 Leave a comment Go to comments

sysindexes Contains

  • one row for each clustered index ( ie no row for table if CI is available)
  • one row for each nonclustered index,
  • one row for each table that has no clustered index,
  • one row for each table that contains text or image columns.
  • one row for each function-based index or index created on a computed column.

For Caluluating the size for each index and table we can use below table

First Row (indid >1) : For NCI , we will calculate index and reserved usage as below , it will not contain table size.
Second Row (indid=0) : For a table only, we can calculate usage as specified in below table
Third Row (indid =1) : As we know, CI resides tigtly couple with table so its size will also include table size.

 

indid Data Pages Index Page Reserved Pages
>1(NCI) data_pages(db_id(), tabid,indid) reserved_pages(db_id(), tabid, indid)
0(TBL) data_pages(db_id(), tabid,indid) reserved_pages(db_id(), tabid, indid)
1(CI) data_pages(db_id(), tabid,0) data_pages(db_id(), tabid,1) reserved_pages(db_id(), tabid, indid)+reserved_pages(db_id(), tabid, 0)

For calulating the Table size

1. If the table has CI

Add All NCI usages for each columns with CI usages i.e. Calulate row 1st for all NCI and add all with  3 rd row .

2. If the table does not has CI :

Add All NCI usages for each columns with Table Usage i.e Calulate 1st row for all NCI and add all with 2nd row.

Check the Sybase Wiki @ sybasewiki.com
Categories: ASE Tags:
  1. No comments yet.
  1. No trackbacks yet.