Search code examples
azure-data-explorerkql

Table Size in ADX

I am trying to get list of all tables size in Azure Data explorer(ADX).

Do we have any single query or table having all tables size meta data ?

I can able to see data using below query:

.show table dev_adls_la_parsed extents;
let tbl_size = $command_results
| summarize num=sum(ExtentSize) by DatabaseName, TableName| extend SizeinGB = format_bytes(num, 2)
| project DatabaseName, TableName, SizeinGB;

tbl_size
| project DatabaseName, TableName, SizeinGB;

Output:

enter image description here

Using below Query trying to store data in one table for better visibility.

.create table adx_tables_space(databaseName:string, tableName:string, SizeinGB:string)

.show table dev_adls_la_parsed extents;
let tbl_size = $command_results
| summarize num=sum(ExtentSize) by DatabaseName, TableName| extend SizeinGB = format_bytes(num, 2)
| project DatabaseName, TableName, SizeinGB;

.set-or-append adx_tables_space <|
tbl_size
| project DatabaseName, TableName, SizeinGB;

It throwing some error:

Syntax Error 

 A recognition error occurred. 
 Token: . 

 Line: 12, Position: 0

 clientRequestId: KustoWebV2;xxxxxxxxxxxxxxxxxxxxxxx
Solution

  • .show database extents

    !! This command is undocumented and might be changed/deprecated in the future.

    .show database extents 
| summarize Extents             = count()
           ,RowCount            = sum(RowCount)
           ,OriginalSize        = format_bytes(sum(OriginalSize), 2)
           ,ExtentSize          = format_bytes(sum(ExtentSize), 2)
           ,CompressedSize      = format_bytes(sum(CompressedSize), 2)
           ,IndexSize           = format_bytes(sum(IndexSize), 2)
            by TableName
| order by  RowCount
    TableName Extents RowCount OriginalSize ExtentSize CompressedSize IndexSize
    Trips 100 1547471776 475.79 GB 100.3 GB 78.95 GB 21.35 GB
    FHV_Trips 34 514304551 37.91 GB 5.92 GB 5.78 GB 146.13 MB
    nyc_taxi 11 165114361 25.29 GB 7.43 GB 7.34 GB 95.35 MB
    GeoRegions 1 5139969 250.35 MB 18.79 MB 12.94 MB 5.85 MB
    demo_many_series1 1 2177472 153.7 MB 12.21 MB 9.01 MB 3.21 MB

    ...

    Fiddle