SQL Server Plan Guide

Problem

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..

Solution

Ideally, we would like to use a plan guide to add a TABLE HINT, so the guided query becomes:

UPDATE JOB_TRIGGERS 
SET TRIGGER_STATE = @state 
WHERE JOB_NAME = @jobName 
AND JOB_GROUP = @jobGroup 
AND TRIGGER_STATE = @oldState
OPTION (TABLE HINT (JOB_TRIGGERS, INDEX(ix_jobname_jobgroup_triggerstate)));

Unfortunately, this is not possible because the UPDATE is written without a FROM clause:

Msg 8724, Level 16, State 1, Line 45

Cannot execute query. Table-valued or OPENROWSET function 'JOB_TRIGGERS' cannot be specified in the TABLE HINT clause.

You can work around this by capturing the XML showplan for the equivalent:

UPDATE JOB_TRIGGERS
SET TRIGGER_STATE = @state 
FROM JOB_TRIGGERS WITH (INDEX(ix_jobname_jobgroup_triggerstate))
WHERE JOB_NAME = @jobName 
AND JOB_GROUP = @jobGroup 
AND TRIGGER_STATE = @oldState

Note this form of the query has a FROM clause to support the index hint. The query must be written exactly as above, without the usual alias for the target table.

You can then use this XML (without the opening `) element as the @hints parameter in sp_create_plan_guide

.
Example

Given the table and indexes:

CREATE TABLE dbo.JOB_TRIGGERS
(
    JOB_TRIGGERS_ID integer PRIMARY KEY,
    JOB_NAME nvarchar(18) NOT NULL,
    JOB_GROUP nvarchar(28) NOT NULL,
    TRIGGER_STATE nvarchar(6) NOT NULL,
);

CREATE NONCLUSTERED INDEX
    ix_jobname_jobgroup_triggerstate
ON dbo.JOB_TRIGGERS
    (JOB_NAME, JOB_GROUP, TRIGGER_STATE);


The plan guide (using the captured XML from the index-hinted form above) is:

``
EXECUTE sys.sp_create_plan_guide
@name = N'UPDATE JOB_TRIGGERS using nonclustered index',
@stmt = N'UPDATE JOB_TRIGGERS
SET TRIGGER_STATE = @state
WHERE JOB_NAME = @jobName
AND JOB_GROUP = @jobGroup
AND TRIGGER_STATE = @oldState',
@type = N'SQL',
@module_or_batch = NULL,
@params = N'@state nvarchar(14), @jobName nvarchar(18), @jobGroup nvarchar(28), @oldState nvarchar(6)',
@hints = N'

Code Snippets

UPDATE JOB_TRIGGERS 
SET TRIGGER_STATE = @state 
WHERE JOB_NAME = @jobName 
AND JOB_GROUP = @jobGroup 
AND TRIGGER_STATE = @oldState
OPTION (TABLE HINT (JOB_TRIGGERS, INDEX(ix_jobname_jobgroup_triggerstate)));

UPDATE JOB_TRIGGERS
SET TRIGGER_STATE = @state 
FROM JOB_TRIGGERS WITH (INDEX(ix_jobname_jobgroup_triggerstate))
WHERE JOB_NAME = @jobName 
AND JOB_GROUP = @jobGroup 
AND TRIGGER_STATE = @oldState

CREATE TABLE dbo.JOB_TRIGGERS
(
    JOB_TRIGGERS_ID integer PRIMARY KEY,
    JOB_NAME nvarchar(18) NOT NULL,
    JOB_GROUP nvarchar(28) NOT NULL,
    TRIGGER_STATE nvarchar(6) NOT NULL,
);

CREATE NONCLUSTERED INDEX
    ix_jobname_jobgroup_triggerstate
ON dbo.JOB_TRIGGERS
    (JOB_NAME, JOB_GROUP, TRIGGER_STATE);

EXECUTE sys.sp_create_plan_guide 
    @name = N'UPDATE JOB_TRIGGERS using nonclustered index',
    @stmt = N'UPDATE JOB_TRIGGERS 
SET TRIGGER_STATE = @state 
WHERE JOB_NAME = @jobName 
AND JOB_GROUP = @jobGroup 
AND TRIGGER_STATE = @oldState',
    @type = N'SQL',
    @module_or_batch = NULL,
    @params = N'@state nvarchar(14), @jobName nvarchar(18), @jobGroup nvarchar(28), @oldState nvarchar(6)',
    @hints = N'<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.5" Build="13.0.4411.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="1" StatementEstRows="1" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="130" StatementSubTreeCost="0.0365109" StatementText="DECLARE&#xD;&#xA;    @state nvarchar(14),&#xD;&#xA;    @jobName nvarchar(18),&#xD;&#xA;    @jobGroup nvarchar(28),&#xD;&#xA;    @oldState nvarchar(6);&#xD;&#xA;&#xD;&#xA;UPDATE JOB_TRIGGERS&#xD;&#xA;SET TRIGGER_STATE = @state &#xD;&#xA;FROM JOB_TRIGGERS WITH (INDEX(ix_jobname_jobgroup_triggerstate))&#xD;&#xA;WHERE JOB_NAME = @jobName &#xD;&#xA;AND JOB_GROUP = @jobGroup &#xD;&#xA;AND TRIGGER_STATE = @oldState" StatementType="UPDATE" QueryHash="0xA993366BDAC14B06" QueryPlanHash="0x21B868F786AB4C56" RetrievedFromCache="false" SecurityPolicyApplied="false">
          <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
          <QueryPlan CachedPlanSize="40" CompileTime="2" CompileCPU="2" CompileMemory="320">
            <MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" />
            <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="209715" EstimatedPagesCached="52428" EstimatedAvailableDegreeOfParallelism="2" MaxCompileMemory="6723992" />
            <RelOp AvgRowSize="9" EstimateCPU="2E-06" EstimateIO="0.02" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Update" NodeId="1" Parallel="false" PhysicalOp="Clustered Index Update" EstimatedTotalSubtreeCost="0.0365109">
              <OutputList />
              <Update DMLRequestSort="false">
                <Object Database="[Sandpit]" Schema="[dbo]" Table="[JOB_TRIGGERS]" Index="[PK__JOB_TRIG__CF66DD90CB121F34]" IndexKind="Clustered" Storage="RowStore" />
                <Object Database="[Sandpit]" Schema="[dbo]" Table="[JOB_TRIGGERS]" Index="[ix_jobname_jobgroup_triggerstate]" IndexKind="NonClustered" Storage="RowStore" />
                <SetPredicate>
                  <ScalarOperator ScalarString="[Sandpit].[dbo].[JOB_TRIGGERS].[TRIGGER_STATE] = RaiseIfNullUpdate([Expr1002])">
                    <ScalarExpressionList>
                      <ScalarOperator>
                        <

EXECUTE sys.sp_executesql
    @stmt = N'UPDATE JOB_TRIGGERS 
SET TRIGGER_STATE = @state 
WHERE JOB_NAME = @jobName 
AND JOB_GROUP = @jobGroup 
AND TRIGGER_STATE = @oldState',
    @params = N'@state nvarchar(14), @jobName nvarchar(18), @jobGroup nvarchar(28), @oldState nvarchar(6)',
    @state = N'', @jobName = N'', @jobGroup = N'', @oldState = N'';

Context

StackExchange Database Administrators Q#166056, answer score: 7

Revisions (0)

No revisions yet.