您的位置：首页 > 博客中心 > 数据库 >

SET Statements for SQLServer

时间：2022-03-10 16:58

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.

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

SET SHOWPLAN_XML { ON | OFF }

It will not execute the TSQL statements.
It will return detailed information about how the statements are going to be executed in XML.

gxlsystem.com,gxl网

SET SHOWPLAN_TEXT { 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. The table shows the column that the output contains.
SET 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.

SET STATISTICS PROFILE { ON | OFF }

It will execute the TSQL statements.
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.
While compare with SHOWPLAN_ALL, it has two additional column Rows,Executes.

SET STATISTICS XML { ON | OFF }

It will execute the TSQL statements.
It will generate detailed information about how the statements were executed in XML.
SET STATISTICS XML need not be the only statement in a batch.
SET 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
SET 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.

gxlsystem.com,gxl网

SET 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.

SET 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.
When 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

SET 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.
SET 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.

USE AdventureWorks2012;
GO
-- Make sure FORCEPLAN is set to OFF.
SET SHOWPLAN_TEXT OFF;
GO
SET FORCEPLAN OFF;
GO
SET SHOWPLAN_TEXT ON;
GO
-- Example where the query plan is not forced.
SELECT p.LastName, p.FirstName, v.Name
FROM Person.Person AS p
   INNER JOIN HumanResources.Employee AS e
   ON e.BusinessEntityID = p.BusinessEntityID
   INNER JOIN Purchasing.PurchaseOrderHeader AS poh
   ON e.BusinessEntityID = poh.EmployeeID
   INNER JOIN Purchasing.Vendor AS v
   ON poh.VendorID = v.BusinessEntityID;
GO
-- SET FORCEPLAN to ON.
SET SHOWPLAN_TEXT OFF;
GO
SET FORCEPLAN ON;
GO
SET SHOWPLAN_TEXT ON;
GO
-- Reexecute inner join to see the effect of SET FORCEPLAN ON.
SELECT p.LastName, p.FirstName, v.Name
FROM Person.Person AS p
   INNER JOIN HumanResources.Employee AS e 
   ON e.BusinessEntityID = p.BusinessEntityID
   INNER JOIN Purchasing.PurchaseOrderHeader AS poh
   ON e.BusinessEntityID = poh.EmployeeID
   INNER JOIN Purchasing.Vendor AS v
   ON poh.VendorID = v.BusinessEntityID;
GO
SET SHOWPLAN_TEXT OFF;
GO
SET FORCEPLAN OFF;
GO

gxlsystem.com,gxl网

DBCC DROPCLEANBUFFERS 清除数据缓存
DBCC FREEPROCCACHE 清除执行计划缓存

REFERENCES

SET SHOWPLAN_XML
http://technet.microsoft.com/en-us/library/ms187757.aspx

SET SHOWPLAN_TEXT

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

SET SHOWPLAN_ALL

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

SET STATISTICS PROFILE

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

SET STATISTICS XML
http://technet.microsoft.com/en-us/library/ms176107.aspx

SET STATISTICS TIME http://technet.microsoft.com/en-us/library/ms190287.aspx SET STATISTICS IO http://technet.microsoft.com/en-us/library/ms184361.aspx
SET FORCEPLAN http://technet.microsoft.com/en-us/library/ms188344.aspx SET Statements (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms190356.aspx

SET Statements for SQLServer,gxlsystem