You work as a database administrator at Domain.com. Domain.com is using a SQL Server
2008 instance that contains a database. During monitoring, you notice that the following Transact-SQL query is running slow:
SELECT VideoTitle, UpcNum, RetailPrice, Release Date FROM Srvideo.VideoTitle WITH
(INDEX(0)) WHERE ReleaseDate BETWEEN ‘20050401’ AND ‘20050510’
With regards to the output, a clustered index exists on the VideoTitle column. Furthermore there is
a nonclustered index on the ReleaseDate column that has the UpcNum and RetailPrice columns.
You also notice that the avg_fragmentation_in_percent is 30 percent, when you explore the
sys.dm_db_index_physical_stats dynamic management function (DMF) or the VideoTitle table.
You have recievewd instructions to find the method to resolve the problem.
What should you do?
A.
You should consider reproducing the entire index on the VideoTitle table.
B.
You should consider deleting the query hint from the query.
C.
You should consider changing the query hint to force the optimizer to force an unclustered
index seek.
D.
You should consider recreating the index on only the ReleaseDate column.