You have a table named Rooms that contains three columns.
You execute the following query:
You discover the execution plan shown in the exhibit. (Click the Exhibit button.)
You need to recommend a solution to reduce the amount of time it takes to execute the
query.
What should you do?
More than one answer choice may achieve the goal. Select the BEST answer.
A.
Include the RoomName column and the Position column in the Room_IX index.
B.
Create a nonclustered index for RoomName, Id, and Position.
C.
Create a clustered index for Id.
D.
Use the WITH (INDEX(Room_IX),NOLOCK) query hint.
I think, it is A because the existing index is already non-clustered. Add RoomName in the index and include Position.
Assuming that Room_IX was created on just [Id], then “adding” RoomName and including position would look like (Id, RoomName). The lookup on RoomName would not be able to use this index.
I believe it’s B because they list RoomName as the first column in the index.
Why do you assume that Room_IX was created just on Id? If it were created on Id alone the optimizer would not have used it in the plan. It’s part of the plan because it is on RoomName column. Adding ID and position.
*Including Id and Position in the index will save an RID lookup on heap.
I agree with A
B should the correct answer because the query is performed using Room name.