Post reply

Query Plan's

  • November 21, 2008 at 3:59 am

    #196328

    Hi all,

    I have a question regarding the values in the "estimated rows" and "actual rows" that are returned by a query plan.

    I have done a stats update on a table with fullscan and still the estimated rows is 1 and the actual is 11228.

    Does anyone know why there is such a huge difference here?

    If you need table/index data please let me know.

    Thanks

    Chris

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life

  • November 21, 2008 at 5:44 am

    #901555

    Hi Chris,

    Could it be that your select will only return one line due to the where clauses and join structure?

    The best thing to do would be to post the T-SQL code and the query plan, a table structure would not hurt either.

    regards

    GermanDBA

    Regards,

    WilliamD

  • November 21, 2008 at 10:19 am

    #901747

    If you are using @table_variable in your query then it is "by design". Query Optimizer hardcodes the estimated row count for tables variables to be always 1.

  • November 21, 2008 at 10:22 am

    #901754

    thanks for that I didn't know that.

    I'm actually using a real table but my join is using a variable.

    So there is Predicate his using a scalar operator.

    Will this result in the same thing that you mentioned about table_variables?

    Thanks

    Chris

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life

  • November 21, 2008 at 11:13 am

    #901806

    If your query has JOIN(s) involving @table_variable then Query Optimizer would likely generate sub-optimal execution plans. Beside the hardcoded row_count = 1 when using table variables it ignores possible parallel plans for multi-CPU database servers. I can't comment on all possible scenarious but the main philosophy Query Optimizer follows is that "table variables usually have very small number of rows so it is very safe to assume that row_count =1".

    I would recommend you the following link which tought me a lot:

    http://jaredko.spaces.live.com/blog/cns!9FA7EE516FA58C15!363.entry

  • November 21, 2008 at 11:19 am

    #901807

    Can you post the exec plan please?

    There are places where this does happen and isn't a problem. The estimated rows is the estimated rows affected per execution of the operator and the actual is the total affected by all of the executions

    So if an operator estimates that it will affect 1 row and it executes 20 times, affecting 1 row on each execution, then the estimated rows will be 1 and the actual will be 20.

    The exec plan in 2005 has the count of executes in the xml plan, but it's not displayed anywhere. 2008 does display the count of executes in the graphical plan.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply