sp_flushstats

Flushes statistics from in-memory storage to the systabstats and sysstatistics system tables.

Syntax

sp_flushstats [objname]

Parameters

Examples

Usage

There are additional considerations when using sp_flushstats:
  • When you do not specify a table with the objname parameter, sp_flushstats acts at the database level.

  • Some statistics in the systabstats table are updated in in-memory storage locations and flushed to systabstats periodically, to reduce overhead and contention on systabstats.

  • If you query systabstats using SQL, executing sp_flushstats guarantees that in-memory statistics are flushed to systabstats.

  • The optdiag command always flushes in-memory statistics before displaying output.

  • The statistics in sysstatistics are changed only by data definition language commands and do not require the use of sp_flushstats.

  • The in-memory datachange counters are persistently stored in sysstatistics. These are flushed to disk when sp_flushstats is executed.

Permissions

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

SettingDescription
Enabled

With granular permissions enabled, you must be a user with monitor qp performance privilege.

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:

InformationValues
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