Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- DROP TABLE IF EXISTS dbo.query_plans;
- CREATE TABLE dbo.query_plans (
- plan_name VARCHAR(100),
- query_xml XML
- );
- INSERT INTO query_plans
- VALUES ('NO_PREFETCH',
- N'<?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.481" Build="14.0.3026.27" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
- <BatchSequence>
- <Batch>
- <Statements>
- <StmtSimple StatementCompId="1" StatementEstRows="2503.6" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="140" StatementSubTreeCost="0.708139" StatementText="SELECT * FROM master.sys.objects
OPTION (LOOP JOIN)" StatementType="SELECT" QueryHash="0x84B4BB2E8F6AC67A" QueryPlanHash="0x3DB6320A0293F8BA" 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 NonParallelPlanReason="CouldNotGenerateValidParallelPlan" CachedPlanSize="48" CompileTime="8" CompileCPU="8" CompileMemory="1064">
- <MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" />
- <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="256000" EstimatedPagesCached="64000" EstimatedAvailableDegreeOfParallelism="2" MaxCompileMemory="8208000" />
- <OptimizerStatsUsage>
- <StatisticsInfo Database="[master]" Schema="[sys]" Table="[syssingleobjrefs]" Statistics="[clst]" ModificationCount="178" SamplingPercent="100" LastUpdate="2018-08-04T12:43:56.86" />
- <StatisticsInfo Database="[mssqlsystemresource]" Schema="[sys]" Table="[syspalnames]" Statistics="[value]" ModificationCount="0" SamplingPercent="100" LastUpdate="2018-05-10T16:55:53.12" />
- <StatisticsInfo Database="[master]" Schema="[sys]" Table="[syssingleobjrefs]" Statistics="[_WA_Sys_00000001_0000004A]" ModificationCount="178" SamplingPercent="100" LastUpdate="2018-08-04T12:43:56.86" />
- <StatisticsInfo Database="[master]" Schema="[sys]" Table="[syssingleobjrefs]" Statistics="[_WA_Sys_00000003_0000004A]" ModificationCount="178" SamplingPercent="100" LastUpdate="2018-08-04T12:43:57.04" />
- <StatisticsInfo Database="[master]" Schema="[sys]" Table="[sysschobjs]" Statistics="[nc1]" ModificationCount="14" SamplingPercent="100" LastUpdate="2017-08-22T19:40:37.31" />
- <StatisticsInfo Database="[master]" Schema="[sys]" Table="[sysschobjs]" Statistics="[nc2]" ModificationCount="14" SamplingPercent="100" LastUpdate="2017-08-22T19:40:37.32" />
- <StatisticsInfo Database="[master]" Schema="[sys]" Table="[sysschobjs]" Statistics="[_WA_Sys_00000006_00000022]" ModificationCount="14" SamplingPercent="100" LastUpdate="2017-08-22T19:40:34.72" />
- <StatisticsInfo Database="[master]" Schema="[sys]" Table="[syssingleobjrefs]" Statistics="[nc1]" ModificationCount="178" SamplingPercent="100" LastUpdate="2018-08-04T12:43:57.04" />
- <StatisticsInfo Database="[master]" Schema="[sys]" Table="[sysschobjs]" Statistics="[clst]" ModificationCount="14" SamplingPercent="100" LastUpdate="2017-08-22T19:40:34.72" />
- <StatisticsInfo Database="[mssqlsystemresource]" Schema="[sys]" Table="[syspalnames]" Statistics="[cl]" ModificationCount="0" SamplingPercent="100" LastUpdate="2018-05-10T16:54:52.24" />
- <StatisticsInfo Database="[master]" Schema="[sys]" Table="[sysschobjs]" Statistics="[nc3]" ModificationCount="14" SamplingPercent="100" LastUpdate="2017-08-22T19:40:37.32" />
- <StatisticsInfo Database="[master]" Schema="[sys]" Table="[sysschobjs]" Statistics="[_WA_Sys_00000008_00000022]" ModificationCount="14" SamplingPercent="100" LastUpdate="2017-08-22T19:40:44.49" />
- </OptimizerStatsUsage>
- <TraceFlags IsCompileTime="true">
- <TraceFlag Value="9806" Scope="Global" />
- <TraceFlag Value="9807" Scope="Global" />
- <TraceFlag Value="9808" Scope="Global" />
- </TraceFlags>
- <RelOp AvgRowSize="237" EstimateCPU="0.0102034" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="2503.6" LogicalOp="Left Outer Join" NodeId="0" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.708139">
- <OutputList>
- <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="id" />
- <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="name" />
- <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="nsid" />
- <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="pid" />
- <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="created" />
- <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="modified" />
- <ColumnReference Database="[master]" Schema="[sys]" Table="[syssingleobjrefs]" Alias="[r]" Column="indepid" />
- <ColumnReference Database="[mssqlsystemresource]" Schema="[sys]" Table="[syspalnames]" Alias="[n]" Column="name" />
- <ColumnReference Column="Expr1003" />
- <ColumnReference Column="Expr1004" />
- <ColumnReference Column="Expr1007" />
- <ColumnReference Column="Expr1008" />
- </OutputList>
- <NestedLoops Optimized="false">
- <OuterReferences>
- <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="type" />
- </OuterReferences>
- <RelOp AvgRowSize="177" EstimateCPU="0.0102034" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="2441" LogicalOp="Left Outer Join" NodeId="1" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.308888">
- <OutputList>
- <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="id" />
- <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="name" />
- <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="nsid" />
- <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="type" />
- <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="pid" />
- <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="created" />
- <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="modified" />
- <ColumnReference Database="[master]" Schema="[sys]" Table="[syssingleobjrefs]" Alias="[r]" Column="indepid" />
- <ColumnReference Column="Expr1003" />
- <ColumnReference Column="Expr1004" />
- <ColumnReference Column="Expr1007" />
- <ColumnReference Column="Expr1008" />
- </OutputList>
- <NestedLoops Optimized="false">
- <Predicate>
- <ScalarOperator ScalarString="[master].[sys].[syssingleobjrefs].[depid] as [r].[depid]=[master].[sys].[sysschobjs].[id] as [o].[id]">
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[master]" Schema="[sys]" Table="[syssingleobjrefs]" Alias="[r]" Column="depid" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="id" />
- </Identifier>
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </Predicate>
- <RelOp AvgRowSize="173" EstimateCPU="0.00385678" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="2441" LogicalOp="Filter" NodeId="2" Parallel="false" PhysicalOp="Filter" EstimatedTotalSubtreeCost="0.0396976">
- <OutputList>
- <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="id" />
- <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="name" />
- <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="nsid" />
- <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="type" />
- <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="pid" />
- <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="created" />
- <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="modified" />
- <ColumnReference Column="Expr1003" />
- <ColumnReference Column="Expr1004" />
- <ColumnReference Column="Expr1007" />
- <ColumnReference Column="Expr1008" />
- </OutputList>
- <Filter StartupExpression="false">
- <RelOp AvgRowSize="175" EstimateCPU="0.0002441" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="2441" LogicalOp="Compute Scalar" NodeId="3" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.0358408">
- <OutputList>
- <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="id" />
- <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="name" />
- <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="nsid" />
- <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="type" />
- <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="pid" />
- <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="created" />
- <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="modified" />
- <ColumnReference Column="Expr1003" />
- <ColumnReference Column="Expr1004" />
- <ColumnReference Column="Expr1007" />
- <ColumnReference Column="Expr1008" />
- </OutputList>
- <ComputeScalar>
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Column="Expr1003" />
- <ScalarOperator ScalarString="CONVERT(char(2),CASE WHEN [master].[sys].[sysschobjs].[type] as [o].[hobjs].[type] as [o].[type] END,0)">
- <Convert DataType="char" Length="2" Style="0" Implicit="false">
- <ScalarOperator>
- <IF>
- <Condition>
- <ScalarOperator>
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="type" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Const ConstValue="" />
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </Condition>
- <Then>
- <ScalarOperator>
- <Const ConstValue="" />
- </ScalarOperator>
- </Then>
- <Else>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="type" />
- </Identifier>
- </ScalarOperator>
- </Else>
- </IF>
- </ScalarOperator>
- </Convert>
- </ScalarOperator>
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Column="Expr1004" />
- <ScalarOperator ScalarString="CONVERT(bit,[master].[sys].[sysschobjs].[status] as [o].[status]&(1),0)">
- <Convert DataType="bit" Style="0" Implicit="false">
- <ScalarOperator>
- <Arithmetic Operation="BIT_AND">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="status" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Const ConstValue="(1)" />
- </ScalarOperator>
- </Arithmetic>
- </ScalarOperator>
- </Convert>
- </ScalarOperator>
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Column="Expr1007" />
- <ScalarOperator ScalarString="CONVERT(bit,[master].[sys].[sysschobjs].[status] as [o].[status]&(16),0)">
- <Convert DataType="bit" Style="0" Implicit="false">
- <ScalarOperator>
- <Arithmetic Operation="BIT_AND">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="status" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Const ConstValue="(16)" />
- </ScalarOperator>
- </Arithmetic>
- </ScalarOperator>
- </Convert>
- </ScalarOperator>
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Column="Expr1008" />
- <ScalarOperator ScalarString="CONVERT(bit,[master].[sys].[sysschobjs].[status] as [o].[status]&(64),0)">
- <Convert DataType="bit" Style="0" Implicit="false">
- <ScalarOperator>
- <Arithmetic Operation="BIT_AND">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="status" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Const ConstValue="(64)" />
- </ScalarOperator>
- </Arithmetic>
- </ScalarOperator>
- </Convert>
- </ScalarOperator>
- </DefinedValue>
- </DefinedValues>
- <RelOp AvgRowSize="176" EstimateCPU="0.0028421" EstimateIO="0.0327546" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="2441" EstimatedRowsRead="2441" LogicalOp="Clustered Index Scan" NodeId="4" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0355967" TableCardinality="2441">
- <OutputList>
- <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="id" />
- <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="name" />
- <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="nsid" />
- <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="status" />
- <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="type" />
- <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="pid" />
- <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="created" />
- <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="modified" />
- </OutputList>
- <IndexScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="id" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="name" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="nsid" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="status" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="type" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="pid" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="created" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="modified" />
- </DefinedValue>
- </DefinedValues>
- <Object Database="[master]" Schema="[sys]" Table="[sysschobjs]" Index="[clst]" Alias="[o]" IndexKind="Clustered" Storage="RowStore" />
- <Predicate>
- <ScalarOperator ScalarString="[master].[sys].[sysschobjs].[nsclass] as [o].[nsclass]=(0) AND [master].[sys].[sysschobjs].[pclass] as [o].[pclass]=(1)">
- <Logical Operation="AND">
- <ScalarOperator>
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="nsclass" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Const ConstValue="(0)" />
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- <ScalarOperator>
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="pclass" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Const ConstValue="(1)" />
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </Logical>
- </ScalarOperator>
- </Predicate>
- </IndexScan>
- </RelOp>
- </ComputeScalar>
- </RelOp>
- <Predicate>
- <ScalarOperator ScalarString="has_access(,[master].[sys].[sysschobjs].[id] as [o].[id])=(1)">
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Intrinsic FunctionName="has_access">
- <ScalarOperator>
- <Const ConstValue="" />
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="id" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Const ConstValue="" />
- </ScalarOperator>
- <ScalarOperator>
- <Const ConstValue="" />
- </ScalarOperator>
- </Intrinsic>
- </ScalarOperator>
- <ScalarOperator>
- <Const ConstValue="(1)" />
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </Predicate>
- </Filter>
- </RelOp>
- <RelOp AvgRowSize="15" EstimateCPU="0.00010038" EstimateIO="0.01" EstimateRebinds="0" EstimateRewinds="2440" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Lazy Spool" NodeId="5" Parallel="false" PhysicalOp="Table Spool" EstimatedTotalSubtreeCost="0.258255">
- <OutputList>
- <ColumnReference Database="[master]" Schema="[sys]" Table="[syssingleobjrefs]" Alias="[r]" Column="depid" />
- <ColumnReference Database="[master]" Schema="[sys]" Table="[syssingleobjrefs]" Alias="[r]" Column="indepid" />
- </OutputList>
- <Spool>
- <RelOp AvgRowSize="20" EstimateCPU="0.0003979" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" EstimatedRowsRead="219" LogicalOp="Index Scan" NodeId="6" Parallel="false" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="0.0035229" TableCardinality="219">
- <OutputList>
- <ColumnReference Database="[master]" Schema="[sys]" Table="[syssingleobjrefs]" Alias="[r]" Column="depid" />
- <ColumnReference Database="[master]" Schema="[sys]" Table="[syssingleobjrefs]" Alias="[r]" Column="indepid" />
- </OutputList>
- <IndexScan Ordered="false" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Database="[master]" Schema="[sys]" Table="[syssingleobjrefs]" Alias="[r]" Column="depid" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[master]" Schema="[sys]" Table="[syssingleobjrefs]" Alias="[r]" Column="indepid" />
- </DefinedValue>
- </DefinedValues>
- <Object Database="[master]" Schema="[sys]" Table="[syssingleobjrefs]" Index="[nc1]" Alias="[r]" IndexKind="NonClustered" Storage="RowStore" />
- <Predicate>
- <ScalarOperator ScalarString="[master].[sys].[syssingleobjrefs].[class] as [r].[class]=(97) AND [master].[sys].[syssingleobjrefs].[depsubid] as [r].[depsubid]=(0)">
- <Logical Operation="AND">
- <ScalarOperator>
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[master]" Schema="[sys]" Table="[syssingleobjrefs]" Alias="[r]" Column="class" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Const ConstValue="(97)" />
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- <ScalarOperator>
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[master]" Schema="[sys]" Table="[syssingleobjrefs]" Alias="[r]" Column="depsubid" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Const ConstValue="(0)" />
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </Logical>
- </ScalarOperator>
- </Predicate>
- </IndexScan>
- </RelOp>
- </Spool>
- </RelOp>
- </NestedLoops>
- </RelOp>
- <RelOp AvgRowSize="71" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="1455.86" EstimateRewinds="984.144" EstimatedExecutionMode="Row" EstimateRows="1" EstimatedRowsRead="1" LogicalOp="Clustered Index Seek" NodeId="7" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.389047" TableCardinality="155">
- <OutputList>
- <ColumnReference Database="[mssqlsystemresource]" Schema="[sys]" Table="[syspalnames]" Alias="[n]" Column="name" />
- </OutputList>
- <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Database="[mssqlsystemresource]" Schema="[sys]" Table="[syspalnames]" Alias="[n]" Column="name" />
- </DefinedValue>
- </DefinedValues>
- <Object Database="[mssqlsystemresource]" Schema="[sys]" Table="[syspalnames]" Index="[cl]" Alias="[n]" IndexKind="Clustered" Storage="RowStore" />
- <SeekPredicates>
- <SeekPredicateNew>
- <SeekKeys>
- <Prefix ScanType="EQ">
- <RangeColumns>
- <ColumnReference Database="[mssqlsystemresource]" Schema="[sys]" Table="[syspalnames]" Alias="[n]" Column="class" />
- <ColumnReference Database="[mssqlsystemresource]" Schema="[sys]" Table="[syspalnames]" Alias="[n]" Column="value" />
- </RangeColumns>
- <RangeExpressions>
- <ScalarOperator ScalarString="">
- <Const ConstValue="" />
- </ScalarOperator>
- <ScalarOperator ScalarString="[master].[sys].[sysschobjs].[type] as [o].[type]">
- <Identifier>
- <ColumnReference Database="[master]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="type" />
- </Identifier>
- </ScalarOperator>
- </RangeExpressions>
- </Prefix>
- </SeekKeys>
- </SeekPredicateNew>
- </SeekPredicates>
- </IndexScan>
- </RelOp>
- </NestedLoops>
- </RelOp>
- </QueryPlan>
- </StmtSimple>
- </Statements>
- </Batch>
- </BatchSequence>
- </ShowPlanXML>'
- );
- INSERT INTO query_plans
- VALUES ('1_PREFETCH',
- '<?xml version="1.0" encoding="UTF-8"?><ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.481" Build="14.0.3030.27">
- <BatchSequence>
- <Batch>
- <Statements>
- <StmtSimple StatementCompId="1" StatementEstRows="1000" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="140" StatementSubTreeCost="0.0243889" StatementText="SELECT TOP (1000)
 P.[Name],
 TH.TransactionID
FROM Production.Product AS P
JOIN Production.TransactionHistory AS TH
 ON TH.ProductID = P.ProductID
WHERE
 P.[Name] LIKE N" StatementType="SELECT" QueryHash="0xAB7531CF8CCBBA6C" QueryPlanHash="0xB3326E6616DC05B3" 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="32" CompileTime="2" CompileCPU="2" CompileMemory="368">
- <MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" />
- <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="209715" EstimatedPagesCached="104857" EstimatedAvailableDegreeOfParallelism="4" MaxCompileMemory="10333384" />
- <OptimizerStatsUsage>
- <StatisticsInfo Database="[AdventureWorks2017]" Schema="[Production]" Table="[Product]" Statistics="[AK_Product_Name]" ModificationCount="0" SamplingPercent="100" LastUpdate="2017-10-27T14:33:08.7" />
- <StatisticsInfo Database="[AdventureWorks2017]" Schema="[Production]" Table="[Product]" Statistics="[PK_Product_ProductID]" ModificationCount="0" SamplingPercent="100" LastUpdate="2017-10-27T14:33:07.3" />
- <StatisticsInfo Database="[AdventureWorks2017]" Schema="[Production]" Table="[TransactionHistory]" Statistics="[IX_TransactionHistory_ReferenceOrderID_ReferenceOrderLineID]" ModificationCount="0" SamplingPercent="100" LastUpdate="2017-10-27T14:33:09.69" />
- <StatisticsInfo Database="[AdventureWorks2017]" Schema="[Production]" Table="[TransactionHistory]" Statistics="[IX_TransactionHistory_ProductID]" ModificationCount="0" SamplingPercent="100" LastUpdate="2017-10-27T14:33:09.58" />
- </OptimizerStatsUsage>
- <RelOp AvgRowSize="65" EstimateCPU="0.0001" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1000" LogicalOp="Top" NodeId="0" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="0.0243889">
- <OutputList>
- <ColumnReference Database="[AdventureWorks2017]" Schema="[Production]" Table="[Product]" Alias="[P]" Column="Name" />
- <ColumnReference Database="[AdventureWorks2017]" Schema="[Production]" Table="[TransactionHistory]" Alias="[TH]" Column="TransactionID" />
- </OutputList>
- <Top RowCount="false" IsPercent="false" WithTies="false">
- <TopExpression>
- <ScalarOperator ScalarString="(1000)">
- <Const ConstValue="(1000)" />
- </ScalarOperator>
- </TopExpression>
- <RelOp AvgRowSize="65" EstimateCPU="0.232257" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1000" LogicalOp="Inner Join" NodeId="1" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.0242889">
- <OutputList>
- <ColumnReference Database="[AdventureWorks2017]" Schema="[Production]" Table="[Product]" Alias="[P]" Column="Name" />
- <ColumnReference Database="[AdventureWorks2017]" Schema="[Production]" Table="[TransactionHistory]" Alias="[TH]" Column="TransactionID" />
- </OutputList>
- <NestedLoops Optimized="false" WithUnorderedPrefetch="true">
- <OuterReferences>
- <ColumnReference Database="[AdventureWorks2017]" Schema="[Production]" Table="[Product]" Alias="[P]" Column="ProductID" />
- <ColumnReference Column="Expr1002" />
- </OuterReferences>
- <RelOp AvgRowSize="65" EstimateCPU="0.0003946" EstimateIO="0.00386574" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="3.88741" EstimatedRowsRead="216" LogicalOp="Index Seek" NodeId="3" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.00329622" TableCardinality="504">
- <OutputList>
- <ColumnReference Database="[AdventureWorks2017]" Schema="[Production]" Table="[Product]" Alias="[P]" Column="ProductID" />
- <ColumnReference Database="[AdventureWorks2017]" Schema="[Production]" Table="[Product]" Alias="[P]" Column="Name" />
- </OutputList>
- <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Database="[AdventureWorks2017]" Schema="[Production]" Table="[Product]" Alias="[P]" Column="ProductID" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[AdventureWorks2017]" Schema="[Production]" Table="[Product]" Alias="[P]" Column="Name" />
- </DefinedValue>
- </DefinedValues>
- <Object Database="[AdventureWorks2017]" Schema="[Production]" Table="[Product]" Index="[AK_Product_Name]" Alias="[P]" IndexKind="NonClustered" Storage="RowStore" />
- <SeekPredicates>
- <SeekPredicateNew>
- <SeekKeys>
- <StartRange ScanType="GE">
- <RangeColumns>
- <ColumnReference Database="[AdventureWorks2017]" Schema="[Production]" Table="[Product]" Alias="[P]" Column="Name" />
- </RangeColumns>
- <RangeExpressions>
- <ScalarOperator ScalarString="NK">
- <Const ConstValue="N" />
- </ScalarOperator>
- </RangeExpressions>
- </StartRange>
- <EndRange ScanType="LT">
- <RangeColumns>
- <ColumnReference Database="[AdventureWorks2017]" Schema="[Production]" Table="[Product]" Alias="[P]" Column="Name" />
- </RangeColumns>
- <RangeExpressions>
- <ScalarOperator ScalarString="N">
- <Const ConstValue="N" />
- </ScalarOperator>
- </RangeExpressions>
- </EndRange>
- </SeekKeys>
- </SeekPredicateNew>
- </SeekPredicates>
- <Predicate>
- <ScalarOperator ScalarString="[AdventureWorks2017].[Production].[Product].[Name] as [P].[Name] like ">
- <Intrinsic FunctionName="like">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[AdventureWorks2017]" Schema="[Production]" Table="[Product]" Alias="[P]" Column="Name" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Const ConstValue="N" />
- </ScalarOperator>
- </Intrinsic>
- </ScalarOperator>
- </Predicate>
- </IndexScan>
- </RelOp>
- <RelOp AvgRowSize="11" EstimateCPU="0.000439964" EstimateIO="0.003125" EstimateRebinds="3.86942" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="257.24" EstimatedRowsRead="257.24" LogicalOp="Index Seek" NodeId="4" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0170807" TableCardinality="113443">
- <OutputList>
- <ColumnReference Database="[AdventureWorks2017]" Schema="[Production]" Table="[TransactionHistory]" Alias="[TH]" Column="TransactionID" />
- </OutputList>
- <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Database="[AdventureWorks2017]" Schema="[Production]" Table="[TransactionHistory]" Alias="[TH]" Column="TransactionID" />
- </DefinedValue>
- </DefinedValues>
- <Object Database="[AdventureWorks2017]" Schema="[Production]" Table="[TransactionHistory]" Index="[IX_TransactionHistory_ProductID]" Alias="[TH]" IndexKind="NonClustered" Storage="RowStore" />
- <SeekPredicates>
- <SeekPredicateNew>
- <SeekKeys>
- <Prefix ScanType="EQ">
- <RangeColumns>
- <ColumnReference Database="[AdventureWorks2017]" Schema="[Production]" Table="[TransactionHistory]" Alias="[TH]" Column="ProductID" />
- </RangeColumns>
- <RangeExpressions>
- <ScalarOperator ScalarString="[AdventureWorks2017].[Production].[Product].[ProductID] as [P].[ProductID]">
- <Identifier>
- <ColumnReference Database="[AdventureWorks2017]" Schema="[Production]" Table="[Product]" Alias="[P]" Column="ProductID" />
- </Identifier>
- </ScalarOperator>
- </RangeExpressions>
- </Prefix>
- </SeekKeys>
- </SeekPredicateNew>
- </SeekPredicates>
- </IndexScan>
- </RelOp>
- </NestedLoops>
- </RelOp>
- </Top>
- </RelOp>
- </QueryPlan>
- </StmtSimple>
- </Statements>
- </Batch>
- </BatchSequence>
- </ShowPlanXML>'
- );
- INSERT INTO query_plans
- VALUES ('2_PREFETCH',
- '<?xml version="1.0" encoding="UTF-8"?><ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.481" Build="14.0.3030.27">
- <BatchSequence>
- <Batch>
- <Statements>
- <StmtSimple StatementCompId="1" StatementEstRows="2000" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="140" StatementSubTreeCost="0.0482231" StatementText="SELECT TOP (1000)
 P.[Name],
 TH.TransactionID
FROM Production.Product AS P
JOIN Production.TransactionHistory AS TH
 ON TH.ProductID = P.ProductID
WHERE
 P.[Name] LIKE N
UNION ALL
SELECT TOP (1000)
 P.[Name],
 TH.TransactionID
FROM Production.Product AS P
JOIN Production.TransactionHistory AS TH
 ON TH.ProductID = P.ProductID
WHERE
 P.[Name] LIKE " StatementType="SELECT" QueryHash="0xC5F675F12B18834C" QueryPlanHash="0x0EE16630F5A8C15F" 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="48" CompileTime="3" CompileCPU="3" CompileMemory="528">
- <MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" />
- <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="209715" EstimatedPagesCached="104857" EstimatedAvailableDegreeOfParallelism="4" MaxCompileMemory="10333496" />
- <OptimizerStatsUsage>
- <StatisticsInfo Database="[AdventureWorks2017]" Schema="[Production]" Table="[Product]" Statistics="[AK_Product_Name]" ModificationCount="0" SamplingPercent="100" LastUpdate="2017-10-27T14:33:08.7" />
- <StatisticsInfo Database="[AdventureWorks2017]" Schema="[Production]" Table="[Product]" Statistics="[PK_Product_ProductID]" ModificationCount="0" SamplingPercent="100" LastUpdate="2017-10-27T14:33:07.3" />
- <StatisticsInfo Database="[AdventureWorks2017]" Schema="[Production]" Table="[TransactionHistory]" Statistics="[IX_TransactionHistory_ReferenceOrderID_ReferenceOrderLineID]" ModificationCount="0" SamplingPercent="100" LastUpdate="2017-10-27T14:33:09.69" />
- <StatisticsInfo Database="[AdventureWorks2017]" Schema="[Production]" Table="[TransactionHistory]" Statistics="[IX_TransactionHistory_ProductID]" ModificationCount="0" SamplingPercent="100" LastUpdate="2017-10-27T14:33:09.58" />
- </OptimizerStatsUsage>
- <RelOp AvgRowSize="65" EstimateCPU="0.0002" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="2000" LogicalOp="Concatenation" NodeId="0" Parallel="false" PhysicalOp="Concatenation" EstimatedTotalSubtreeCost="0.0482231">
- <OutputList>
- <ColumnReference Column="Union1004" />
- <ColumnReference Column="Union1005" />
- </OutputList>
- <Concat>
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Column="Union1004" />
- <ColumnReference Database="[AdventureWorks2017]" Schema="[Production]" Table="[Product]" Alias="[P]" Column="Name" />
- <ColumnReference Database="[AdventureWorks2017]" Schema="[Production]" Table="[Product]" Alias="[P]" Column="Name" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Column="Union1005" />
- <ColumnReference Database="[AdventureWorks2017]" Schema="[Production]" Table="[TransactionHistory]" Alias="[TH]" Column="TransactionID" />
- <ColumnReference Database="[AdventureWorks2017]" Schema="[Production]" Table="[TransactionHistory]" Alias="[TH]" Column="TransactionID" />
- </DefinedValue>
- </DefinedValues>
- <RelOp AvgRowSize="65" EstimateCPU="0.0001" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1000" LogicalOp="Top" NodeId="1" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="0.0243889">
- <OutputList>
- <ColumnReference Database="[AdventureWorks2017]" Schema="[Production]" Table="[Product]" Alias="[P]" Column="Name" />
- <ColumnReference Database="[AdventureWorks2017]" Schema="[Production]" Table="[TransactionHistory]" Alias="[TH]" Column="TransactionID" />
- </OutputList>
- <Top RowCount="false" IsPercent="false" WithTies="false">
- <TopExpression>
- <ScalarOperator ScalarString="(1000)">
- <Const ConstValue="(1000)" />
- </ScalarOperator>
- </TopExpression>
- <RelOp AvgRowSize="65" EstimateCPU="0.232257" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1000" LogicalOp="Inner Join" NodeId="2" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.0242889">
- <OutputList>
- <ColumnReference Database="[AdventureWorks2017]" Schema="[Production]" Table="[Product]" Alias="[P]" Column="Name" />
- <ColumnReference Database="[AdventureWorks2017]" Schema="[Production]" Table="[TransactionHistory]" Alias="[TH]" Column="TransactionID" />
- </OutputList>
- <NestedLoops Optimized="false" WithUnorderedPrefetch="true">
- <OuterReferences>
- <ColumnReference Database="[AdventureWorks2017]" Schema="[Production]" Table="[Product]" Alias="[P]" Column="ProductID" />
- <ColumnReference Column="Expr1006" />
- </OuterReferences>
- <RelOp AvgRowSize="65" EstimateCPU="0.0003946" EstimateIO="0.00386574" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="3.88741" EstimatedRowsRead="216" LogicalOp="Index Seek" NodeId="4" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.00329622" TableCardinality="504">
- <OutputList>
- <ColumnReference Database="[AdventureWorks2017]" Schema="[Production]" Table="[Product]" Alias="[P]" Column="ProductID" />
- <ColumnReference Database="[AdventureWorks2017]" Schema="[Production]" Table="[Product]" Alias="[P]" Column="Name" />
- </OutputList>
- <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Database="[AdventureWorks2017]" Schema="[Production]" Table="[Product]" Alias="[P]" Column="ProductID" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[AdventureWorks2017]" Schema="[Production]" Table="[Product]" Alias="[P]" Column="Name" />
- </DefinedValue>
- </DefinedValues>
- <Object Database="[AdventureWorks2017]" Schema="[Production]" Table="[Product]" Index="[AK_Product_Name]" Alias="[P]" TableReferenceId="1" IndexKind="NonClustered" Storage="RowStore" />
- <SeekPredicates>
- <SeekPredicateNew>
- <SeekKeys>
- <StartRange ScanType="GE">
- <RangeColumns>
- <ColumnReference Database="[AdventureWorks2017]" Schema="[Production]" Table="[Product]" Alias="[P]" Column="Name" />
- </RangeColumns>
- <RangeExpressions>
- <ScalarOperator ScalarString="">
- <Const ConstValue="N" />
- </ScalarOperator>
- </RangeExpressions>
- </StartRange>
- <EndRange ScanType="LT">
- <RangeColumns>
- <ColumnReference Database="[AdventureWorks2017]" Schema="[Production]" Table="[Product]" Alias="[P]" Column="Name" />
- </RangeColumns>
- <RangeExpressions>
- <ScalarOperator ScalarString="N">
- <Const ConstValue="N" />
- </ScalarOperator>
- </RangeExpressions>
- </EndRange>
- </SeekKeys>
- </SeekPredicateNew>
- </SeekPredicates>
- <Predicate>
- <ScalarOperator ScalarString="[AdventureWorks2017].[Production].[Product].[Name] as [P].[Name] like N">
- <Intrinsic FunctionName="like">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[AdventureWorks2017]" Schema="[Production]" Table="[Product]" Alias="[P]" Column="Name" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Const ConstValue="N" />
- </ScalarOperator>
- </Intrinsic>
- </ScalarOperator>
- </Predicate>
- </IndexScan>
- </RelOp>
- <RelOp AvgRowSize="11" EstimateCPU="0.000439964" EstimateIO="0.003125" EstimateRebinds="3.86942" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="257.24" EstimatedRowsRead="257.24" LogicalOp="Index Seek" NodeId="5" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0170807" TableCardinality="113443">
- <OutputList>
- <ColumnReference Database="[AdventureWorks2017]" Schema="[Production]" Table="[TransactionHistory]" Alias="[TH]" Column="TransactionID" />
- </OutputList>
- <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Database="[AdventureWorks2017]" Schema="[Production]" Table="[TransactionHistory]" Alias="[TH]" Column="TransactionID" />
- </DefinedValue>
- </DefinedValues>
- <Object Database="[AdventureWorks2017]" Schema="[Production]" Table="[TransactionHistory]" Index="[IX_TransactionHistory_ProductID]" Alias="[TH]" TableReferenceId="1" IndexKind="NonClustered" Storage="RowStore" />
- <SeekPredicates>
- <SeekPredicateNew>
- <SeekKeys>
- <Prefix ScanType="EQ">
- <RangeColumns>
- <ColumnReference Database="[AdventureWorks2017]" Schema="[Production]" Table="[TransactionHistory]" Alias="[TH]" Column="ProductID" />
- </RangeColumns>
- <RangeExpressions>
- <ScalarOperator ScalarString="[AdventureWorks2017].[Production].[Product].[ProductID] as [P].[ProductID]">
- <Identifier>
- <ColumnReference Database="[AdventureWorks2017]" Schema="[Production]" Table="[Product]" Alias="[P]" Column="ProductID" />
- </Identifier>
- </ScalarOperator>
- </RangeExpressions>
- </Prefix>
- </SeekKeys>
- </SeekPredicateNew>
- </SeekPredicates>
- </IndexScan>
- </RelOp>
- </NestedLoops>
- </RelOp>
- </Top>
- </RelOp>
- <RelOp AvgRowSize="65" EstimateCPU="0.0001" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1000" LogicalOp="Top" NodeId="6" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="0.0236342">
- <OutputList>
- <ColumnReference Database="[AdventureWorks2017]" Schema="[Production]" Table="[Product]" Alias="[P]" Column="Name" />
- <ColumnReference Database="[AdventureWorks2017]" Schema="[Production]" Table="[TransactionHistory]" Alias="[TH]" Column="TransactionID" />
- </OutputList>
- <Top RowCount="false" IsPercent="false" WithTies="false">
- <TopExpression>
- <ScalarOperator ScalarString="(1000)">
- <Const ConstValue="(1000)" />
- </ScalarOperator>
- </TopExpression>
- <RelOp AvgRowSize="65" EstimateCPU="0.0464514" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1000" LogicalOp="Inner Join" NodeId="7" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.0235342">
- <OutputList>
- <ColumnReference Database="[AdventureWorks2017]" Schema="[Production]" Table="[Product]" Alias="[P]" Column="Name" />
- <ColumnReference Database="[AdventureWorks2017]" Schema="[Production]" Table="[TransactionHistory]" Alias="[TH]" Column="TransactionID" />
- </OutputList>
- <NestedLoops Optimized="false" WithUnorderedPrefetch="true">
- <OuterReferences>
- <ColumnReference Database="[AdventureWorks2017]" Schema="[Production]" Table="[Product]" Alias="[P]" Column="ProductID" />
- <ColumnReference Column="Expr1007" />
- </OuterReferences>
- <RelOp AvgRowSize="65" EstimateCPU="0.00020452" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="3.88741" EstimatedRowsRead="43.2" LogicalOp="Index Seek" NodeId="9" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.00328628" TableCardinality="504">
- <OutputList>
- <ColumnReference Database="[AdventureWorks2017]" Schema="[Production]" Table="[Product]" Alias="[P]" Column="ProductID" />
- <ColumnReference Database="[AdventureWorks2017]" Schema="[Production]" Table="[Product]" Alias="[P]" Column="Name" />
- </OutputList>
- <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Database="[AdventureWorks2017]" Schema="[Production]" Table="[Product]" Alias="[P]" Column="ProductID" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[AdventureWorks2017]" Schema="[Production]" Table="[Product]" Alias="[P]" Column="Name" />
- </DefinedValue>
- </DefinedValues>
- <Object Database="[AdventureWorks2017]" Schema="[Production]" Table="[Product]" Index="[AK_Product_Name]" Alias="[P]" TableReferenceId="2" IndexKind="NonClustered" Storage="RowStore" />
- <SeekPredicates>
- <SeekPredicateNew>
- <SeekKeys>
- <StartRange ScanType="GE">
- <RangeColumns>
- <ColumnReference Database="[AdventureWorks2017]" Schema="[Production]" Table="[Product]" Alias="[P]" Column="Name" />
- </RangeColumns>
- <RangeExpressions>
- <ScalarOperator ScalarString="N">
- <Const ConstValue="N" />
- </ScalarOperator>
- </RangeExpressions>
- </StartRange>
- <EndRange ScanType="LT">
- <RangeColumns>
- <ColumnReference Database="[AdventureWorks2017]" Schema="[Production]" Table="[Product]" Alias="[P]" Column="Name" />
- </RangeColumns>
- <RangeExpressions>
- <ScalarOperator ScalarString="N">
- <Const ConstValue="N" />
- </ScalarOperator>
- </RangeExpressions>
- </EndRange>
- </SeekKeys>
- </SeekPredicateNew>
- </SeekPredicates>
- <Predicate>
- <ScalarOperator ScalarString="[AdventureWorks2017].[Production].[Product].[Name] as [P].[Name] like N">
- <Intrinsic FunctionName="like">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[AdventureWorks2017]" Schema="[Production]" Table="[Product]" Alias="[P]" Column="Name" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Const ConstValue="N" />
- </ScalarOperator>
- </Intrinsic>
- </ScalarOperator>
- </Predicate>
- </IndexScan>
- </RelOp>
- <RelOp AvgRowSize="11" EstimateCPU="0.000439964" EstimateIO="0.003125" EstimateRebinds="3.86942" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="257.24" EstimatedRowsRead="257.24" LogicalOp="Index Seek" NodeId="10" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.016561" TableCardinality="113443">
- <OutputList>
- <ColumnReference Database="[AdventureWorks2017]" Schema="[Production]" Table="[TransactionHistory]" Alias="[TH]" Column="TransactionID" />
- </OutputList>
- <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Database="[AdventureWorks2017]" Schema="[Production]" Table="[TransactionHistory]" Alias="[TH]" Column="TransactionID" />
- </DefinedValue>
- </DefinedValues>
- <Object Database="[AdventureWorks2017]" Schema="[Production]" Table="[TransactionHistory]" Index="[IX_TransactionHistory_ProductID]" Alias="[TH]" TableReferenceId="2" IndexKind="NonClustered" Storage="RowStore" />
- <SeekPredicates>
- <SeekPredicateNew>
- <SeekKeys>
- <Prefix ScanType="EQ">
- <RangeColumns>
- <ColumnReference Database="[AdventureWorks2017]" Schema="[Production]" Table="[TransactionHistory]" Alias="[TH]" Column="ProductID" />
- </RangeColumns>
- <RangeExpressions>
- <ScalarOperator ScalarString="[AdventureWorks2017].[Production].[Product].[ProductID] as [P].[ProductID]">
- <Identifier>
- <ColumnReference Database="[AdventureWorks2017]" Schema="[Production]" Table="[Product]" Alias="[P]" Column="ProductID" />
- </Identifier>
- </ScalarOperator>
- </RangeExpressions>
- </Prefix>
- </SeekKeys>
- </SeekPredicateNew>
- </SeekPredicates>
- </IndexScan>
- </RelOp>
- </NestedLoops>
- </RelOp>
- </Top>
- </RelOp>
- </Concat>
- </RelOp>
- </QueryPlan>
- </StmtSimple>
- </Statements>
- </Batch>
- </BatchSequence>
- </ShowPlanXML>'
- );
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement