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.
sp_metrics [‘backup’ backup_group_ID | ‘drop’, ‘gid’ [, ‘id’] | ‘flush’ | ‘help’, ‘command’]
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.
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.
removes QP metrics from the system catalog. If you do not provide ‘id’, sp_metrics drops the whole group you specified with ‘gid’.
is the group ID of the QP metrics from the system catalog.
is the ID of the QP metrics from the system catalog.
flushes all aggregated metrics in memory to the system catalog. The aggregated metrics for all statements in memory are zeroed out.
provides usage information on sp_metrics commands.
Move the QP metrics from a default group to a backup group.
sp_metrics 'backup', '3'
Provides information about sp_metrics flush:
sp_metrics 'help', 'flush'
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.
The permission checks for sp_metrics differ based on your granular permissions settings.
Granular permissions enabled |
With granular permissions enabled, you must be a user with manage server privilege or with monitor qp performance privilege (for filter, show, help). |
Granular permissions disabled |
With granular permissions disabled, you must be a user with sa_role. |
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 |
|
Procedures sp_configure
