Estimated rows are more than actual rows in execution plan

• kbhanu15

  SSCarpal Tunnel

  Points: 4493

  More actions

  September 3, 2014 at 7:14 am

  #397290

  hi All,

  can you please provide me some suggestion how to avoid this conflict.

  estimated rows are greater than actual rows.

  and also please provide some tuning tips for this procedure.

  please find the exec plans and procedure scripts.

  Thanks

  Bhanu

• kbhanu15

  SSCarpal Tunnel

  Points: 4493

  More actions

  September 3, 2014 at 7:37 am

  #1742788

  need one more help.

  table insert cost is 30%. how to reduce this cost.

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  September 3, 2014 at 8:26 am

  #1742823

  kbhanu15 (9/3/2014)

  ---

  hi All,

  can you please provide me some suggestion how to avoid this conflict.

  estimated rows are greater than actual rows.

  and also please provide some tuning tips for this procedure.

  please find the exec plans and procedure scripts.

  Thanks

  Bhanu

  A well-known issue with using views is that the complexity of the query is hidden in the view definition: what looks like a simple query is far more complex underneath and SQL Server may struggle to find a suitable plan in the time allowed. That's what has happened here: Reason For Early Termination Of Statement Optimization = Time Out (found on the properties sheet for SELECT INTO). Simplify your query.

  Estimated rows and Actual rows are almost always different. Estimated rows are calculated by SQL Server using statistics and table cardinalities; Actual rows are recorded when the query is run.

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• SQLRNNR

  SSC Guru

  Points: 281334

  More actions

  September 3, 2014 at 9:11 am

  #1742854

  ChrisM@Work (9/3/2014)

  ---

  kbhanu15 (9/3/2014)

  ---

  hi All,

  can you please provide me some suggestion how to avoid this conflict.

  estimated rows are greater than actual rows.

  and also please provide some tuning tips for this procedure.

  please find the exec plans and procedure scripts.

  Thanks

  Bhanu

  ... Estimated rows are calculated by SQL Server using statistics and table cardinalities; Actual rows are recorded when the query is run.

  To further what Chris said, this is normal behavior. This is the QO "sniffing" for a value based on your parameter value. This comes from statistics and cardinality.

  Since you likely provide a different companyid each time this query is run, you may want to use one of the recompile methods. Test it out and see if it helps with your issue.

  Jason...AKA CirqueDeSQLeil
  _______________________________________________
  I have given a name to my pain...MCM SQL Server, MVP
  SQL RNNR
  Posting Performance Based Questions - Gail Shaw[/url]
  Learn Extended Events