sp_opt_querystats

Returns a performance analysis for the selected query.

Syntax

sp_opt_querystats "query_text" | help [, "diagnostic_options" | null 
	[, database_name] [, user_name]]

Parameters

Examples

Usage

There are additional considerations when using sp_opt_querystats:
  • You must include the exec command for sp_opt_querystats to execute the query.

  • To run sp_opt_querystats as a different user, include the setuser command with the exec immediate command or in an out query context.

  • You must include the showdata command for sp_query_stats to return the result set.

  • After you issue set quoted_identifier on, you may surround sp_opt_querystats options with quotes. For example:
    sp_opt_querystats 'select "col" from "MYTABLE"', 'all','DB' 
  • diagnostic_option is one of:

    diagnostic_option

    set option

    Notes

    statio

    set statistics io on

    stattime

    set statistics time on

    showplan

    set showplan on

    missingstats

    set option show_missing_stats long

    resource

    set statistics resource on

    switches

    show switches

    option_show_long

    set option show long

    option_show_long and option_show are mutually exclusive.

    option_show

    set option show on

    showdata

    set nodata on

    set nodata on is not executed when you include showdata.

    plancost

    set statistics plancost on

    Only available when you specify the exec or allexec options.

    exec

    set noexec on

    set noexec on is not executed when you include exec.

    allrows_mix

    set plan optgoal allrows_mix

    allrows_mix, allrows_oltp, and allrows_dss are mutually exclusive.

    allrows_oltp

    set plan optgoal allrows_oltp

    allrows_dss

    set plan optgoal allrows_dss

    diagmode

    Returns enhanced progress information.

    all

    Enables the first seven options

    all and allexec cannot be combined with other parameters, and are mutually exclusive.

    The allexec option includes the all option.

    allexec

    Enables the first seven options

  • The option list must be enclosed in quotation marks if you include more than one option, or if you specify the keyword all.

  • Running sp_opt_querystats without any options is the same as running it with the all option.

Permissions

Any user can execute sp_opt_querystats. Permission checks do not differ based on the granular permissions settings.

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