Question
Paul Riker · Jul 11, 2016

Database Table Size

Hi All,

We are doing capacity planning. I want to track the number of rows and size of each table (or at least the top ~10). Any ideas?

Thanks

1
0 3,250
Discussion (9)2
Log in or sign up to continue

If you use the command d ^%GSIZE the result will be something like this:

      Global        Blocks       Bytes Used  Packing   Contig.
      --------    --------  ---------------  -------   -------
      Aviation.AircraftD
                        64          455,452     87 %        62
      Aviation.AircraftI
                         4           27,300     84 %         2
      Aviation.Countries
                         1            4,300     53 %         0
      Aviation.CrewI
                         5           36,824     90 %         3
      Aviation.EventD
                     1,153        6,980,501     74 %       649
      Aviation.EventI
                         3           19,020     78 %         1
      Aviation.States
                         1              820     10 %         0
      CacheMsg           6           30,040     61 %         0
      Cinema.ReviewD
                         1            4,012     49 %         0

Maybe this command could help you.

I hope that helps

I'm new to this so I'm sure it's me, but I get 

 

do ^%GSIZE


Directory name: /hs/data/mgr/ensemble/ =>

All Globals? No => No
Global ^
0 globals selected from 187 available globals.

No globals were selected!

Paul, 

When the command ^%GSIZE asked if you wanted "All Globals", your aswer was No


All Globals? No => No
Global ^
0 globals selected from 187 available globals.

So, the command do not selected no globals of your 187 globals.


Try to answer Yes instead and look what happens after.

This worked like a charm, thanks! What I didn't realize is that using commands in studio vs. cscession hiecontroller is different. Can I check the size of a specific table?

Try to look at query  Size in class %SYS.GlobalQuery

HTH

Can you tell me the specific syntax? Should I just be able to run %SYS.GlobalQuery?

there is a thread about class queries that gives a full explanation but in this specific case you could do ...

 

   set statement=##class(%SQL.Statement).%New()
    set status=statement.%PrepareClassQuery("%SYS.GlobalQuery","Size")
   set resultset=statement.%Execute("c:\intersystems\ens20163a\mgr\ensemble","","*")
    while resultset.%Next() {
        write !, resultset.%Get("Name"),", "                                       
        write resultset.%Get("Allocated MB"),", "    
        write resultset.%Get("Used MB")
     }

I did a logic to read all globals of the namespace and show only the globals that have the size larger than 100 mb:

VerGlobal    
    new global,set,tamanho,qtde
    set (global,qtde)=""
    kill ^mtempTAMANHOGLO
    for  {
        set global=$order(^$global(global)) quit:global=""
        set sc=##class(%Library.GlobalEdit).GetGlobalSize($zu(12,""),global,.tamanho,,1)
        set qtde=qtde+1
        set ^mtempLISTA(global)=global    
        if tamanho>100 {
            set ^mtempTAMANHOGLO(tamanho,global)=global            
        }
        w #
        write !, "Quantidade de globais verificadas "_qtde
    }    
    
    quit 1