Querying Microsoft SQL Server 2012/2014 — Question 3

You have a database application that uses Microsoft SQL Server 2012. You have a query named Query1 that returns four columns from a frequently updated table that has a clustered index. Three of the columns are referenced in the WHERE clause of the query. The three columns are part of a non-clustered index. The fourth column is not referenced in the WHERE clause.
Users report that the application begins to run slowly. You determine that the root cause for the performance issue is Query1.
You need to optimize the statement.
What should you do?

Answer options

• A. Add a HASH hint to the query.
• B. Add a LOOP hint to the query.
• C. Add a FORCESEEK hint to the query.
• D. Add an INCLUDE clause to the index.
• E. Add a FORCESCAN hint to the Attach query.
• F. Add a columnstore index to cover the query.
• G. Enable the optimize for ad hoc workloads option.
• H. Cover the unique clustered index with a columnstore index.
• I. Include a SET FORCEPLAN ON statement before you run the query.
• J. Include a SET STATISTICS PROFILEON statement before you run the query. K. Include a SET STATISTICS SHOWPLAN_XML ON statement before you run the query. L. Include a SET TRANSACTION ISOLATION LEVEL REPEATABLE READ statement before you run the query. M. Include a SET TRANSACTION ISOLATIONLEVEL SNAPSHOT statement before you run the query. N. Include a SET TRANSACTION ISOLATION LEVEL SERIALIZABLE statement before you run the query.

Correct answer: K

Explanation

The correct action is to add a SET STATISTICS SHOWPLAN_XML ON statement before executing the query, as it provides detailed execution plans that can help in diagnosing performance issues. The other options either hint at query execution strategies that may not address the root cause or involve settings that do not directly improve the performance of the specific query in question.