sysquerymetrics

All databases

Description

Presents aggregated historical query processing metrics for individual queries from persistent data. In addition to monitoring tables, use performance metrics information from this catalog.

Columns

The columns for sysquerymetrics are:

Name	Datatype	Description
uid	int	User ID
gid	int	Group ID
hashkey	int	Hashkey over the SQL query text
id	int	Unique ID
sequence	smallint null	Sequence number for a row when multiple rows are required for the text of the SQL
exec_min	int null	Minimum execution time
exec_max	int null	Maximum execution time
exec_avg	int null	Average execution time
elap_min	int null	Minimum elapsed time
elap_max	int null	Maximum elapsed time
elap_avg	int null	Average elapsed time
lio_min	int null	Minimum logical IO
lio_max	int null	Maximum logical IO
lio_avg	int null	Average logical IO
pio_min	int null	Minimum physical IO
pio_max	int null	Maximum physical IO
pio_avg	int null	Average physical IO
cnt	int null	Number of times the query has been executed.
abort_cnt	int null	Number of times a query is aborted by the Resource Governor when a resource limit is exceeded
qtext	varchar(255) null	Query text

The number of metrics shared among user IDs increased for Adaptive Server release 15.0.2 and later, reducing the number of entries in sysquerymetrics (a view of sysqueryplans), and automatically aggregates the metrics for identical queries across different user IDs.

The user ID (uid) of sysquerymetrics is 0 when all table names in a query that are not qualified by user name are owned by the DBO.

For example, if table t1 is owned only by the DBO and shared by different users:

select * from t1 where c1 = 1

Adaptive Server uses 0 as the uid for the sysquerymetrics entry for all users executing this query who do not have a private table named t1.

In this example, if table t2 is owned and qualified by “user1,” Adaptive Server also uses an UID of 0:

selet * from user1.t2 where c1 = 1

However, if table t3 is owned only by “user1,” but is unqualified and not owned by the DBO, the UID of “user1” is used in the sysquerymetrics entry:

select * from t3 where c1 = 1