You need to capture the execution plan for a query. Which statement should you use?

Question

Options

• ASET FORCEPLAN ON;
• BSET SHOWPLAN_XML ON;
• CSET STATISTICS IO ON;
• DSET STATISTICS TIME ON;

• A
  9% (4)
• B
  84% (38)
• C
  2% (1)
• D
  4% (2)

Explanation

SET SHOWPLAN_XML ON This statement causes SQL Server not to execute Transact-SQL statements. Instead, Microsoft SQL Server returns execution plan information about how the statements are going to be executed in a well-formed XML document. SET SHOWPLAN_TEXT ON After this SET statement is executed, SQL Server returns the execution plan information for each query in text. The Transact-SQL statements or batches are not executed. SET SHOWPLAN_ALL ON This statement is similar to SET SHOWPLAN_TEXT, except that the output is in a format more verbose than that of SHOWPLAN_TEXT. SET STATISTICS XML ON Returns execution information for each statement after the statement executes in addition to the regular result set the statement returns. The output is a set of well-formed XML documents. SET STATISTICS XML ON produces an XML output document for each statement that executes. The difference between SET SHOWPLAN_XML ON and SET STATISTICS XML ON is that the second SET option executes the Transact-SQL statement or batch. SET STATISTICS XML ON output also includes information about the actual number of rows processed by various operators and the actual number of executes of the operators. SET STATISTICS PROFILE ON Returns the execution information for each statement after the statement executes in addition to the regular result set the statement returns. Both SET statement options provide output in text. The difference between SET SHOWPLAN_ALL ON and SET STATISTICS PROFILE ON is that the second SET option executes the Transact-SQL statement or batch. SET STATISTICS PROFILE ON output also includes information about the actual number of rows processed by various operators and the actual number of executes of the operators.

No community discussion yet for this question.