主页 / dba / 问题 / 166056
Accepted
Goforebroke
Asked: 2017-03-03 20:08:42 +0800 CST

SQL Server 计划指南

  • 772

我有一个需要计划指南的查询，但我很难设置它。

从过程缓存中查询以下...

(@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 选择执行聚簇索引扫描与非聚簇索引查找。这个更新语句和表上的某个选择语句偶尔会出现死锁问题。我明白为什么 SQL 在表上选择聚集索引扫描....Rows < 100 和 PageCount < 25。

该表有大量活动，并且由于它是第 3 方产品，我无法修改查询并提供索引提示。使用非聚集索引的查询成本更高，但我相信它会根据测试提高并发性......

我需要告诉它使用下面的非聚集索引

WITH (INDEX (ix_jobname_jobgroup_triggerstate))

帮助设置这个将不胜感激..

sql-server optimization

1 个回答

  1. Best Answer

    理想情况下，我们希望使用计划指南来添加一个TABLE HINT，因此引导查询变为：

    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没有FROM子句：

    消息 8724，级别 16，状态 1，第 45 行
    无法执行查询。不能在 TABLE HINT 子句中指定表值或 OPENROWSET 函数“JOB_TRIGGERS”。

    您可以通过捕获等效的 XML 显示计划来解决此问题：

    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

    请注意，这种形式的查询有一个FROM子句来支持索引提示。查询必须完全按照上面的方式编写，没有目标表的常用别名。

    然后，您可以将此 XML（不带开头<?xml version="1.0" encoding="utf-16"?>）元素用作 中的@hints参数sp_create_plan_guide。

    例子

    给定表和索引：

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

    计划指南（使用从上面的索引提示表单中捕获的 XML）是：

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

    提交查询：

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

    给出想要的方案：

    指导计划

    计划属性显示使用了计划指南：

    特性

    您不能只使用上面的确切计划指南 - 它只是一个示例，它适用于我创建的用于重现您的问题的玩具模式。尽管如此，概述的一般过程应该适合您。

    • 7

相关问题