You are a database administrator for AIOTestKing.com. You notice that the following Transact-SQL query is performing very slowly.
SELECT VideoTitle, UpcNum, RetailPrice, ReleaseDate FROM Srvideo.VideoTitle WITH (INDEX(0)) WHERE ReleaseDate BETWEEN ‘20050401’ AND ‘20050510’ There is a clustered index on the VideoTitle column. There is a nonclustered index on the ReleaseDate column that includes the UpcNum and RetailPrice columns. When you query the sys.dm_db_index_physical_stats dynamic management function (DMF) or the VideoTitle table, the avg_fragmentation_in_percent is 10 percent. The graphical execution plan for the slow query is shown in the exhibit.
You need to ensure that the query performs quickly. What should you do?
A.
Remove the query hint from the query.
B.
Re-create the index on only the ReleaseDate column.
C.
Rebuild all indexes on the VideoTitle table.
D.
Change the query hint to force the optimizer to force a clustered index seek or a scan.
Explanation:
The WITH (INDEX(0)) is not necessary in this query.