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