sp_opt_querystats

Description

Returns a performance analysis for the selected query.

Syntax

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

Parameters

Examples

Example 1

Analyzes a select command on the pubs2 database:

sp_opt_querystats 'select * from pubs2.dbo.authors'

Example 2

Analyzes a select command on the pubs2 database, and includes information based on enabling these set commands: set showplan, set statistics io, set option show, set statistics plancost on:

sp_opt_querystats 'select * from pubs2.dbo.authors',
   'showplan,statio,option_show, plancost'

Usage

• 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: