Sql-Server-2012

DBCC TRACEON (652) 不禁用預讀

  • November 14, 2019

參考這個 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>

跟踪標誌 652 通常仍然可以禁用預讀（在2012 SP1 CU1 - build 11.0.3321上測試）。例外情況是使用Batch Hash Table Build的雜湊連接，在這種情況下，為連接的探測端提供數據的列儲存索引仍然使用預讀，而不管 TF 652：

從問題查詢計劃

上圖顯示了問題的 XML 顯示計劃輸出，載入到SQL Sentry Plan Explorer中。我已經強調了擊敗 TF 652 所必需的批量雜湊表建構、雜湊匹配內連接和探測端列儲存索引掃描計劃元素。

跟踪標誌 652 對我嘗試過的所有其他列儲存計劃（行模式和批處理模式處理）都有效，這表明這要麼是疏忽，要麼是故意的，因為這個特定的執行路徑在內部的工作方式非常不同。

跟踪標誌 652 的文件很少，因此我不確定是否可以提供支持。儘管如此，這個問題可能值得在Microsoft 的回饋站點上報告，作為在未來的修補程序、服務包或完整版本中增強的建議（我不認為它符合錯誤的條件）。

引用自：https://dba.stackexchange.com/questions/29333

相關問答

Mysql

關於交易知識

  • August 9, 2022
檢視問答
Sql-Server

在 SQL Server 中的任何會話和任何數據庫中查找打開的事務

  • August 6, 2022
檢視問答
Sql-Server-2012

如何從sql表中過濾數據？

  • August 2, 2022
檢視問答
Sql-Server

如何設置 sqlcmd.exe 退出程式碼？

  • July 24, 2022
檢視問答
Sql-Server

INSERT INTO.. 使用預設關鍵字選擇

  • July 22, 2022
檢視問答
Sql-Server

表 X 上的更新如何鎖定表 Y？

  • July 20, 2022
檢視問答