Estimated way too less compared to Actual no of rows


Even after making the stats up to date by running frag and stats job, the attached query takes more than a min to fetch 4K records

What could be the reason here for mismatch of estimnate vs actual? Or is there a way i can improve this query to complete much faster as is expcted to run in 10-15 seconds.

Pleae suggest

avatar image By SQLDB 60 asked Apr 25, 2016 at 02:58 PM
more ▼
(comments are locked)
10|10000 characters needed characters left

1 answer: sort voted first

We can't see exact details because you uploaded an anonymized plan.

The mismatches between estimates and actuals are expected, given the size and complexity of the plan. Any cardinality estimation errors tend to grow as the plan becomes more complex, because the estimate from one operation is the input to the next.

You want to rethink the use of table-valued functions as well. These get a fixed guess at cardinality, and do not support statistics.

As far as performance is concerned, one issue is again the size of the plan. Humans and computers alike struggle to work with things that big. If you can break the query into smaller, more easily analysed parts, I would encourage you to do so.

As it is, I would look first at the scans (and seeks) with residual predicates (not seek predicates). These are tests that are applied to a full scan, or on the results of a seek operation. In many cases, your query is reading a much larger number of rows from the table or index than is shown as the operator's output.

For example, a Clustered Index Scan on "Object9.Index14" is reading 47 million rows, but passing on only 14,000. That is the effect of the predicate identified as ScalarString87. The solution depends on exactly what that predicate is of course. Perhaps you are missing an index, or are using a predicate test that cannot be used to seek.

There are many other issues in the plan, but that is the first thing that jumped out at me.

You should upgrade your version of Plan Explorer by the way.

avatar image By SQLkiwi ♦ 6.6k answered Apr 26, 2016 at 06:42 PM
more ▼
(comments are locked)
10|10000 characters needed characters left
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question



asked: Apr 25, 2016 at 02:58 PM

Seen: 145 times

Last Updated: Apr 26, 2016 at 06:42 PM