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

"query_text": is the text of the query you are analyzing, enclosed in quotation marks.

help: displays syntax and usage information for sp_opt_querystats.

diagnostic_options: (Optional) the diagnostic parameters based on set options. See “Usage.”

null: sp_opt_querystats requires three parameters to specify the name of a database. If you do not require diagnostic options, enter a value of null for this parameter to specify a value for the database_name parameter.

database_name: (optional) the name of the database in which the query is executed. Use this parameter if the query you are analyzing does not have fully qualified tables.

user_name: (Optional) name of the user who executes the query within the database specified by the database_name parameter. This user must already exist in the database, and the login executing sp_opt_querystats must have permission to execute the setuser command in that database.

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
plancost	set statistics plancost 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.
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 eight 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.