主页 / dba / 问题 / 29333
Accepted
Akash
Asked: 2012-11-27 21:28:48 +0800 CST

DBCC TRACEON (652) 不禁用预读读取

  • 772

参考这个 StackOverflow 问题：https ://stackoverflow.com/questions/13307485/sql-server-clearing-cache-has-no-effect-on-io-stats

我使用了跟踪标志 652，它当时正确地禁用了预读功能。

我回到它，它不再这样做了。

询问：

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

统计：

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.

执行计划：

<?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>
sql-server-2012

1 个回答

  1. Best Answer

    跟踪标志 652 通常仍然可以禁用预读（在2012 SP1 CU1 - build 11.0.3321上测试）。例外情况是使用Batch Hash Table Build的哈希连接，在这种情况下，为连接的探测端提供数据的列存储索引仍然使用预读，而不管 TF 652：

    从问题中查询计划

    上图显示了问题的 XML 显示计划输出，加载到SQL Sentry Plan Explorer中。我突出显示了击败 TF 652 所必需的批处理哈希表构建、哈希匹配内部连接和探测端列存储索引扫描计划元素。

    跟踪标志 652 对我尝试过的所有其他列存储计划（行和批处理模式处理）都有效，这表明这要么是疏忽，要么是有意为之，因为这个特定的执行路径在内部的工作方式非常不同。

    跟踪标志 652 的记录非常少，因此我不确定是否支持它。尽管如此，这个问题可能值得在Microsoft 的反馈站点上报告，作为在未来的修补程序、服务包或完整版本中进行增强的建议（我认为它不属于错误）。

    • 10

相关问题