Operations

• 1: Estimate table size
• 2: Journal management
• 3: System information
• 4: Operations
• 5: Queries and commands
• 6: Statistics

1 - Estimate table size

Understanding the size of a table can be helpful for efficient resource management and optimized query performance. In this article, you’ll learn different methods to estimate table sizes and how to use them effectively.

Original size of ingested data

Use the .show table details to estimate the original data size of a table. For an example, see Use .show table details.

This command provides an estimation of the uncompressed size of data ingested into your table based on the assumption that the data was transferred in CSV format. The estimation is based on approximate lengths of numeric values, such as integers, longs, datetimes, and guids, by considering their string representations.

Example use case: Track the size of incoming data over time to make informed decisions about capacity planning.

Table size in terms of access bytes

Use the estimate_data_size() along with the sum() aggregation function to estimate table size based on data types and their respective byte sizes. For an example, see Use estimate_data_size().

This method provides a more precise estimation by considering the byte sizes of numeric values without formatting them as strings. For example, integer values require 4 bytes whereas long and datetime values require 8 bytes. By using this approach, you can accurately estimate the data size that would fit in memory.

Example use case: Determine the cost of a query in terms of bytes to be scanned.

Combined size of multiple tables

You can use the union operator along with the estimate_data_size() and sum() functions to estimate the combined size of multiple tables in terms of access bytes. For an example, see Use union with estimate_data_size().

Example use case: Assess the memory requirements for consolidating data from multiple tables into a single dataset.

Examples

Use .show table details

The following query estimates the original data size of the StormEvents table.

.show table StormEvents details
| project TotalOriginalSize

Output

Use estimate_data_size()

The following query estimates the original data size of the StormEvents table in bytes.

StormEvents
| extend sizeEstimateOfColumn = estimate_data_size(*)
| summarize totalSize=sum(sizeEstimateOfColumn)

Output

Use union with estimate_data_size()

The following query estimates the data size based for all tables in the Samples database.

union withsource=_TableName *
| extend sizeEstimateOfColumn = estimate_data_size(*)
| summarize totalSize=sum(sizeEstimateOfColumn)
| extend sizeGB = format_bytes(totalSize,2,"GB")

2 - Journal management

Journal contains information about metadata operations done on your database.

The metadata operations can result from a management command that a user executed, or internal management commands that the system executed, such as drop extents by retention.

Taking a dependency on them isn’t recommended.

.show journal

The .show journal command returns a list of metadata changes on databases or the cluster that the user has admin access to. The .show journal command returns a list of metadata changes on databases or the environment that the user has admin access to. Permissions

Everyone with permission can execute the command.

Results returned will include:

• All journal entries of the user executing the command.

• All journal entries of databases that the user executing the command has admin access to.

• All cluster journal entries if the user executing the command is a Cluster AllDatabases Admin.

• All journal entries specific to the environment level if the user executing the command has appropriate admin permissions.

.show database DatabaseName journal

The .show database DatabaseName journal command returns journal for the specific database metadata changes.

Permissions

Everyone with permission can execute the command.

Results returned include:

• All journal entries of database DatabaseName if the user executing the command is a database admin in DatabaseName.
• Otherwise, all the journal entries of database DatabaseName and of the user executing the command.

3 - System information

This section summarizes commands that are available to Database Admins and Database Monitors to explore usage, track operations, and investigate ingestion failures. For more information on security roles, see Kusto role-based access control.

• .show journal - displays history of the metadata operations.
• .show operations - displays administrative operations both running and completed, since Admin node was last elected.
• .show queries - displays information on completed and running queries.
• .show commands - displays information on completed commands and their resources utilization.
• .show commands-and-queries - displays information on completed commands and queries, and their resources utilization.
• .show ingestion failures - displays information on failures encountered during data ingestion.
• .show table details - displays information on table size and other table statistics.
• .show table data statistics - displays table data statistics per column.

4 - Operations

5 - Queries and commands

6 - Statistics