Edit

.show extents

  • Article

Note

Data shards are called extents, and all commands use "extent" or "extents" as a synonym. For more information on extents, see Extents (Data Shards) Overview.

The types of .show extents commands are as follows:

Note

The .show extents command may consume a lot of resources if it runs on a scope (such as a database or a cluster) with many extents. We recommended using the command variant at the lowest possible scope. Table-scope is preferable over database-scope, and database-scope over cluster-scope. The command variant that includes filtering extents is preferable to filtering the results of the command using another query.

Permissions

To see extents on the cluster, you must have AllDatabasesMonitor permissions.

To see extents on a database, you must have Database User, Database Viewer, or Database Monitor permissions.

For more information, see role-based access control.

Table scope

Syntax

Shows information about extents (data shards) that are present in the specified tables. The database is taken from the command's context. If hot is specified, shows only extents that are expected to be in the hot cache.

.show table TableName extents [( ExtentId [, ...])] [hot] [where tags (has|contains|!has|!contains) TagName [and tags (has|contains|!has|!contains) TagName [, ...]]]

.show tables (TableName [, ...]) extents [( ExtentId [, ...])] [hot] [where tags (has|contains|!has|!contains) TagName [and tags (has|contains|!has|!contains) TagName [, ...]]]

Learn more about syntax conventions.

Parameters

Name Type Required Description
TableName string ✔️ The name of the table.
ExtentId string The ID of the extent to show.
Tag string The name of a tag to filter by as specified.

Recommendations

  • Using built-in filtering capabilities in the command is preferred over adding a query-based filter (such as adding | where DatabaseName == '...' and TableName == '...').
  • If the optional list of extent IDs is provided, the returned dataset is limited to those extents only.
    • This method is faster than filtering (adding | where ExtentId in(...)) to the results of "bare" commands.
  • If tags filters are specified:
    • The returned list is limited to those extents whose tags collection obeys all of the provided tags filters.
    • This method is faster than filtering (adding | where Tags has '...' and Tags contains '...' to) the results of "bare" commands.
    • has filters are equality filters. Extents that aren't tagged with either of the specified tags are filtered out.
    • !has filters are equality negative filters. Extents that are tagged with either of the specified tags are filtered out.
    • contains filters are case-insensitive substring filters. Extents that don't have the specified strings as a substring of any of their tags are filtered out.
    • !contains filters are case-insensitive substring negative filters. Extents that have the specified strings as a substring of any of their tags are filtered out.

Database scope

Shows information about extents (data shards) that are present in the specified database. If hot is specified - shows only extents that expected to be in the hot cache.

Syntax

.show database DatabaseName extents [( ExtentId [, ...])] [hot] [where tags (has|contains|!has|!contains) TagName [and tags (has|contains|!has|!contains) TagName [, ...]]]

Learn more about syntax conventions.

Parameters

Name Type Required Description
DatabaseName string ✔️ The name of the database.
ExtentId string The ID of the extent to show.
Tag string The name of a tag to filter by as specified.

Cluster scope

Syntax

.show cluster extents [hot]

Learn more about syntax conventions.

Shows information about extents (data shards) that are present in the cluster. If hot is specified - shows only extents that are expected to be in the hot cache.

Returns

Output parameter Type Description
ExtentId guid ID of the extent
DatabaseName string Database that the extent belongs to
TableName string Table that the extents belong to
MaxCreatedOn datetime Date-time when the extent was created. For a merged extent, the maximum of creation times among source extents
OriginalSize Double Original size in bytes of the extent data
ExtentSize Double Size of the extent in memory (compressed + index)
CompressedSize Double Compressed size of the extent data in memory
IndexSize Double Index size of the extent data
Blocks long Number of data blocks in the extent
Segments long Number of data segments in the extent
ExtentContainerId string ID of the extent container the extent is in
RowCount long Number of rows in the extent
MinCreatedOn datetime Date-time when the extent was created. For a merged extent, the minimum of creation times among the source extents
Tags string Tags, if any, defined for the extent
Kind string The kind of the storage engine that created the extent
DeletedRowCount long Number of deleted rows in the extent

Examples

Tagged extent

Extent E in table T is tagged with tags aaa, BBB, and ccc.

  • This query returns E:

      .show table T extents where tags has 'aaa' and tags contains 'bb'

  • This query doesn't return E since it isn't tagged with aa:

      .show table T extents where tags has 'aa' and tags contains 'bb'

  • This query returns E:

      .show table T extents where tags contains 'aaa' and tags contains 'bb'

Show volume of extents created

Show volume of extents being created per hour in a specific database

.show database MyDatabase extents | summarize count(ExtentId) by MaxCreatedOn bin=time(1h) | render timechart

Show volume of data arriving by table per hour

.show database MyDatabase extents  
| summarize sum(OriginalSize) by TableName, MaxCreatedOn bin=time(1h)  
| render timechart

Show data size distribution by table

.show database MyDatabase extents | summarize sum(OriginalSize) by TableName

Show all extents in the database named 'GamesDB'

.show database GamesDB extents

Show all extents in the table named 'Games'

.show table Games extents

Show all extents in specific tables

Show all extents in the tables named 'TaggingGames1' and 'TaggingGames2', tagged with both 'tag1' and 'tag2'

.show tables (TaggingGames1,TaggingGames2) extents where tags has 'tag1' and tags has 'tag2'