Chapter 1: System Procedures

sp_metrics

Description

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.

Permissions

System Administrator

Auditing

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

Event	Audit option	Command or access audited	Information in extrainfo
38	exec_procedure	Execution of a procedure	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