Which Transact-SQL statement should you run?

You have multiple queries that take a long time to complete.

You need to identify the cause by using detailed information about the Transact-SQL statements in the queries. The Transact-SQL statements must not run as part of the analysis.

Which Transact-SQL statement should you run?
A . SET STATISTICS PROFILE OFF
B . SET SHOWPLAN_TEXT OFF
C . SET SHOWPLAN_ALL ON
D . SET STATISTICS PROFILE ON

View Answer

Answer: C

Explanation:

SET SHOWPLAN_ALL ON causes Microsoft SQL Server not to execute Transact-SQL statements. Instead, SQL Server returns detailed information about how the statements are executed and provides estimates of the resource requirements for the statements.

Incorrect Answers:

D: When STATISTICS PROFILE is ON, each executed query returns its regular result set, followed by an additional result set that shows a profile of the query execution. Displays the profile information for a statement. STATISTICS PROFILE works for ad hoc queries, views, and stored procedures.

References:

https://docs.microsoft.com/en-us/sql/t-sql/statements/set-showplan-all-transact-sql