Big difference between Estimated and Actual Actual rows in execution Plan.

Question

Big difference between Estimated and Actual Actual rows in execution Plan.

sizal0234

SSCrazy

Points: 2272
More actions
April 5, 2021 at 7:36 pm

#3865706

Hi,
This is an Index Seek which is 60% cost of the query. Now, I see there is a big difference between actual and estimated rows. ( 0 and 15600) , the statistics on the table and index are updated yesterday. What else I should look at to optimize the seek? Any thoughts, it is doing SEEK in object8, thanks!

SELECT
Variable1 = Function1(Function2(Object1.Column1), ?)
FROM Schema1.Object2 Object3 WITH (NOLOCK)
INNER JOIN Schema1.Object4 Object5 WITH (NOLOCK) on Object3.Column2 = Column3
INNER JOIN Schema1.Object6 Object7 WITH (NOLOCK) ON Column4 = Object7.Column5
INNER JOIN Schema1.Object8 Object9 WITH (NOLOCK) ON Object7.Column6 = Object9.Column7
INNER JOIN Schema1.Object4 Object1 WITH (NOLOCK) ON Object1.Column4 = Object9.Column8
WHERE Object3.Column9 = Variable2
AND Object1.Column10 = Variable3
AND Object1.Column11 = ?
AND Object1.Column12 = Variable4

Viewing 4 posts - 1 through 3 (of 3 total)

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

Jo Pattyn SSC-Dedicated Points: 32070 More actions · Answer 1

Your query is too abstracted to give much information.

If it index seeks on object8 (alias) object name, there is an index on Object 8 Column7, Column 8 (or the other way around)

Is the index seek problematic ( zero rows?)

You have a function

There are a lot of nolocks

Are there any foreign keys / constraints defined that could help?

Is ? of the correct datatype (implicit vs explicit conversion)

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 2

It's because <insert drumroll here>, it's an "Estimate". 😀

It's like asking someone how many wheels there are on their truck. Because of "statistics" on the subject and you've identified it as a "pickup truck", you might estimate that it has 4 wheels when it's actually a "dually" that has 6 wheels.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jonathan AC Roberts SSCoach Points: 18263 More actions · Answer 3

Make sure all the columns on Object8 that your query references are either in the index key or the included columns, this will stop it doing a key lookup for each row. You could also rebuild the relevant index on that table to reduce any fragmentation.

Can you post the execution plan and the messages from running with SET STATISTICS IO, TIME ON?

I've got to add your table aliases seem designed to cause confusion.