Skip to content

Instantly share code, notes, and snippets.

@jadarnel27

jadarnel27/weird-reads.sql

Last active April 8, 2019 13:59
Show Gist options
  • Save jadarnel27/46ad2e7feedc705f6e3f5027b9ce1396 to your computer and use it in GitHub Desktop.
Save jadarnel27/46ad2e7feedc705f6e3f5027b9ce1396 to your computer and use it in GitHub Desktop.
Download ZIP
Weird reads
Raw
weird-reads.sql
DECLARE @userId INT = 22656;
DECLARE @postType INT = 1;
SELECT Id
FROM dbo.Posts WITH (INDEX([IX_Posts_OwnerUserId]))
WHERE OwnerUserId = @userId AND PostTypeId = 1;
SELECT Id
FROM dbo.Posts WITH (INDEX([IX_Posts_OwnerUserId]))
WHERE OwnerUserId = 22656 AND PostTypeId = 1
OPTION(MAXDOP 1);
Raw
weird-reads.sqlplan
<?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.3048.4" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementCompId="3" StatementEstRows="14.2544" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="70" StatementSubTreeCost="0.0470304" StatementText="SELECT Id &#xD;&#xA;FROM dbo.Posts WITH (INDEX([IX_Posts_OwnerUserId])) &#xD;&#xA;WHERE OwnerUserId = @userId AND PostTypeId = 1" StatementType="SELECT" QueryHash="0xFB8AC018120AAFE1" QueryPlanHash="0x1AF6268FCA0A517E" RetrievedFromCache="true" 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 DegreeOfParallelism="1" CachedPlanSize="24" CompileTime="1" CompileCPU="1" CompileMemory="232">
<MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" />
<OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="209715" EstimatedPagesCached="52428" EstimatedAvailableDegreeOfParallelism="2" MaxCompileMemory="6005944" />
<QueryTimeStats CpuTime="95" ElapsedTime="95" />
<RelOp AvgRowSize="15" EstimateCPU="5.95832E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="14.2544" LogicalOp="Inner Join" NodeId="0" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.0470304">
<OutputList>
<ColumnReference Database="[StackOverflow2010]" Schema="[dbo]" Table="[Posts]" Column="Id" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="22" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="95" ActualCPUms="95" />
</RunTimeInformation>
<NestedLoops Optimized="false">
<OuterReferences>
<ColumnReference Database="[StackOverflow2010]" Schema="[dbo]" Table="[Posts]" Column="Id" />
</OuterReferences>
<RelOp AvgRowSize="11" EstimateCPU="0.00017268" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="14.2544" EstimatedRowsRead="14.2544" LogicalOp="Index Seek" NodeId="1" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.00329768" TableCardinality="3744190">
<OutputList>
<ColumnReference Database="[StackOverflow2010]" Schema="[dbo]" Table="[Posts]" Column="Id" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="11371" ActualRowsRead="11371" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="12" ActualCPUms="12" ActualScans="1" ActualLogicalReads="23" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
</RunTimeInformation>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="true" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[StackOverflow2010]" Schema="[dbo]" Table="[Posts]" Column="Id" />
</DefinedValue>
</DefinedValues>
<Object Database="[StackOverflow2010]" Schema="[dbo]" Table="[Posts]" Index="[IX_Posts_OwnerUserId]" IndexKind="NonClustered" Storage="RowStore" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[StackOverflow2010]" Schema="[dbo]" Table="[Posts]" Column="OwnerUserId" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[@userId]">
<Identifier>
<ColumnReference Column="@userId" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
<RelOp AvgRowSize="11" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="13.2544" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="4.18327" LogicalOp="Clustered Index Seek" NodeId="3" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0436732" TableCardinality="3744190">
<OutputList />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="22" ActualRowsRead="11371" Batches="0" ActualEndOfScans="11349" ActualExecutions="11371" ActualExecutionMode="Row" ActualElapsedms="78" ActualCPUms="78" ActualScans="0" ActualLogicalReads="45484" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
</RunTimeInformation>
<IndexScan Lookup="true" Ordered="true" ScanDirection="FORWARD" ForcedIndex="true" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues />
<Object Database="[StackOverflow2010]" Schema="[dbo]" Table="[Posts]" Index="[PK_Posts__Id]" TableReferenceId="-1" IndexKind="Clustered" Storage="RowStore" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[StackOverflow2010]" Schema="[dbo]" Table="[Posts]" Column="Id" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[StackOverflow2010].[dbo].[Posts].[Id]">
<Identifier>
<ColumnReference Database="[StackOverflow2010]" Schema="[dbo]" Table="[Posts]" Column="Id" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
<Predicate>
<ScalarOperator ScalarString="[StackOverflow2010].[dbo].[Posts].[PostTypeId]=(1)">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[StackOverflow2010]" Schema="[dbo]" Table="[Posts]" Column="PostTypeId" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
<ParameterList>
<ColumnReference Column="@userId" ParameterDataType="int" ParameterRuntimeValue="(22656)" />
</ParameterList>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
<Batch>
<Statements>
<StmtSimple StatementCompId="4" StatementEstRows="11371" StatementId="2" StatementOptmLevel="FULL" CardinalityEstimationModelVersion="70" StatementSubTreeCost="37.155" StatementText="SELECT Id &#xD;&#xA;FROM dbo.Posts WITH (INDEX([IX_Posts_OwnerUserId])) &#xD;&#xA;WHERE OwnerUserId = 22656 AND PostTypeId = 1&#xD;&#xA;OPTION(MAXDOP 1)" StatementType="SELECT" QueryHash="0x0F8C2404165220F5" QueryPlanHash="0xEFBE875906874E91" RetrievedFromCache="true" 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 DegreeOfParallelism="0" NonParallelPlanReason="MaxDOPSetToOne" CachedPlanSize="24" CompileTime="1" CompileCPU="1" CompileMemory="232">
<MissingIndexes>
<MissingIndexGroup Impact="99.9896">
<MissingIndex Database="[StackOverflow2010]" Schema="[dbo]" Table="[Posts]">
<ColumnGroup Usage="EQUALITY">
<Column Name="[OwnerUserId]" ColumnId="14" />
<Column Name="[PostTypeId]" ColumnId="16" />
</ColumnGroup>
</MissingIndex>
</MissingIndexGroup>
</MissingIndexes>
<MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" />
<OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="209715" EstimatedPagesCached="52428" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="6005944" />
<QueryTimeStats CpuTime="51" ElapsedTime="51" />
<RelOp AvgRowSize="15" EstimateCPU="0.0475308" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="11371" LogicalOp="Inner Join" NodeId="0" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="37.155">
<OutputList>
<ColumnReference Database="[StackOverflow2010]" Schema="[dbo]" Table="[Posts]" Column="Id" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="22" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="50" ActualCPUms="50" />
</RunTimeInformation>
<NestedLoops Optimized="false" WithUnorderedPrefetch="true">
<OuterReferences>
<ColumnReference Database="[StackOverflow2010]" Schema="[dbo]" Table="[Posts]" Column="Id" />
<ColumnReference Column="Expr1002" />
</OuterReferences>
<RelOp AvgRowSize="11" EstimateCPU="0.0126651" EstimateIO="0.0169617" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="11371" EstimatedRowsRead="11371" LogicalOp="Index Seek" NodeId="2" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0296268" TableCardinality="3744190">
<OutputList>
<ColumnReference Database="[StackOverflow2010]" Schema="[dbo]" Table="[Posts]" Column="Id" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="11371" ActualRowsRead="11371" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="6" ActualCPUms="6" ActualScans="1" ActualLogicalReads="23" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
</RunTimeInformation>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="true" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[StackOverflow2010]" Schema="[dbo]" Table="[Posts]" Column="Id" />
</DefinedValue>
</DefinedValues>
<Object Database="[StackOverflow2010]" Schema="[dbo]" Table="[Posts]" Index="[IX_Posts_OwnerUserId]" IndexKind="NonClustered" Storage="RowStore" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[StackOverflow2010]" Schema="[dbo]" Table="[Posts]" Column="OwnerUserId" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="(22656)">
<Const ConstValue="(22656)" />
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
<RelOp AvgRowSize="11" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="11370" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="3337.08" LogicalOp="Clustered Index Seek" NodeId="4" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="37.0778" TableCardinality="3744190">
<OutputList />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="22" ActualRowsRead="11371" Batches="0" ActualEndOfScans="11349" ActualExecutions="11371" ActualExecutionMode="Row" ActualElapsedms="37" ActualCPUms="37" ActualScans="0" ActualLogicalReads="43327" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
</RunTimeInformation>
<IndexScan Lookup="true" Ordered="true" ScanDirection="FORWARD" ForcedIndex="true" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues />
<Object Database="[StackOverflow2010]" Schema="[dbo]" Table="[Posts]" Index="[PK_Posts__Id]" TableReferenceId="-1" IndexKind="Clustered" Storage="RowStore" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[StackOverflow2010]" Schema="[dbo]" Table="[Posts]" Column="Id" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[StackOverflow2010].[dbo].[Posts].[Id]">
<Identifier>
<ColumnReference Database="[StackOverflow2010]" Schema="[dbo]" Table="[Posts]" Column="Id" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
<Predicate>
<ScalarOperator ScalarString="[StackOverflow2010].[dbo].[Posts].[PostTypeId]=(1)">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[StackOverflow2010]" Schema="[dbo]" Table="[Posts]" Column="PostTypeId" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>
Raw
weird-reads.txt
(22 rows affected)
Table 'Posts'. Scan count 1, logical reads 45507, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row affected)
SQL Server Execution Times:
CPU time = 62 ms, elapsed time = 232 ms.
(22 rows affected)
Table 'Posts'. Scan count 1, logical reads 46226, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row affected)
SQL Server Execution Times:
CPU time = 32 ms, elapsed time = 182 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment