title: How does SQL server work out the estimated number of rows?
body: <p>I'm trying to debug a fairly complex stored procedure that joins across many tabls (10-11). I'm seeing that for a part of the tree the estimated number of rows drasticly differs from the actual number of rows - at its worst SQL server estimates that 1 row will be returned, when in actuality 55,000 rows are returned!</p>
  
  <p>I'm trying to work out why this is - all of my statistics are up-to-date, and I've updated statistics with a FULLSCAN on several tables. I'm not using any user defined functions or table variables. As far as I can see SQL server should be able to exactly estimate how many rows are going to be returned, but it continues to choose a plan which cases it to perform tens of thousands of RDI lookups (when it is expecting to only perform 1 or 2).</p>
  
  <p>What can I do to try and understand why the estimated number of rows is out by so much?</p>
  
  <p><strong>UPDATE:</strong> So looking at the plan I've found one node in particular which seems suspicous - its a table scan on a table using the following predecate:</p>
  
  <pre><code>status &lt;&gt; 5
  AND [type] = 1
  OR [type] = 2
  </code></pre>
  
  <p>This predicate returns the entire table (630 rows - the table scan itself it NOT the source of the poor performance) however SQL server has the estimated number of rows at just 37. SQL server then goes on to do several nested loops with this onto RDI lookups, index scans and index seeks. Could this be the source of my massive miscalculation? How do I get it to estimate a more sensible number of rows?</p>