Query to return table sizes

Question

Question

Bradley Larson · Dec 8, 2021

I've been accessing Cache tables from a developer/reporting side, but am now involved in a project to create a data warehouse for our application. I'm trying to find a query I can use to return the sizes of all the tables in the database, so we can identify the largest tables and handle those individually. Can someone give me a query I can run against our Cache database to return the sizes of all the tables from largest to smallest?

Thanks for the help

Product version: Caché 2017.1

Discussion (8)1

Log in or sign up to continue

Vitaliy Serdtsev · Dec 8, 2021

Ok, the index can be omitted.
The table data is stored in different globals: *D - for data, *I - for indexes, *S - for streams. You can get the size of the data parts you want.

Example [Size()]:

select * from %SYS.GlobalQuery_Size('C:\InterSystems\Cache\mgr\samples','','*D,*S',0,0,1) order by "Allocated MB" desc

0 0

Timothy Leavitt · Dec 8, 2021

Here's a full example relating global size back to related classes/tables, expanding on @Vitaliy.Serdtsev's example:

select sdef.parent "Class", class.SqlSchemaName||'.'||class.SqlTableName "Table", LIST(sdef.Location) "Globals", SUM(s."Allocated MB") "Allocated MB", SUM(s."Used MB") "Used MB" from %SYS.GlobalQuery_Size('C:\InterSystems\IRIS20\mgr\user','','*D,*S,*I',0,0,1) s
join (
select parent,DataLocation as Location from %Dictionary.StorageDefinition where not parent %STARTSWITH '%'
union all
select parent,StreamLocation from %Dictionary.StorageDefinition where not parent %STARTSWITH '%'
union all
select parent,IndexLocation from %Dictionary.StorageDefinition where not parent %STARTSWITH '%'
) sdef
on sdef.Location = '^'||s.Name
join %Dictionary.CompiledClass class on class.Name = sdef.parent
group by sdef.parent

Note that if you use inheritance with persistent classes sharing an extent this'll be a little bit off (since the same global is used for multiple classes and will be double counted).

0 0

score 0 · Answer 1 · 2021-12-08T09:02:37-05:00

Timothy Leavitt · Dec 8, 2021

By size, do you mean number of rows or size on disk?

0 0

score 0 · Answer 2 · 2021-12-08T09:17:42-05:00

Size on Disk. We have some tables with narratives. While only a single row of data, the text can be pretty large. Thanks

score 0 · Answer 3 · 2021-12-08T09:50:34-05:00

Vitaliy Serdtsev · Dec 8, 2021

Will the size of the global (with data,index,stream) suit you? If yes, then see this.

0 0

score 0 · Answer 4 · 2021-12-08T09:56:26-05:00

Sure, I can try it and see. I am only interested in the table size on disk however (don't need index information or anything else). Thanks

score 1 · Answer 5 · 2021-12-08T12:27:32-05:00

Note, this also assumes default global names - if you have customized these, you'll want to just get all globals. Also, replace 'C:\InterSystems\IRIS20\mgr\user' with the appropriate database directory for your instance.

score 0 · Answer 6 · 2021-12-08T13:07:15-05:00

Eduard Lebedyuk · Dec 8, 2021

Calculating detailed class/table size.

0 0