SQL Server Optimization – Understanding Plan Guides

execution-planoptimizationplan-guidessql server

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

Best Answer

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 <?xml version="1.0" encoding="utf-16"?>) 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'<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>
                        <MultipleAssign>
                          <Assign>
                            <ColumnReference Database="[Sandpit]" Schema="[dbo]" Table="[JOB_TRIGGERS]" Column="TRIGGER_STATE" />
                            <ScalarOperator>
                              <Intrinsic FunctionName="RaiseIfNullUpdate">
                                <ScalarOperator>
                                  <Identifier>
                                    <ColumnReference Column="Expr1002" />
                                  </Identifier>
                                </ScalarOperator>
                              </Intrinsic>
                            </ScalarOperator>
                          </Assign>
                        </MultipleAssign>
                      </ScalarOperator>
                    </ScalarExpressionList>
                  </ScalarOperator>
                </SetPredicate>
                <RelOp AvgRowSize="25" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="2" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.0165089">
                  <OutputList>
                    <ColumnReference Database="[Sandpit]" Schema="[dbo]" Table="[JOB_TRIGGERS]" Column="JOB_TRIGGERS_ID" />
                    <ColumnReference Column="Expr1002" />
                    <ColumnReference Column="Expr1008" />
                  </OutputList>
                  <ComputeScalar>
                    <DefinedValues>
                      <DefinedValue>
                        <ColumnReference Column="Expr1008" />
                        <ScalarOperator ScalarString="[Expr1008]">
                          <Identifier>
                            <ColumnReference Column="Expr1008" />
                          </Identifier>
                        </ScalarOperator>
                      </DefinedValue>
                    </DefinedValues>
                    <RelOp AvgRowSize="25" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="3" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.0165089">
                      <OutputList>
                        <ColumnReference Database="[Sandpit]" Schema="[dbo]" Table="[JOB_TRIGGERS]" Column="JOB_TRIGGERS_ID" />
                        <ColumnReference Column="Expr1002" />
                        <ColumnReference Column="Expr1008" />
                      </OutputList>
                      <ComputeScalar>
                        <DefinedValues>
                          <DefinedValue>
                            <ColumnReference Column="Expr1008" />
                            <ScalarOperator ScalarString="CASE WHEN [Expr1005] THEN (0) ELSE (1) END">
                              <IF>
                                <Condition>
                                  <ScalarOperator>
                                    <Identifier>
                                      <ColumnReference Column="Expr1005" />
                                    </Identifier>
                                  </ScalarOperator>
                                </Condition>
                                <Then>
                                  <ScalarOperator>
                                    <Const ConstValue="(0)" />
                                  </ScalarOperator>
                                </Then>
                                <Else>
                                  <ScalarOperator>
                                    <Const ConstValue="(1)" />
                                  </ScalarOperator>
                                </Else>
                              </IF>
                            </ScalarOperator>
                          </DefinedValue>
                        </DefinedValues>
                        <RelOp AvgRowSize="22" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="4" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.0165088">
                          <OutputList>
                            <ColumnReference Database="[Sandpit]" Schema="[dbo]" Table="[JOB_TRIGGERS]" Column="JOB_TRIGGERS_ID" />
                            <ColumnReference Column="Expr1002" />
                            <ColumnReference Column="Expr1005" />
                          </OutputList>
                          <ComputeScalar>
                            <DefinedValues>
                              <DefinedValue>
                                <ColumnReference Column="Expr1002" />
                                <ScalarOperator ScalarString="CONVERT_IMPLICIT(nvarchar(6),[@state],0)">
                                  <Identifier>
                                    <ColumnReference Column="ConstExpr1006">
                                      <ScalarOperator>
                                        <Convert DataType="nvarchar" Length="12" Style="0" Implicit="true">
                                          <ScalarOperator>
                                            <Identifier>
                                              <ColumnReference Column="@state" />
                                            </Identifier>
                                          </ScalarOperator>
                                        </Convert>
                                      </ScalarOperator>
                                    </ColumnReference>
                                  </Identifier>
                                </ScalarOperator>
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Column="Expr1005" />
                                <ScalarOperator ScalarString="CASE WHEN [Sandpit].[dbo].[JOB_TRIGGERS].[TRIGGER_STATE] = CONVERT_IMPLICIT(nvarchar(6),[@state],0) THEN (1) ELSE (0) END">
                                  <IF>
                                    <Condition>
                                      <ScalarOperator>
                                        <Compare CompareOp="BINARY IS">
                                          <ScalarOperator>
                                            <Identifier>
                                              <ColumnReference Database="[Sandpit]" Schema="[dbo]" Table="[JOB_TRIGGERS]" Column="TRIGGER_STATE" />
                                            </Identifier>
                                          </ScalarOperator>
                                          <ScalarOperator>
                                            <Identifier>
                                              <ColumnReference Column="ConstExpr1006">
                                                <ScalarOperator>
                                                  <Convert DataType="nvarchar" Length="12" Style="0" Implicit="true">
                                                    <ScalarOperator>
                                                      <Identifier>
                                                        <ColumnReference Column="@state" />
                                                      </Identifier>
                                                    </ScalarOperator>
                                                  </Convert>
                                                </ScalarOperator>
                                              </ColumnReference>
                                            </Identifier>
                                          </ScalarOperator>
                                        </Compare>
                                      </ScalarOperator>
                                    </Condition>
                                    <Then>
                                      <ScalarOperator>
                                        <Const ConstValue="(1)" />
                                      </ScalarOperator>
                                    </Then>
                                    <Else>
                                      <ScalarOperator>
                                        <Const ConstValue="(0)" />
                                      </ScalarOperator>
                                    </Else>
                                  </IF>
                                </ScalarOperator>
                              </DefinedValue>
                            </DefinedValues>
                            <RelOp AvgRowSize="21" EstimateCPU="0.00010056" EstimateIO="0.013125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Eager Spool" NodeId="5" Parallel="false" PhysicalOp="Table Spool" EstimatedTotalSubtreeCost="0.0165087">
                              <OutputList>
                                <ColumnReference Database="[Sandpit]" Schema="[dbo]" Table="[JOB_TRIGGERS]" Column="JOB_TRIGGERS_ID" />
                                <ColumnReference Database="[Sandpit]" Schema="[dbo]" Table="[JOB_TRIGGERS]" Column="TRIGGER_STATE" />
                              </OutputList>
                              <Spool>
                                <RelOp AvgRowSize="21" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" EstimatedRowsRead="1" LogicalOp="Index Seek" NodeId="6" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0">
                                  <OutputList>
                                    <ColumnReference Database="[Sandpit]" Schema="[dbo]" Table="[JOB_TRIGGERS]" Column="JOB_TRIGGERS_ID" />
                                    <ColumnReference Database="[Sandpit]" Schema="[dbo]" Table="[JOB_TRIGGERS]" Column="TRIGGER_STATE" />
                                  </OutputList>
                                  <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="true" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
                                    <DefinedValues>
                                      <DefinedValue>
                                        <ColumnReference Database="[Sandpit]" Schema="[dbo]" Table="[JOB_TRIGGERS]" Column="JOB_TRIGGERS_ID" />
                                      </DefinedValue>
                                      <DefinedValue>
                                        <ColumnReference Database="[Sandpit]" Schema="[dbo]" Table="[JOB_TRIGGERS]" Column="TRIGGER_STATE" />
                                      </DefinedValue>
                                    </DefinedValues>
                                    <Object Database="[Sandpit]" Schema="[dbo]" Table="[JOB_TRIGGERS]" Index="[ix_jobname_jobgroup_triggerstate]" IndexKind="NonClustered" Storage="RowStore" />
                                    <SeekPredicates>
                                      <SeekPredicateNew>
                                        <SeekKeys>
                                          <Prefix ScanType="EQ">
                                            <RangeColumns>
                                              <ColumnReference Database="[Sandpit]" Schema="[dbo]" Table="[JOB_TRIGGERS]" Column="JOB_NAME" />
                                              <ColumnReference Database="[Sandpit]" Schema="[dbo]" Table="[JOB_TRIGGERS]" Column="JOB_GROUP" />
                                              <ColumnReference Database="[Sandpit]" Schema="[dbo]" Table="[JOB_TRIGGERS]" Column="TRIGGER_STATE" />
                                            </RangeColumns>
                                            <RangeExpressions>
                                              <ScalarOperator ScalarString="[@jobName]">
                                                <Identifier>
                                                  <ColumnReference Column="@jobName" />
                                                </Identifier>
                                              </ScalarOperator>
                                              <ScalarOperator ScalarString="[@jobGroup]">
                                                <Identifier>
                                                  <ColumnReference Column="@jobGroup" />
                                                </Identifier>
                                              </ScalarOperator>
                                              <ScalarOperator ScalarString="[@oldState]">
                                                <Identifier>
                                                  <ColumnReference Column="@oldState" />
                                                </Identifier>
                                              </ScalarOperator>
                                            </RangeExpressions>
                                          </Prefix>
                                        </SeekKeys>
                                      </SeekPredicateNew>
                                    </SeekPredicates>
                                  </IndexScan>
                                </RelOp>
                              </Spool>
                            </RelOp>
                          </ComputeScalar>
                        </RelOp>
                      </ComputeScalar>
                    </RelOp>
                  </ComputeScalar>
                </RelOp>
              </Update>
            </RelOp>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>';

Submitting the query:

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'';

Gives the desired plan:

Guided plan

The plan properties show the plan guide was used:

Properties

You cannot just use the exact plan guide above - it is just an example that worked on the toy schema I created to reproduce your issue. Nevertheless, the general process outlined should work for you.

Related Question