Sample Header Ad - 728x90

SQL Server Plan Guide

7 votes
1 answer
921 views
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