Exam 70-461 topic 1 question 116 discussion

Actual exam question from Microsoft's 70-461

Question #: 116
Topic #: 1

You are developing a database application by using Microsoft SQL Server 2012.
An application that uses a database begins to run slowly.
You discover that the root cause is a query against a frequently updated table that has a clustered index. The query returns four columns: three columns in its
WHERE clause contained in a non-clustered index and one additional column.
You need to optimize the statement.
What should you do?

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 PROFILE ON statement before you run the query. K. Include a SET STATISTICS SHOWPLAN_XML ON statement before you run the query. L. Include a SET TRANSACTIONISOLATION LEVEL REPEATABLE READ statement before you run the query. M. Include a SET TRANSACTION ISOLATION LEVEL SNAPSHOT statement before you run the query. N. Include a SET TRANSACTION ISOLATION LEVEL SERIALIZABLE statement before you run the query.

Show Suggested Answer

Suggested Answer: C 🗳️

by marco13 at June 1, 2020, 12:04 p.m.

Comments

Submit Cancel

marco13

5 years ago

This seems an ideal scenario to add INCLUDE clause to the index (option D). Can anyone explain why adding FORCESEEK is a better answer?

upvoted 3 times

course104

4 years, 5 months ago

Nobody. D is correct.

upvoted 1 times

...