Menu
I have an execution plan for a fairly complex join which shows an index seek being performed on a table with the "Actual Number of Rows" reading ~70,000, when there are in fact only ~600 rows in the table in total (the estimated number of rows is only 127).
Note that all of the statistics are up to date and the input parameters to the query are exactly the same as the parameters that were entered when the proc was compiled.
Why is the actual number of rows so high, and what does the number "Actual Number of Rows" really mean?
My only theory is that high number of rows is related to the nested loops, and that this index seek is being executed a number of times - the "Actual Number of Rows" really represents the total number of rows over all executions. If this is the case is the estimated number of rows also meant to be the total number of rows over all executions?
226
Actual execution plans are generated after the Transact-SQL queries or batches execute. Because of this, an actual execution plan contains runtime information, such as actual number of rows, resource usage metrics and runtime warnings (if any). For more information, see Display an Actual Execution Plan.
This type of plan is generated once the query gets executed or we can say after the run time. The actual execution plan shows the steps SQL Server takes to execute the query. It is giving actual information by the query processor. It provides all information like which are the steps involved when we execute that query.
How would you view the execution plan? An execution plan is basically a road map that graphically or textually shows the data retrieval methods chosen by the SQL server's query optimizer for a stored procedure or ad hoc query.
Right-click in a blank area of the execution plan and click Compare Showplan. Choose the second query plan file that you would like to compare with. The second file will open so that you can compare the plans. The compared plans will open a new window, by default with one on top and one on the bottom.
ActualRows counts the number of times GetNext() was called on a physical operator.
You should also look at the ActualRebinds, ActualRewinds and ActualEndOfScans to get an idea how many times the inner loop was re-evaluated:
A rebind means that one or more of the correlated parameters of the join changed and the inner side must be reevaluated. A rewind means that none of the correlated parameters changed and the prior inner result set may be reused.
69
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
