SET Statements for SQLServer

SET

SHOWPLAN_ALL { ON | OFF }

It will not

execute the TSQL statements.

It cannot

be specified inside a stored procedure,must be the only statements in a

batch.

Returns

information as a set of rows that form a hierarchical tree representing the

steps taken by the SQL Server query processor as it executes each statement.

Each statement reflected in the output contains a single row with the text of

the statement, followed by several rows with the details of the execution

steps.

SHOWPLAN_ALL ON;

GO

SELECT

BusinessEntityID, JobTitle

FROM

HumanResources.Employee

WHERE

JobTitle LIKE ‘Production%‘;

SHOWPLAN_ALL OFF;

All

the information in the resultset are estimated, no any actual statistics

information.

SHOWPLAN_XML { ON | OFF }

It will

return detailed information about how the statements are going to be executed

in XML.

SHOWPLAN_XML ON;

SHOWPLAN_XML OFF;

SHOWPLAN_TEXT { ON | OFF }

It cannot be

specified inside a stored procedure,must be the only statements in a

Each statement reflected in the output

contains a single row with the text of the statement, followed by

several rows with the details of the execution steps. The table shows the

column that the output contains.

SHOWPLAN_TEXT is intended to return readable output for Microsoft Win32

command prompt applications such as

the osql utility. SET SHOWPLAN_ALL

returns more detailed output intended to be used with programs designed to

handle its output.

SHOWPLAN_TEXT ON;

SHOWPLAN_TEXT OFF;

STATISTICS PROFILE { ON | OFF }

It

will execute the TSQL statements.

While

compare with SHOWPLAN_ALL, it has two additional column

Rows,Executes.

statistics profile ON;

statistics profile OFF;

STATISTICS XML { ON | OFF }

generate detailed information about how the statements were executed in

XML.

STATISTICS XML need not be the only statement in a batch.

STATISTICS XML returns output as nvarchar(max) for applications, such as

the sqlcmd utility, where the XML output is

subsequently used by other tools to display and process the query plan

information. The xml comply with below xsd file \Microsoft SQL Server\100\Tools\Binn\schemas\sqlserver\2004\07\showplan\showplanxml.xsd

STATISTICS PROFILE and SET STATISTICS XML are counterparts of each other. The

former produces textual output; the latter produces XML output. In

future versions of SQL Server, new query execution plan information will only

be displayed through the SET STATISTICS XML statement, not the SET STATISTICS

PROFILE statement.

If Include Actual Execution Plan is selected in SQL

Server Management Studio, this SET option does not produce XML Showplan

output. Clear the Include Actual Execution

Plan button before using this SET

option. 

STATISTICS XML ON;

STATISTICS XML OFF;

STATISTICS TIME { ON | OFF }

When SET

STATISTICS TIME is ON, the time statistics for a statement are displayed. When

OFF, the time statistics are not displayed.

The setting of SET

STATISTICS TIME is set at execute or run time and not at parse

time.

Microsoft SQL

Server is unable to provide accurate statistics in fiber mode, which is

activated when you enable the lightweight

pooling configuration option.

The cpu column

in the sysprocesses table is only updated when a

query executes with SET STATISTICS TIME ON. When SET STATISTICS TIME is

OFF, 0 is returned.

ON and OFF

settings also affect the CPU column in the Process Info View for Current

Activity in SQL Server Management Studio.

STATISTICS TIME ON;

STATISTICS TIME OFF;

STATISTICS IO { ON | OFF }

When

STATISTICS IO is ON, statistical information is displayed. When OFF, the

information is not displayed.

After this

option is set ON, all subsequent Transact-SQL statements return the

statistical information until the option is set to OFF.

Transact-SQL statements retrieve LOB columns, some LOB retrieval operations

might require traversing the LOB tree multiple times. This may cause SET

STATISTICS IO to report higher than expected logical reads.

Output

item

Meaning

Table

Name

of the table.

Scan

count

Number

of seeks/scans started after reaching the leaf level in any direction to

retrieve all the values to construct the final dataset for the

output.

count is 0 if the index used is a unique index or clustered index on a

primary key and you are seeking for only one value. For

example WHERE Primary_Key_Column =

<value>.

Scant

count is 1 when you are searching for one value using a non-unique

clustered index which is defined on a non-primary key column. This is

done to check for duplicate values for the key value that you are

searching for. For example WHERE

Clustered_Index_Key_Column = <value>.

count is N when N is the number of different seek/scan started towards

the left or right side at the leaf level after locating a key value

using the index key.

logical

reads

of pages read from the data cache.

physical

of pages read from disk.

read-ahead

of pages placed into the cache for the query.

lob

logical reads

of text, ntext, image, or

large value type (varchar(max), nvarchar(max), varbinary(max)) pages read from the data

cache.

physical reads

of text, ntext, image or large value type pages read from

disk.

read-ahead reads

of text, ntext, image or large value type pages placed into

the cache for the query.

STATISTICS IO ON;

STATISTICS IO OFF;

FORCEPLAN { ON | OFF }

 When

FORCEPLAN is set to ON, the SQL Server query optimizer processes a join in the

same order as the tables appear in the FROM clause of a query.

In addition, setting FORCEPLAN to ON forces the use of a nested loop join

unless other types of joins are required to construct a plan for the query, or

they are requested with join hints or query hints.

FORCEPLAN essentially overrides the logic used by the query optimizer to

process a Transact-SQL SELECT statement. The data returned by the SELECT

statement is the same regardless of this setting. The only difference is the

way in which SQL Server processes the tables to satisfy the query.Query

optimizer hints can also be used in queries to affect how SQL Server processes

the SELECT statement.

DBCC

DROPCLEANBUFFERS 清除数据缓存

DBCC FREEPROCCACHE  清除执行计划缓存

REFERENCES

SHOWPLAN_XML

SHOWPLAN_TEXT 

http://technet.microsoft.com/en-us/library/ms176058.aspx

SHOWPLAN_ALL

http://technet.microsoft.com/en-us/library/ms187735.aspx

STATISTICS PROFILE

http://technet.microsoft.com/en-us/library/ms188752.aspx

STATISTICS XML

STATISTICS TIME 

http://technet.microsoft.com/en-us/library/ms190287.aspx

STATISTICS IO

http://technet.microsoft.com/en-us/library/ms184361.aspx

FORCEPLAN

http://technet.microsoft.com/en-us/library/ms188344.aspx

Statements (Transact-SQL)

http://msdn.microsoft.com/en-us/library/ms190356.aspx