none
Estimated number of rows issue RRS feed

  • Question

  • What could be the reason for "Estimated number of rows" showing/picking up 1 for all tables in the query. We did stats updates recently on all tables and even then the plan going wrong leading to poor query performance.


    Mahesh

    Thursday, October 26, 2017 2:53 PM

All replies

  • Can you post plan details...?

    Make sure you are not looking at "Estimated number of executions" ( don't take it wrong , I asked this as you mentioned you are seeing 1 as Estimated no.of Rows for all tables


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.


    • Edited by Sarat (SS) Thursday, October 26, 2017 3:10 PM
    Thursday, October 26, 2017 3:08 PM
  • It's really hard to say without at least seeing the query, and to Sarat's point, the execution plan wouldn't hurt either.

    SQL Server will sometimes give an estimated row count of 1 when it can't make a good guess as to how many rows would be returned. The following link will take you to a plan where that's happening:

    https://www.brentozar.com/pastetheplan/?id=S18GOLFig

    One area where you see that is with table variables. SQL Server can't figure out how many records in in the table variable because it has no statistics as result it estimates 1 record. Check out the following for some other scenarios.

    Ten Common Threats to Execution Plan Quality

    A-


    Adam

    Thursday, October 26, 2017 8:49 PM
  • It is a complex query can't place it on one screenshot. The query uses 20+ tables with subquery, union, joins, etc.

    Yes it is Rows Not executions.

    I would like to know why the query estimating 1 rows on most tables except for few small code tables. This is not just for one query and many reporting query which leading to slow performance of entire system.


    Mahesh

    Thursday, October 26, 2017 9:36 PM
  • Hi Mahesh,

    Per your description, if you haven't got any errors from your server when executing the complex query, I guess it is more likely related to resource configuration. For this purpose, you could test your query by using Activity Monitor tool.(turn to the second tab to get useful resource information)

    If you still have questions, please remember to share your result of the testing to us.

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, October 27, 2017 8:15 AM
    Moderator