SQL Server 2012 Criar índice com Sort In TempDb On - Gains False?

Question

Akash

Asked: 2012-11-27 21:28:48 +0800 CST2012-11-27 21:28:48 +0800 CST 2012-11-27 21:28:48 +0800 CST

DBCC TRACEON (652) não desativa as leituras de leitura antecipada

772

Referindo-se a esta pergunta do StackOverflow: https://stackoverflow.com/questions/13307485/sql-server-clearing-cache-has-no-effect-on-io-stats

Usei o sinalizador de rastreamento 652 que, no momento, desativou a função de leitura antecipada corretamente.

Voltei a ele e não o faz mais.

Consulta:

DBCC DROPCLEANBUFFERS
DBCC TRACEON(652,-1)
select a.JDID,a.JDDESC from t1 a with (Index = ccsi) JOIN t2 b on a.manager = b.JDID JOIN t3 c on a.department = c.jdid

Estatísticas:

Table 't1'. Scan count 4, logical reads 5884, physical reads 364, read-ahead reads 13679, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Plano de execução:

<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.2" Build="11.0.2218.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="1" StatementEstRows="566392" StatementId="1" StatementOptmLevel="FULL" StatementSubTreeCost="3.98206" StatementText="select a.JDID,a.JDDESC from t1 a with (Index = ccsi) JOIN t2 b on a.manager = b.JDID JOIN t3 c on a.department = c.jdid" StatementType="SELECT" QueryHash="0xD7458E0CA3CDD337" QueryPlanHash="0xB4104FE9B0A0FBEE" RetrievedFromCache="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="72" CompileTime="16" CompileCPU="16" CompileMemory="472">
            <ThreadStat Branches="3" />
            <MemoryGrantInfo SerialRequiredMemory="2048" SerialDesiredMemory="2952" />
            <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="104846" EstimatedPagesCached="12447" EstimatedAvailableDegreeOfParallelism="2" />
            <RelOp AvgRowSize="65" EstimateCPU="1.75104" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="566392" LogicalOp="Gather Streams" NodeId="0" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="3.98206">
              <OutputList>
                <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t1]" Alias="[a]" Column="JDID" />
                <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t1]" Alias="[a]" Column="JDDESC" />
              </OutputList>
              <Parallelism>
                <RelOp AvgRowSize="65" EstimateCPU="0.215848" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Batch" EstimateRows="566392" LogicalOp="Inner Join" NodeId="1" Parallel="true" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="2.23102">
                  <OutputList>
                    <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t1]" Alias="[a]" Column="JDID" />
                    <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t1]" Alias="[a]" Column="JDDESC" />
                  </OutputList>
                  <MemoryFractions Input="1" Output="1" />
                  <Hash>
                    <DefinedValues />
                    <HashKeysBuild>
                      <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t2]" Alias="[b]" Column="JDID" />
                    </HashKeysBuild>
                    <HashKeysProbe>
                      <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t1]" Alias="[a]" Column="manager" />
                    </HashKeysProbe>
                    <ProbeResidual>
                      <ScalarOperator ScalarString="[bigdatatest].[dbo].[t1].[manager] as [a].[manager]=[bigdatatest].[dbo].[t2].[JDID] as [b].[JDID]">
                        <Compare CompareOp="EQ">
                          <ScalarOperator>
                            <Identifier>
                              <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t1]" Alias="[a]" Column="manager" />
                            </Identifier>
                          </ScalarOperator>
                          <ScalarOperator>
                            <Identifier>
                              <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t2]" Alias="[b]" Column="JDID" />
                            </Identifier>
                          </ScalarOperator>
                        </Compare>
                      </ScalarOperator>
                    </ProbeResidual>
                    <RelOp AvgRowSize="11" EstimateCPU="0.0286198" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="100" LogicalOp="Repartition Streams" NodeId="2" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="0.0623762">
                      <OutputList>
                        <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t2]" Alias="[b]" Column="JDID" />
                      </OutputList>
                      <Parallelism PartitioningType="Hash">
                        <PartitionColumns>
                          <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t2]" Alias="[b]" Column="JDID" />
                        </PartitionColumns>
                        <RelOp AvgRowSize="11" EstimateCPU="0.00093625" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Batch" EstimateRows="100" LogicalOp="Batch Hash Table Build" NodeId="3" Parallel="true" PhysicalOp="Batch Hash Table Build" EstimatedTotalSubtreeCost="0.0337563">
                          <OutputList>
                            <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t2]" Alias="[b]" Column="JDID" />
                          </OutputList>
                          <MemoryFractions Input="0.477876" Output="0.477876" />
                          <BatchHashTableBuild>
                            <RelOp AvgRowSize="11" EstimateCPU="0.0286874" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="100" LogicalOp="Distribute Streams" NodeId="4" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="0.0328201">
                              <OutputList>
                                <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t2]" Alias="[b]" Column="JDID" />
                              </OutputList>
                              <Parallelism PartitioningType="Demand">
                                <RelOp AvgRowSize="11" EstimateCPU="0.000267" EstimateIO="0.00386574" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="100" LogicalOp="Table Scan" NodeId="5" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="0.00413274" TableCardinality="100">
                                  <OutputList>
                                    <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t2]" Alias="[b]" Column="JDID" />
                                  </OutputList>
                                  <TableScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false">
                                    <DefinedValues>
                                      <DefinedValue>
                                        <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t2]" Alias="[b]" Column="JDID" />
                                      </DefinedValue>
                                    </DefinedValues>
                                    <Object Database="[bigdatatest]" Schema="[dbo]" Table="[t2]" Alias="[b]" IndexKind="Heap" />
                                  </TableScan>
                                </RelOp>
                              </Parallelism>
                            </RelOp>
                          </BatchHashTableBuild>
                        </RelOp>
                      </Parallelism>
                    </RelOp>
                    <RelOp AvgRowSize="69" EstimateCPU="0" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="750056" LogicalOp="Repartition Streams" NodeId="6" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="1.95279">
                      <OutputList>
                        <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t1]" Alias="[a]" Column="JDID" />
                        <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t1]" Alias="[a]" Column="JDDESC" />
                        <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t1]" Alias="[a]" Column="manager" />
                      </OutputList>
                      <Parallelism PartitioningType="Hash">
                        <PartitionColumns>
                          <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t1]" Alias="[a]" Column="manager" />
                        </PartitionColumns>
                        <RelOp AvgRowSize="69" EstimateCPU="0.287091" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Batch" EstimateRows="750056" LogicalOp="Inner Join" NodeId="7" Parallel="true" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="1.95279">
                          <OutputList>
                            <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t1]" Alias="[a]" Column="JDID" />
                            <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t1]" Alias="[a]" Column="JDDESC" />
                            <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t1]" Alias="[a]" Column="manager" />
                          </OutputList>
                          <MemoryFractions Input="0.522124" Output="0.522124" />
                          <Hash>
                            <DefinedValues />
                            <HashKeysBuild>
                              <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t3]" Alias="[c]" Column="JDID" />
                            </HashKeysBuild>
                            <HashKeysProbe>
                              <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t1]" Alias="[a]" Column="department" />
                            </HashKeysProbe>
                            <ProbeResidual>
                              <ScalarOperator ScalarString="[bigdatatest].[dbo].[t1].[department] as [a].[department]=[bigdatatest].[dbo].[t3].[JDID] as [c].[JDID]">
                                <Compare CompareOp="EQ">
                                  <ScalarOperator>
                                    <Identifier>
                                      <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t1]" Alias="[a]" Column="department" />
                                    </Identifier>
                                  </ScalarOperator>
                                  <ScalarOperator>
                                    <Identifier>
                                      <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t3]" Alias="[c]" Column="JDID" />
                                    </Identifier>
                                  </ScalarOperator>
                                </Compare>
                              </ScalarOperator>
                            </ProbeResidual>
                            <RelOp AvgRowSize="11" EstimateCPU="0.0286198" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="100" LogicalOp="Repartition Streams" NodeId="8" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="0.0623762">
                              <OutputList>
                                <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t3]" Alias="[c]" Column="JDID" />
                              </OutputList>
                              <Parallelism PartitioningType="Hash">
                                <PartitionColumns>
                                  <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t3]" Alias="[c]" Column="JDID" />
                                </PartitionColumns>
                                <RelOp AvgRowSize="11" EstimateCPU="0.00093625" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Batch" EstimateRows="100" LogicalOp="Batch Hash Table Build" NodeId="9" Parallel="true" PhysicalOp="Batch Hash Table Build" EstimatedTotalSubtreeCost="0.0337563">
                                  <OutputList>
                                    <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t3]" Alias="[c]" Column="JDID" />
                                  </OutputList>
                                  <MemoryFractions Input="0.522124" Output="0.522124" />
                                  <BatchHashTableBuild>
                                    <RelOp AvgRowSize="11" EstimateCPU="0.0286874" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="100" LogicalOp="Distribute Streams" NodeId="10" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="0.0328201">
                                      <OutputList>
                                        <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t3]" Alias="[c]" Column="JDID" />
                                      </OutputList>
                                      <Parallelism PartitioningType="Demand">
                                        <RelOp AvgRowSize="11" EstimateCPU="0.000267" EstimateIO="0.00386574" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="100" LogicalOp="Table Scan" NodeId="11" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="0.00413274" TableCardinality="100">
                                          <OutputList>
                                            <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t3]" Alias="[c]" Column="JDID" />
                                          </OutputList>
                                          <TableScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false">
                                            <DefinedValues>
                                              <DefinedValue>
                                                <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t3]" Alias="[c]" Column="JDID" />
                                              </DefinedValue>
                                            </DefinedValues>
                                            <Object Database="[bigdatatest]" Schema="[dbo]" Table="[t3]" Alias="[c]" IndexKind="Heap" />
                                          </TableScan>
                                        </RelOp>
                                      </Parallelism>
                                    </RelOp>
                                  </BatchHashTableBuild>
                                </RelOp>
                              </Parallelism>
                            </RelOp>
                            <RelOp AvgRowSize="73" EstimateCPU="0" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="999998" LogicalOp="Repartition Streams" NodeId="12" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="1.60332">
                              <OutputList>
                                <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t1]" Alias="[a]" Column="JDID" />
                                <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t1]" Alias="[a]" Column="JDDESC" />
                                <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t1]" Alias="[a]" Column="manager" />
                                <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t1]" Alias="[a]" Column="department" />
                              </OutputList>
                              <Parallelism PartitioningType="Hash">
                                <PartitionColumns>
                                  <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t1]" Alias="[a]" Column="department" />
                                </PartitionColumns>
                                <RelOp AvgRowSize="73" EstimateCPU="0.0550077" EstimateIO="1.54831" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Batch" EstimateRows="999998" LogicalOp="Index Scan" NodeId="13" Parallel="true" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="1.60332" TableCardinality="999998">
                                  <OutputList>
                                    <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t1]" Alias="[a]" Column="JDID" />
                                    <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t1]" Alias="[a]" Column="JDDESC" />
                                    <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t1]" Alias="[a]" Column="manager" />
                                    <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t1]" Alias="[a]" Column="department" />
                                  </OutputList>
                                  <IndexScan Ordered="false" ForcedIndex="true" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="ColumnStore">
                                    <DefinedValues>
                                      <DefinedValue>
                                        <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t1]" Alias="[a]" Column="JDID" />
                                      </DefinedValue>
                                      <DefinedValue>
                                        <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t1]" Alias="[a]" Column="JDDESC" />
                                      </DefinedValue>
                                      <DefinedValue>
                                        <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t1]" Alias="[a]" Column="manager" />
                                      </DefinedValue>
                                      <DefinedValue>
                                        <ColumnReference Database="[bigdatatest]" Schema="[dbo]" Table="[t1]" Alias="[a]" Column="department" />
                                      </DefinedValue>
                                    </DefinedValues>
                                    <Object Database="[bigdatatest]" Schema="[dbo]" Table="[t1]" Index="[ccsi]" Alias="[a]" IndexKind="NonClustered" />
                                  </IndexScan>
                                </RelOp>
                              </Parallelism>
                            </RelOp>
                          </Hash>
                        </RelOp>
                      </Parallelism>
                    </RelOp>
                  </Hash>
                </RelOp>
              </Parallelism>
            </RelOp>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>

1 respostas

Voted

Paul White · Answer 1 · 2012-11-30T05:12:18+08:00

O sinalizador de rastreamento 652 geralmente ainda funciona para desabilitar a leitura antecipada (testado em 2012 SP1 CU1 - build 11.0.3321 ). A exceção a isso é para hash joins usando Batch Hash Table Build , caso em que o índice columnstore que alimenta o lado da sondagem da junção ainda usa read-ahead independentemente do TF 652:

Plano de consulta a partir da pergunta

O diagrama acima mostra a saída do plano de exibição XML da pergunta, carregada no SQL Sentry Plan Explorer . Eu destaquei o Batch Hash Table Build, o Hash Match Inner Join e os elementos do plano Columnstore Index Scan do lado da sonda necessários para derrotar o TF 652.

O sinalizador de rastreamento 652 é eficaz em todos os outros planos de armazenamento de coluna que tentei (processamento em modo de linha e em lote), o que sugere que isso é um descuido ou intencional porque esse caminho de execução específico funciona de maneira muito diferente internamente.

O sinalizador de rastreamento 652 é apenas minimamente documentado, então não tenho certeza de que o suporte estaria disponível para ele. No entanto, pode valer a pena relatar esse problema no site de comentários da Microsoft como uma sugestão (não acredito que se qualifique como um bug) para aprimoramento em um futuro hotfix, service pack ou versão completa.

DBCC TRACEON (652) não desativa as leituras de leitura antecipada

Como ver a lista de bancos de dados no Oracle?

Quão grande deve ser o mysql innodb_buffer_pool_size?

Listar todas as colunas de uma tabela especificada

restaurar a tabela do arquivo .frm e .ibd?

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

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

Como selecionar a primeira linha de cada grupo?

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

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

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

DBCC TRACEON (652) não desativa as leituras de leitura antecipada

1 respostas

relate perguntas