I have a query that needs a plan guide, but I am having a hard time setting it up.
Query below from the procedure cache...
(@state nvarchar(14),
@jobName nvarchar(18),
@jobGroup nvarchar(28),
@oldState nvarchar(6))
UPDATE JOB_TRIGGERS
SET TRIGGER_STATE = @state
WHERE JOB_NAME = @jobName
AND JOB_GROUP = @jobGroup
AND TRIGGER_STATE = @oldState
SQL Server chooses to perform a clustered index scan vs a non clustered index seek. I am having sporadic deadlock issues with this update statement and a certain select statement on the table. I understand why SQL is choosing an clustered index scan on the table....Rows < 100 and PageCount < 25.
The table has a large of amount of activity, and since its a 3rd party product I don't have the ability to modify the query and supply an index hint. The query cost of using the non clustered index is more, but I believe it will improve concurrency based on testing....
I need to tell it to use the non clustered index below
WITH (INDEX (ix_jobname_jobgroup_triggerstate))
Help setting this up would be much appreciated..
Asked by Goforebroke
(315 rep)
Mar 3, 2017, 04:08 AM
Last activity: Mar 8, 2017, 12:25 AM
Last activity: Mar 8, 2017, 12:25 AM