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

See also

Commands select, set

Procedures sp_configure