The part of the proc which is using the most IO is an insert into a variable.
Part of this gets information from a table where an index hint has been included.
Looking at the execution plan, SQL is ignoring the index hint and choosing to use the clustered index on the table, which is not efficient.
The fragmentation on the table <=8%. I have come across this before and rebuilding the indexes does work, but this will require downtime as the table is in constant use and SQL is not enterpirse edition.
Is there any way to force SQL to use the index hint provided to it?
By DaliMorse 1 asked Jul 15, 2014 at 09:40 AM
Also, in looking at the query (I must have missed the image, or it was added later), what you have highlighted is the key lookup to get all of the fields that are not in the index (that is hidden behind the popup). I am guessing that the index behind the pop-up is the one in the hint. To get rid of the highlighted item, alter the index used behind the pop-up with: includes(EarliestActionTime,IsActive,LatestActionTime, PlannedJobID,PlannedJobStatusID). That will remove the key lookup completely.
By Edward Norris 96 answered Jul 15, 2014 at 12:13 PM