What is the best way to determine which tables are the culprits in a large (disk size) database.

We put out the same basic structure of tables to our customers and some databases end up much bigger than others. My initial attempts to determine where the size is used were to check the row counts for each table, but I do not think I am doing that as efficiently as I could, going table by table. But a higher row count does not necessarily mean more space on the disk if another lower row count table is storing more data.

I also checked in Sybase Central the Fragmentation for the tables with the most main pages, as well as the %File column of Page Usage which I assume means the % of the database file that table uses.

So I thought I would ask if there is a more efficient, more accurate way to tell which tables were taking up the most disk space within the database file.

asked 26 Jul '11, 09:56

Siger%20Matt's gravatar image

Siger Matt
3.2k516998
accept rate: 13%


select table_page_count+ext_page_count as totalpages, table_name  from systable order by totalpages desc

The table using the most db pages will be on top ;-)

permanent link

answered 26 Jul '11, 11:39

Martin's gravatar image

Martin
8.6k119151237
accept rate: 14%

dbinfo -u is a convenient way to get the information from the command line. It's probably equivalent to the information displayed in Sybase Central. Builds 2630 and later of 12.0.0 as well as all versions of 12.0.1 gather this information much more quickly when it is not already in cache.

permanent link

answered 26 Jul '11, 10:02

John%20Smirnios's gravatar image

John Smirnios
9.0k377114
accept rate: 39%

edited 26 Jul '11, 10:05

Foxhound shows the amount of disk space used by each table: data pages, extension pages and indexes: How much space?

It also shows a list of the largest tables and materialized views: What's unusual about this database?

The numbers shown are for disk space, not column size; for example, the bytes-per-row number is the total disk space used by the table and all its indexes divided by the row count, and is larger (sometimes much larger) than the bytes-of-column-data-per-row... the latter number might have some use but I haven't found one yet :)

Foxhound is designed from the ground up to be the database consultant's friend when first encountering an existing database.

permanent link

answered 26 Jul '11, 12:35

Breck%20Carter's gravatar image

Breck Carter
27.0k444614889
accept rate: 21%

edited 26 Jul '11, 12:36

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×248
×10
×8
×1

question asked: 26 Jul '11, 09:56

question was seen: 4,255 times

last updated: 26 Jul '11, 12:36