Developing SQL Databases — Question 150

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?

Answer options

• A. SET STATISTICS PROFILE OFF
• B. SET SHOWPLAN_TEXT OFF
• C. SET SHOWPLAN_ALL ON
• D. SET STATISTICS PROFILE ON

Correct answer: C

Explanation

The correct answer is C, as SET SHOWPLAN_ALL ON provides a detailed execution plan for the Transact-SQL statements, allowing you to analyze query performance without executing them. The other options either disable profiling or provide a less detailed view of the execution plan, which does not meet the requirement to gather extensive information.