Bradley Larson · Dec 8, 2021

Query to return table sizes

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
0 384
Discussion (8)1
Log in or sign up to continue

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

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

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

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

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

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).

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.