How to get the disk space occupied by all the tables?

[Former Member]

Hi All,

We are in the process of archiving table data and would like to know how much disk space each table consumed. With M_CS_TABLES I can get how much main memory each table is consuming but not the disk space.

Is there any table/way to get the disk space consumed by all tables?

I appreciate your help.

Thanks,

Bharath

Message was edited by: Tom Flanagan

[lbreddemann · Accepted solution]

You could use something like this:

SELECT DISK_SIZE from

M_TABLE_PERSISTENCE_STATISTICS

WHERE schema_name = ? and table_name = ?

- Lars

[Former Member · Accepted solution]

Hi Lars,

Thanks for the quick response. It helped.

SELECT A.SCHEMA_NAME,
    A.TABLE_NAME,DISK_SIZE/1000000000 AS DISK_SIZE_GB ,
    MEMORY_SIZE_IN_TOTAL/1000000000 AS MEMORY_SIZE_IN_TOTAL_IN_GB,
    MEMORY_SIZE_IN_MAIN/1000000000 AS MEMORY_SIZE_IN_MAIN_IN_GB,
    RECORD_COUNT
from
M_TABLE_PERSISTENCE_STATISTICS  A
INNER JOIN m_cs_tables B
ON A.SCHEMA_NAME =B.SCHEMA_NAME AND A.TABLE_NAME =B.TABLE_NAME
ORDER BY 3 DESC;

[Former Member · Accepted solution]

Hi Lars,

One more question, Why SELECT SUM(MEMORY_SIZE_IN_TOTAL) FROM M_CS_TABLES is not matching with Used memory in the System Monitor?

SELECT SUM(MEMORY_SIZE_IN_TOTAL) FROM M_CS_TABLES showing 141 GB where as Used memory in the System Monitor showing 519 GB.

Am I comparing 2 different values?

Thanks,

Bharath

[lbreddemann · Accepted solution]

Yes you are. Or at least from two different angles.

System Monitor looks kind of "top-down" on the memory allocator structure and reports the usage by allocator.

M_CS_TABLES looks "bottom-up" and reports what the table data structures report.

Also M_CS_TABLES is only covering the column store tables.

- Lars