» Documentation » SQL Reference

Monitoring

The system database contains information about all database catalogs in the server cluster, configuration of the current server and various memory, network and transaction statistics.

System configuration tables

SysCatalogs

System catalog table syscatalogs. Disk-based table containing records for all databases on the server instance. This table will be replicated to all nodes in a cluster.

Table columns:

Name

Type

Description

id

BIGINT

Unique numeric identifier of database

name

VARCHAR

Database name

path

VARCHAR

Directory for files

locale

INTEGER

Language Codepage number

SysTables

Systables is a list of all tables in the database cluster.

Name

Type

Description

catalog_id

BIGINT

Database this table belongs to. References syscatalogs (id)

id

BIGINT

Table id. Unique to the server cluster. Each time a table is altered, a new id is assigned. The original id when the table was created is found in creation_stamp_id.

name

VARCHAR

Table name, unique to the catalog.

path

VARCHAR

Physical path to table data

count

BIGINT

Approximate number of rows. This is generated by Generate Statistics SQL command

part_fld

SMALLINT

The column which this table is partitioned on. Abbreviation of "partition field". References syscolumns(id). Unless the value is 255, meaning the table is replicated.

data_provider_id

INTEGER

The source provider of the data. E.g. Memory, disk or registry.

creation_stamp_id

BIGINT

The first id of the table, assigned when it was created

stats_timestamp

DATETIME

The last time the statistics were generated

stats_utimestamp

BIGINT

The number of table modifications that had happened at the last GENERATE STATISTICS

SysColumns

Description of the column types. Contains some statistic information for the optimizer, as generated by GENERATE STATISTICS

Name

Type

Description

table_id

BIGINT

Table this column belongs to. References systables (id)

position

SMALLINT

Logical position of the column, as defined by the order of the columns in the CREATE TABLE statement. Unique to the table.

id

SMALLINT

Physical position of the column on disk. Unique to the table. Primary key columns will always come first in this ordering.

name

VARCHAR

Name of the column, unique to the table.

type

SMALLINT

Data type id of the column

type_sz

SMALLINT

Maximum length of the data in bytes for this column

default

VARCHAR

Constant default value. NULL if default_func is used.

default_func

SMALLINT

Internal function id used to generate default values. -1 if 'default' value column is used.

nullable

SMALLINT

0: NOT NULL option (default); 1: NULL option

comment

VARCHAR

Column comment as set by the COMMENT '' option

unique

FLOAT

Approximate proportion of uniqueness in the column. Where 1 means all fields are unique and 0 would (theoretically) mean that all fields were equal. Generated by GENERATE STATISTICS.

avg

INTEGER

Approximate average size of the column's fields' data. Blob and text columns are not calculated. Generated by GENERATE STATISTICS.

SysIndexes

Catalogue of all the database indices and foreign keys.

Name

Type

Description

table_id

BIGINT

Table this index belongs to. References systables (id)

id

INTEGER

Unique id per-table

name

VARCHAR

Constraint name

keys

VARCHAR

' ("vertical bar" or "pipe") character, e.g. "0|1|3|"

ref_table_id

BIGINT

Id of the table if this index is a foreign key, or -1 if none. May reference systables (id)

ref_id

SMALLINT

Id of the foreign index, or -1 if none. May reference sysindexes (id)

on_update

SMALLINT

The ON UPDATE action of a foreign key. 0: NOACTION (default); 1: CASCADE (currently not supported)

on_delete

SMALLINT

The ON DELETE action of a foreign key. 0: NOACTION (default); 1: CASCADE

unique

SMALLINT

The UNIQUE option. 1: index is UNIQUE, 0: index is not UNIQUE

height

INTEGER

Height of the b+tree

dpages

INTEGER

Number of data leaf pages of the b+tree

SysProcedures

One row per stored procedure in the server cluster.

Name

Type

Description

catalog_id

BIGINT

Catalog/database this procedure belongs to. References syscatalogs (id)

proc_id

BIGINT

Globally unique procedure id

name

VARCHAR

Name of the stored procedure, unique to the database catalog.

sql

VARCHAR

SQL code of the procedure

SysInstances

There is one row per server instance in the cluster in the sysinstances table.

Name

Type

Description

instance_id

INTEGER

Node identifier taking account the partition group it belongs too. The node_id calculated: node_id = (node id {1,2,..32})(partition_group_id32). For example, for node (1) in partition group 1, the node_id = 32. Node_id/32 will tell the partition group id the node belongs too.

partition_group_id

INT

partition group identifier the node belongs to

unique_id

BIGINT

Similar to node_id, except the unique node id never changes. node_id will change when partition group is split.

endpoint

VARCHAR

Connection endpoint. This is the connection string for the network protocol. This will be '<ip-number>:<port-number>' for a TCP/IP connection, or <server-name>/<pipe-name>/scimore for a named pipe.

current

SMALLINT

This will be 1 for the one instance that the query is run through. Other instances in the cluster will be 0.

pg_mask

INT

32 bit availability partition groups' mask. The nodes in the partition enumerated [1,32], each node's id represents the bit in the mask. For example, initially partition group consist 3 nodes:1,2,3, the mask = 1 + 2 + 4 = 7. If node (2) is voted off, the mask becomes 5.

v_lower

INT

Nodes' hash range starts value.

v_upper

INT

Nodes' hash range end value.

SysCluster

A cluster change history/log table. When either failover or admin executes cluster changes, the new configuration set is inserted to the table.

Name

Type

Description

view_id

BIGINT

Auto incremental value. Increments per new cluster configuration inserted.

cluster_version

INT

Cluster version. If admin changes cluster, the version will increment. If a node is voted off the cluster, the cluster version will remain the same and [group_mask] will change.

group_id

INT

Unique group id the node belongs to.

group_version

INT

Like with cluster version, group version increments, when particular clusters' group is changed, e.g. add/remove nodes in group, split group.

group_mask

INT

The same as [SysInstances] "pg_mask" column

node_id

INT

The same as [SysInstances] "instance_id" column.

unique_id

BIGINT

The same as [SysInstances] "unique_id" column

endpoint

VARCHAR

The same as [SysInstances] "endpoint" column

Sysusers

The table list users and roles.

Name

Type

Description

catalog_id

BIGINT

Database identifier the role belongs too. Users always belong to 0 that is SYSTEM catalog.

id

BIGINT

Unique user or role id

grants

VARCHAR

' the list of roles IDs the user/role belongs to. For example, user belongs to roles: 1,2,4, the field value will be: '1|2|4'

name

VARCHAR

Either globally unique username or unique per database the role name.

password

VARCHAR

Encrypted users' value.

owner_id

BIGINT

Unique identifier of the user what created the particular user. If value '-1' user don't have the parent/creator.

role

INT

Type of the security object: 0 - user, 1 - role, 2 - built-in role, 3- performance role

SysPermissions

The map between the user/role and the explicit permissions.

Name

Type

Description

user_id

BIGINT

User or role id (Sysusers(id)).

id

BIGINT

permission unique identifier.

object_id

BIGINT

Depending on objet_type field value, either unique identifier of database, table or procedure

object_type

INT

The value identify the object type: 1 - system/server, 2 - database, 4 - table, 8 - procedure

permission_type

INT

values]]

allow

BIT

1 - grant, 0 - deny the permission.

grantee_id

BIGINT

The unique identifier of the user who granted/denied permission.

grantable

BIT

Identify is user can grant the permission to others.

sysconfig

A table containing the current server instance configuration. This table is derived from information in the registry and startup settings of the server instances.

Performance statistical tables

These are virtual tables generated dynamically to provide monitoring capabilities.

msessions

Information about the current connection sessions

Name

Type

Description

instance_id

INTEGER

Node id. References sysinstances (id)

id

BIGINT

Unique session id, unique within cluster

exec_state

INTEGER

Execution state of the internal thread handling this session:

  • 0 - pending (ready to process new statements, or return recordset to the client)
  • 1 - waiting for NET messages from another node
  • 2 - waiting for DISK IO to complete
  • 3 - waiting to grant the lock
  • 4 - waiting to grant latch lock on the DB page
  • 5 - InDoubt transaction. Means the transaction faith will be decided by ScimoreAgent.

ph_pages_io

BIGINT

Number of pages that have been physically accessed on disk in the current transaction.

lg_pages_io

BIGINT

Total number of logical pages accessed in the current transaction.

tran_id

BIGINT

Unique id of most recent transaction of this session

tran_state

INTEGER

Transaction's current commit state:

  • 0 - Unknown -transactions have not begun yet
  • 1 Read Only. Transaction has not modified any data
  • 2 Dirty - transaction has modified some data
  • 3 Commit - transaction has been committed
  • 4 Rollback - transaction has been aborted
  • 5 Precommit Dirty - transaction in pre-commit of the 2PC phase, one of the db instances modified data
  • 6 Precommit Read - transaction in pre-commit of the 2PC phase, none of the instances modified any data
  • 7 Preabort - transaction in precommit 2PC phase, at least one instance failed transaction
  • 8 Committing - transaction will be committed, all instances are committing the changes
  • 9 Rolling Back - transaction will be aborted, all instances are rolling back the changes

tran_iso

INTEGER

Transaction's isolation level:

  • 0 - Read Committed - reads data, including any updates committed while running
  • 1 and 2 - Read Repeatable - reads only committed data that was committed before start of transaction
  • 3 - Serializable - only one transaction at a time

tran_auto_commit

INTEGER

Transaction's automatic commit mode. 0: Auto-commit off; 1: Auto-commit on.

locks

INTEGER

Number of row-level locks that the transaction is currently holding.

blocked_by_tran_id

BIGINT

Transaction id that this session is waiting for locks from

error

VARCHAR

Text of the most recent error message

sql

VARCHAR

Last SQL statement executed

quants_0

INT

The number of executed LOW priority quants. Per transaction.

quants_1

INT

The number of executed NORMAL priority quants. Per transaction.

quants_2

INT

The number of executed HIGH priority quants. Per transaction.

quants_3

INT

The number of executed HIGHEST priority quants. Per transaction.

max_priority

INT

Maximum allowed priority

query_priority

INT

Starting query priority. If dynamic priority throttling is disabled, the starting priority will never change.

throttle_priority

INT

When dynamic priority throttling is enabled, the priority will decrease for heavy queries, the "throttle_priority" identify the current transactions' priority.

cpu_time

BIGINT

Session used CPU time in microseconds. Per transaction.

net_requests

INT

Number of network IOs. Per transaction.

musers

If database has been secured, musers store users' aggregated performance statistics.

Name

Type

Description

instance_id

INT

Node id. References sysinstances(id)

user_id

BIGINT

User id.

transactions

BIGINT

Number of transactions executed by the user.

cpu_time

BIGINT

Number of CPU time in microseconds.

mem_pages

BIGINT

Number of accessed database pages.

disk_pages

BIGINT

Number of pages read from disk

page_faults

BIGINT

Number of flushed dirty pages to disk in order to read another page.

quants_highest

BIGINT

Number of executed quants with [Highest] priority

quants_higher

BIGINT

Number of executed quants with [Higher] priority

quants_normal

BIGINT

Number of executed quants with [Normal] priority

quants_low

BIGINT

Number of executed quants with [Low] priority

max_priority

INT

Maximum query priority

avg_priority

INT

Average query priority.

min_priority

INT

minimum query priority.

mprocesses

One system process per node per CPU. Within each node there are several worker threads - CPU schedulers, where each uses the distinct CPU.

Name

Type

Description

instance_id

INTEGER

Node id that this process is part of. References sysinstances (id)

id

INTEGER

Unique process id internal to the node

busy

INTEGER

Number of executing transactions

total

INTEGER

Total number of fibers allocated in this process

queue

INTEGER

Number of SQL requests awaiting to service them.

switch_per_sec

INTEGER

Number of internal context switches between fibers per second per process

connections

INTEGER

Number of active connections per process. Each connection is serviced by one CPU process, assigned on connection creation.

mnetwork

Statistics on current network connections

Name

Type

Description

instance_id

INTEGER

Node id. References sysinstances (id)

id

INTEGER

Id of the communication protocol

protocol

VARCHAR

Name of the communication protocol

read_bytes_sec

INTEGER

Bytes per second currently being received on the network layer

write_bytes_sec

INTEGER

Bytes per second currently being sent on the network layer

requests_sec

INTEGER

Number of network layer requests per second

mtables

Performance monitor on tables

Name

Type

Description

instance_id

INTEGER

Node id. References sysinstances (id)

id

BIGINT

Table id. References systables (id)

deletes_sec

INTEGER

Current rate of deletes per second

inserts_sec

INTEGER

Current rate of inserts per second

updates_sec

INTEGER

Current rate of updates per second

selects_sec

INTEGER

Current rate of selects per second

cache_hit_ratio

INTEGER

Current cache hit ratio percentage for the database pages of the table

cache_page_io_sec

INTEGER

Current rate of pages being read from the cache per second

disk_page_io_sec

INTEGER

Current rate of pages being read from disk per second

file_size

INTEGER

Size of the table's file on disk in MB

page_split_sec

INTEGER

Current rate of B+tree page splits per second

page_alloc_sec

INTEGER

Current rate of allocated pages per second

page_free_sec

INTEGER

Current rate of freed pages per second

ncount

INTEGER

Approximate number of rows in the table. copied from systables, used by optimizer. The values are populated when generating statistics, SQL GENERATE STATISTICS FOR DATABASE mydb

dpages

INTEGER

Number of B+Tree data leaf pages in the table. copied from systables, used by optimizer and populated when generating statistics.

mprocedures

mprocedures is a virtual table containing information on how many times a procedure has been executed.

Name

Type

Description

instance_id

INTEGER

Node id. References sysinstances (id)

id

BIGINT

procedure_id. References sysprocedures (id)

avg_cpu

INTEGER

Average CPU cost (unimplemented)

executed

INTEGER

Number of times the procedure has been executed

mtransactionlog

The dynamic mtransactionlog table provides information on the transaction log file of each server instance.

Name

Type

Description

instance_id

INTEGER

Node id. References sysinstances (id)

log_bytes_sec

INTEGER

Current rate of bytes being sent to the log manager per second

disk_sec

INTEGER

Current rate of disk write operations to transaction log per second

commit_sec

INTEGER

Current rate of commit/rollback requests received per second. disk_sec may be lower than commit_sec, if there are group-commits happening. A group-commit is when the process waits for other commits before writing to disk, for efficiency.

file_size

INTEGER

Current size of transaction log file in MB

mtransactions

The dynamic mtransactions table provides statistical information on current transactions.

Name

Type

Description

instance_id

INTEGER

Node id. References sysinstances (id)

exec_tran

INTEGER

Current number of active transactions in progress (not committed or aborted)

tran_sec

INTEGER

Current rate of transactions per second

commit_sec

INTEGER

Current rate of committed transactions per second

abort_sec

INTEGER

Current rate of aborted transactions per second

mlocks

The dynamic mlocks table provides statistical information on locks for each node.

Name

Type

Description

instance_id

INTEGER

Node id. References sysinstances (id)

deadlock_sec

INTEGER

Current rate of deadlocks per second

grant_locks

INTEGER

Current number of allocated and granted locks

lock_sec

INTEGER

Current rate of locks granted per second

block_tran

INTEGER

Current number of blocked transactions

mpagecache

The dynamic mpagecache table provides information on the transaction log file of each node.

Name

Type

Description

instance_id

INTEGER

Node id. References sysinstances (id)

free

INTEGER

Current number of free pages in the memory cache

pinned

INTEGER

Current number of pages locked in memory, that cannot be page faulted, moved, etc.

dirty

INTEGER

Current number of modified pages in the memory cache, unwritten to disk

page_faults

INTEGER

Current rate of page faults (pages that have to be swapped out to disk so another can be swapped in) per second.

checkpoint_pages_sec

INTEGER

Current rate of pages flushed to disk by transaction checkpoints per second

checkpoint_group_pages

INTEGER

Current number of pages flushed to disk in a group, while a checkpoint is in progress.

lwriter_pages_sec

INTEGER

Current rate of pages flushed by transaction checkpoints per second for the lazy-writer process

lwriter_group_pages

INTEGER

Current number of pages flushed to disk in a group for the lazy-writer process.