DaveWentzel.com            All Things Data

Actual vs Estimated Rows and Executions


When reading SHOWPLAN look at the most indented row that has the highest incremental change in the TotalSubtreeCost.  When there are substantial differences it could mean outdated stats or skewed stats.  Try UPDATE STATISTICS WITH FULLSCAN. 
The most significant input the optimizer uses in evaluating the cost of various alternative query plans is the cardinality estimates for each operator, which you can see in the Showplan (EstimateRows and EstimateExecutions attributes). Without accurate cardinality estimates, the primary input used in optimization is flawed, and many times so is the final plan.
Row Count Connecting Bars in a Graphical Plan
--The connecting bars between two operators show how many rows the optimizer believe are being passed between steps

--The wider the bar, the more rows moving

--Performance Ramifications

     --Thin bars are better.  Move conditions from WHERE to ON to achieve this

     --If tables are small but intermediate processing shows a large number of rows then the optimizer is not choosing the optimal processing order

     --Use hints

     --Update statistics

     --the lower input to a JOIN should have fewer rows than the upper input line.  If not then providing a JOIN hint may help you.  See How to Read a Graphical Plan

Add new comment