Databases
StackOverflow2013
Postsdbo
POQuery plan estimated row count insanely low despite valid stats
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POQuery plan estimated row count insanely low despite valid stats
    text
    copied!<p>I have a simple query (below) in my data warehouse for which the proper indexes are created. The query plan always says the estimated row count is around 1 or 2 when it should be around 6,000,000. I ran sp_updatestats 'resample' and I checked the statistics histogram and it shows 6,000,000 for EQ_ROWS where the DWDateEnd = 12/31/9999, so I'm stumped. 99% of the rows of all tables have a DWDateEnd of 12/31/9999. If I remove the DWDateEnd filters the estimated row counts become fairly accurate. It seems to be slowing my queries by causing nested loops where it should be doing hash joins. Help!</p> <pre><code>SELECT r.OrderID FROM DWOrders r JOIN DWOrderStatus rs ON r.OrderID = rs.OrderID AND rs.StatusID = 5 AND rs.ExpiredDate IS NULL WHERE rs.StatusTimeStamp BETWEEN @StartDate AND @EndDate AND r.DWDateEnd = '12/31/9999' AND rs.DWDateEnd = '12/31/9999' </code></pre> <p>Here's the execution plan.</p> <pre><code>&lt;?xml version="1.0" encoding="utf-16"?&gt; &lt;ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.1" Build="10.50.4000.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan"&gt; &lt;BatchSequence&gt; &lt;Batch&gt; &lt;Statements&gt; &lt;StmtSimple StatementCompId="3" StatementEstRows="2.48996" StatementId="3" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" StatementSubTreeCost="0.00817222" StatementText="&amp;#xA;SELECT r.OrderID&amp;#xD;&amp;#xA; FROM DWOrder r&amp;#xD;&amp;#xA; JOIN DWOrderStatus rs ON r.OrderID = rs.OrderID AND rs.StatusID = 5 AND rs.ExpiredDate IS NULL AND rs.DWDateEnd = '12/31/9999' &amp;#xD;&amp;#xA; WHERE rs.StatusTimeStamp BETWEEN @StartDate AND @EndDate AND r.DWDateEnd = '12/31/9999' &amp;#xD;&amp;#xA;&amp;#xD;&amp;#xA;" StatementType="SELECT" QueryHash="0x24DFCAFA0E610FB3" QueryPlanHash="0x6DD5D50E203E6273"&gt; &lt;StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" /&gt; &lt;QueryPlan CachedPlanSize="24" CompileTime="6" CompileCPU="6" CompileMemory="472"&gt; &lt;RelOp AvgRowSize="11" EstimateCPU="1.0408E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="2.48996" LogicalOp="Inner Join" NodeId="0" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.00817222"&gt; &lt;OutputList&gt; &lt;ColumnReference Database="[EcommArchive]" Schema="[dbo]" Table="[DWOrder]" Alias="[r]" Column="OrderID" /&gt; &lt;/OutputList&gt; &lt;NestedLoops Optimized="false"&gt; &lt;OuterReferences&gt; &lt;ColumnReference Database="[EcommArchive]" Schema="[dbo]" Table="[DWOrderStatus]" Alias="[rs]" Column="OrderID" /&gt; &lt;/OuterReferences&gt; &lt;RelOp AvgRowSize="11" EstimateCPU="0.000159131" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1.93725" LogicalOp="Index Seek" NodeId="1" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.00328413" TableCardinality="24961700"&gt; &lt;OutputList&gt; &lt;ColumnReference Database="[EcommArchive]" Schema="[dbo]" Table="[DWOrderStatus]" Alias="[rs]" Column="OrderID" /&gt; &lt;/OutputList&gt; &lt;IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false"&gt; &lt;DefinedValues&gt; &lt;DefinedValue&gt; &lt;ColumnReference Database="[EcommArchive]" Schema="[dbo]" Table="[DWOrderStatus]" Alias="[rs]" Column="OrderID" /&gt; &lt;/DefinedValue&gt; &lt;/DefinedValues&gt; &lt;Object Database="[EcommArchive]" Schema="[dbo]" Table="[DWOrderStatus]" Index="[IX_DWOrderStatus_DWDateEnd_ExpiredDate_SID_STS_RID]" Alias="[rs]" IndexKind="NonClustered" /&gt; &lt;SeekPredicates&gt; &lt;SeekPredicateNew&gt; &lt;SeekKeys&gt; &lt;Prefix ScanType="EQ"&gt; &lt;RangeColumns&gt; &lt;ColumnReference Database="[EcommArchive]" Schema="[dbo]" Table="[DWOrderStatus]" Alias="[rs]" Column="DWDateEnd" /&gt; &lt;ColumnReference Database="[EcommArchive]" Schema="[dbo]" Table="[DWOrderStatus]" Alias="[rs]" Column="ExpiredDate" /&gt; &lt;ColumnReference Database="[EcommArchive]" Schema="[dbo]" Table="[DWOrderStatus]" Alias="[rs]" Column="StatusID" /&gt; &lt;/RangeColumns&gt; &lt;RangeExpressions&gt; &lt;ScalarOperator ScalarString="'9999-12-31 00:00:00.000'"&gt; &lt;Const ConstValue="'9999-12-31 00:00:00.000'" /&gt; &lt;/ScalarOperator&gt; &lt;ScalarOperator ScalarString="NULL"&gt; &lt;Const ConstValue="NULL" /&gt; &lt;/ScalarOperator&gt; &lt;ScalarOperator ScalarString="(5)"&gt; &lt;Const ConstValue="(5)" /&gt; &lt;/ScalarOperator&gt; &lt;/RangeExpressions&gt; &lt;/Prefix&gt; &lt;StartRange ScanType="GE"&gt; &lt;RangeColumns&gt; &lt;ColumnReference Database="[EcommArchive]" Schema="[dbo]" Table="[DWOrderStatus]" Alias="[rs]" Column="StatusTimeStamp" /&gt; &lt;/RangeColumns&gt; &lt;RangeExpressions&gt; &lt;ScalarOperator ScalarString="[@StartDate]"&gt; &lt;Identifier&gt; &lt;ColumnReference Column="@StartDate" /&gt; &lt;/Identifier&gt; &lt;/ScalarOperator&gt; &lt;/RangeExpressions&gt; &lt;/StartRange&gt; &lt;EndRange ScanType="LE"&gt; &lt;RangeColumns&gt; &lt;ColumnReference Database="[EcommArchive]" Schema="[dbo]" Table="[DWOrderStatus]" Alias="[rs]" Column="StatusTimeStamp" /&gt; &lt;/RangeColumns&gt; &lt;RangeExpressions&gt; &lt;ScalarOperator ScalarString="[@EndDate]"&gt; &lt;Identifier&gt; &lt;ColumnReference Column="@EndDate" /&gt; &lt;/Identifier&gt; &lt;/ScalarOperator&gt; &lt;/RangeExpressions&gt; &lt;/EndRange&gt; &lt;/SeekKeys&gt; &lt;/SeekPredicateNew&gt; &lt;/SeekPredicates&gt; &lt;/IndexScan&gt; &lt;/RelOp&gt; &lt;RelOp AvgRowSize="11" EstimateCPU="0.000158414" EstimateIO="0.003125" EstimateRebinds="0.937251" EstimateRewinds="0" EstimateRows="1.28531" LogicalOp="Index Seek" NodeId="2" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.00487768" TableCardinality="8048090"&gt; &lt;OutputList&gt; &lt;ColumnReference Database="[EcommArchive]" Schema="[dbo]" Table="[DWOrder]" Alias="[r]" Column="OrderID" /&gt; &lt;/OutputList&gt; &lt;IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false"&gt; &lt;DefinedValues&gt; &lt;DefinedValue&gt; &lt;ColumnReference Database="[EcommArchive]" Schema="[dbo]" Table="[DWOrder]" Alias="[r]" Column="OrderID" /&gt; &lt;/DefinedValue&gt; &lt;/DefinedValues&gt; &lt;Object Database="[EcommArchive]" Schema="[dbo]" Table="[DWOrder]" Index="[IX_DWOrder_DWDateEnd]" Alias="[r]" IndexKind="NonClustered" /&gt; &lt;SeekPredicates&gt; &lt;SeekPredicateNew&gt; &lt;SeekKeys&gt; &lt;Prefix ScanType="EQ"&gt; &lt;RangeColumns&gt; &lt;ColumnReference Database="[EcommArchive]" Schema="[dbo]" Table="[DWOrder]" Alias="[r]" Column="DWDateEnd" /&gt; &lt;ColumnReference Database="[EcommArchive]" Schema="[dbo]" Table="[DWOrder]" Alias="[r]" Column="OrderID" /&gt; &lt;/RangeColumns&gt; &lt;RangeExpressions&gt; &lt;ScalarOperator ScalarString="'9999-12-31 00:00:00.000'"&gt; &lt;Const ConstValue="'9999-12-31 00:00:00.000'" /&gt; &lt;/ScalarOperator&gt; &lt;ScalarOperator ScalarString="[EcommArchive].[dbo].[DWOrderStatus].[OrderID] as [rs].[OrderID]"&gt; &lt;Identifier&gt; &lt;ColumnReference Database="[EcommArchive]" Schema="[dbo]" Table="[DWOrderStatus]" Alias="[rs]" Column="OrderID" /&gt; &lt;/Identifier&gt; &lt;/ScalarOperator&gt; &lt;/RangeExpressions&gt; &lt;/Prefix&gt; &lt;/SeekKeys&gt; &lt;/SeekPredicateNew&gt; &lt;/SeekPredicates&gt; &lt;/IndexScan&gt; &lt;/RelOp&gt; &lt;/NestedLoops&gt; &lt;/RelOp&gt; &lt;/QueryPlan&gt; &lt;/StmtSimple&gt; &lt;/Statements&gt; &lt;/Batch&gt; &lt;/BatchSequence&gt; &lt;/ShowPlanXML&gt; </code></pre> <p>One interesting thing is that if I change = '12/31/9999' to >= '12/31/9999' then it uses a hash join but wants me to change most of my indexes.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload