AskOverflow.Dev

AskOverflow.Dev Logo AskOverflow.Dev Logo

AskOverflow.Dev Navigation

  • Início
  • system&network
  • Ubuntu
  • Unix
  • DBA
  • Computer
  • Coding
  • LangChain

Mobile menu

Close
  • Início
  • system&network
    • Recentes
    • Highest score
    • tags
  • Ubuntu
    • Recentes
    • Highest score
    • tags
  • Unix
    • Recentes
    • tags
  • DBA
    • Recentes
    • tags
  • Computer
    • Recentes
    • tags
  • Coding
    • Recentes
    • tags
Início / dba / Perguntas / 166056
Accepted
Goforebroke
Goforebroke
Asked: 2017-03-03 20:08:42 +0800 CST2017-03-03 20:08:42 +0800 CST 2017-03-03 20:08:42 +0800 CST

Guia do Plano do SQL Server

  • 772

Eu tenho uma consulta que precisa de um guia de plano, mas estou tendo dificuldade em configurá-lo.

Consulta abaixo do cache do procedimento...

(@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

O SQL Server opta por executar uma verificação de índice clusterizado em vez de uma busca de índice não clusterizado. Estou tendo problemas esporádicos de deadlock com esta instrução de atualização e uma determinada instrução de seleção na tabela. Entendo por que o SQL está escolhendo uma verificação de índice clusterizado na tabela....Rows < 100 e PageCount < 25.

A tabela tem uma grande quantidade de atividade e, como é um produto de terceiros, não tenho a capacidade de modificar a consulta e fornecer uma dica de índice. O custo de consulta de usar o índice não clusterizado é maior, mas acredito que melhorará a simultaneidade com base em testes....

Eu preciso dizer a ele para usar o índice não clusterizado abaixo

WITH (INDEX (ix_jobname_jobgroup_triggerstate))

Ajuda para configurar isso seria muito apreciada..

sql-server optimization
  • 1 1 respostas
  • 741 Views

1 respostas

  • Voted
  1. Best Answer
    Paul White
    2017-03-08T16:25:11+08:002017-03-08T16:25:11+08:00

    Idealmente, gostaríamos de usar um guia de plano para adicionar um TABLE HINT, para que a consulta guiada se torne:

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

    Infelizmente, isso não é possível porque o UPDATEé escrito sem uma FROMcláusula:

    Msg 8724, Level 16, State 1, Line 45
    Não é possível executar a consulta. A função com valor de tabela ou OPENROWSET 'JOB_TRIGGERS' não pode ser especificada na cláusula TABLE HINT.

    Você pode contornar isso capturando o showplan XML para o equivalente:

    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
    

    Observe que este formulário da consulta tem uma FROMcláusula para dar suporte à dica de índice. A consulta deve ser escrita exatamente como acima, sem o alias usual para a tabela de destino.

    Você pode usar esse elemento XML (sem a abertura <?xml version="1.0" encoding="utf-16"?>) como o @hintsparâmetro em sp_create_plan_guide.

    Exemplo

    Dada a tabela e os índices:

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

    O guia do plano (usando o XML capturado do formulário com sugestão de índice acima) é:

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

    Enviando a consulta:

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

    Dá o plano desejado:

    Plano guiado

    As propriedades do plano mostram que o guia do plano foi usado:

    Propriedades

    Você não pode simplesmente usar o guia de plano exato acima - é apenas um exemplo que funcionou no esquema de brinquedo que criei para reproduzir seu problema. No entanto, o processo geral descrito deve funcionar para você.

    • 7

relate perguntas

  • SQL Server - Como as páginas de dados são armazenadas ao usar um índice clusterizado

  • Preciso de índices separados para cada tipo de consulta ou um índice de várias colunas funcionará?

  • Quando devo usar uma restrição exclusiva em vez de um índice exclusivo?

  • Quais são as principais causas de deadlocks e podem ser evitadas?

  • Como determinar se um Índice é necessário ou necessário

Sidebar

Stats

  • Perguntas 205573
  • respostas 270741
  • best respostas 135370
  • utilizador 68524
  • Highest score
  • respostas
  • Marko Smith

    conectar ao servidor PostgreSQL: FATAL: nenhuma entrada pg_hba.conf para o host

    • 12 respostas
  • Marko Smith

    Como fazer a saída do sqlplus aparecer em uma linha?

    • 3 respostas
  • Marko Smith

    Selecione qual tem data máxima ou data mais recente

    • 3 respostas
  • Marko Smith

    Como faço para listar todos os esquemas no PostgreSQL?

    • 4 respostas
  • Marko Smith

    Listar todas as colunas de uma tabela especificada

    • 5 respostas
  • Marko Smith

    Como usar o sqlplus para se conectar a um banco de dados Oracle localizado em outro host sem modificar meu próprio tnsnames.ora

    • 4 respostas
  • Marko Smith

    Como você mysqldump tabela (s) específica (s)?

    • 4 respostas
  • Marko Smith

    Listar os privilégios do banco de dados usando o psql

    • 10 respostas
  • Marko Smith

    Como inserir valores em uma tabela de uma consulta de seleção no PostgreSQL?

    • 4 respostas
  • Marko Smith

    Como faço para listar todos os bancos de dados e tabelas usando o psql?

    • 7 respostas
  • Martin Hope
    Jin conectar ao servidor PostgreSQL: FATAL: nenhuma entrada pg_hba.conf para o host 2014-12-02 02:54:58 +0800 CST
  • Martin Hope
    Stéphane Como faço para listar todos os esquemas no PostgreSQL? 2013-04-16 11:19:16 +0800 CST
  • Martin Hope
    Mike Walsh Por que o log de transações continua crescendo ou fica sem espaço? 2012-12-05 18:11:22 +0800 CST
  • Martin Hope
    Stephane Rolland Listar todas as colunas de uma tabela especificada 2012-08-14 04:44:44 +0800 CST
  • Martin Hope
    haxney O MySQL pode realizar consultas razoavelmente em bilhões de linhas? 2012-07-03 11:36:13 +0800 CST
  • Martin Hope
    qazwsx Como posso monitorar o andamento de uma importação de um arquivo .sql grande? 2012-05-03 08:54:41 +0800 CST
  • Martin Hope
    markdorison Como você mysqldump tabela (s) específica (s)? 2011-12-17 12:39:37 +0800 CST
  • Martin Hope
    Jonas Como posso cronometrar consultas SQL usando psql? 2011-06-04 02:22:54 +0800 CST
  • Martin Hope
    Jonas Como inserir valores em uma tabela de uma consulta de seleção no PostgreSQL? 2011-05-28 00:33:05 +0800 CST
  • Martin Hope
    Jonas Como faço para listar todos os bancos de dados e tabelas usando o psql? 2011-02-18 00:45:49 +0800 CST

Hot tag

sql-server mysql postgresql sql-server-2014 sql-server-2016 oracle sql-server-2008 database-design query-performance sql-server-2017

Explore

  • Início
  • Perguntas
    • Recentes
    • Highest score
  • tag
  • help

Footer

AskOverflow.Dev

About Us

  • About Us
  • Contact Us

Legal Stuff

  • Privacy Policy

Language

  • Pt
  • Server
  • Unix

© 2023 AskOverflow.DEV All Rights Reserve