sp_metrics

Backs up, drops, and flushes QP metrics—always captured in the default running group, which is group 1 in each respective database—and their statistics on queries.

Syntax

sp_metrics [‘backup’ backup_group_ID | ‘drop’, ‘gid’ [, ‘id’] | 
	‘flush’ | ‘help’, ‘command’]

Parameters

backup – moves saved QP metrics from the default running group to a backup group, backs up the QP metrics from the old server into a backup group, and moves saved QP metrics from the default running group to a backup group.
backup_group_ID – is the ID of the group the QP metrics from the old server into a backup group. To move saved QP metrics from the default running group to a backup group.
drop – removes QP metrics from the system catalog. If you do not provide ‘id’, sp_metrics drops the whole group you specified with ‘gid’.
gid – is the group ID of the QP metrics from the system catalog.
id – is the ID of the QP metrics from the system catalog.
flush – flushes all aggregated metrics in memory to the system catalog. The aggregated metrics for all statements in memory are zeroed out.
‘help’, ‘command’ – provides usage information on sp_metrics commands.

Examples

Example 1 – Move the QP metrics from a default group to a backup group.
```
sp_metrics 'backup', '3'
```
Example 2 – Provides information about sp_metrics flush:
```
sp_metrics 'help', 'flush'
```

Usage

Access metric information using a select statement with order by against the sysquerymetrics view.

Use to back up the QP metrics from the old server into a backup group. To move saved QP metrics from the default running group to a backup group, to remove QP metrics from the system catalog. Flush all aggregated metrics in memory to the system catalog.

See also select, set in Reference Manual: Commands.

Permissions

The permission checks for sp_metrics differ based on your granular permissions settings.

Setting	Description
Enabled	With granular permissions enabled, you must be a user with `manage server` privilege or with `monitor qp performance` privilege (for filter, show, help).
Disabled	With granular permissions disabled, you must be a user with sa_role.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

Information	Values
Event	38
Audit option	exec_procedure
Command or access audited	Execution of a procedure
Information in `extrainfo`	Roles – Current active roles Keywords or options – NULL Previous value – NULL Current value – NULL Other information – All input parameters Proxy information – Original login name, if set proxy in effect