title: Hash Match : Incorrect estimation number of rows
body: <p>I have a large table (FactTable) which inner joins to a small table (DimensionTable), so as expected the execution plan is using hash match but for some reason the estimate number of rows on hash match(Inner Join) is 1 record.</p>
  
  <p>I created a copy of DimensionTable table (With all indexes and copy of data ) lets call that table DimensionTable_BKP and used that in the query instead of the actual dimensiontable and the hash match in execution plan for new query is showing correct row estimate</p>
  
  <p>Execution plan for DimensionTable 
  <a href="https://i.stack.imgur.com/AJQ2l.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/AJQ2l.png" alt="enter image description here"></a></p>
  
  <p>Execution plan for DimensionTable_BKP
  <a href="https://i.stack.imgur.com/quY6p.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/quY6p.png" alt="enter image description here"></a></p>
  
  <p>So i tried to run DBCC on DimensionTable and then ran DBCC on index used by the query and they both turned up with no errors</p>
  
  <p>I updated the statistics on that table and that didn't help either</p>
  
  <p>Any other thoughts/suggestions on next steps?</p>