You have the following query on a disk-based table:
You discover that the query takes a long time to complete.
The execution plan is shown in the Execution Plan exhibit. (Click the Exhibit button.)
The index usage is show in the Index Usage exhibit. (Click the Exhibit button.)
You need to reduce the amount of time it takes to complete the query. You must achieve this
goal as quickly as possible.
What should you do?
A.
Reorganize the index.
B.
Update statistics.
C.
Create an index on LastName.
D.
Rebuild the index.
Why not update statistics? Estimated number of rows is quite different from actual number of rows
You must create nonclustered index on LastName to replace index scan with index seek. So C
The Query Plan is performing IMPLICIT CONVERSION on the filtering predicate (LastName). Hence, there’s need for a non-clustered index on this column.
The object of the index is PK_Contact_ContactId
so it can be assumed the used index is the primary key index on ContactId.
We need an index on lastname
creating non cluster index on last name is the solution