June 14, 2017 at 1:07 pm
Why do I have one estimated row in the nested loop node 2 while correlated seek and scan each has about 13300 estimated rows?
June 14, 2017 at 1:29 pm
is it possible that TradeActive table doesn't have statistics or has wildly out of date statistics on the column IDPosition? That could cause a bad estimate of the join.
June 14, 2017 at 1:49 pm
I even have the index on the IDPosition column which statistics was updated yesterday. I rebuild it but nothing has changed in estimates besides of the index used to scan.
June 15, 2017 at 3:07 am
Even this simpliest query has the same result. There is the foreign key from TradeActive.IDPosition that references Position.ID.
What can be a reason for this estsimates?
June 15, 2017 at 8:23 am
Can you post the statistics histogram for the statistics associated with each index?
Cheers!
June 15, 2017 at 8:29 am
Estimated rows may be 1, but how many estimated executions? The two combine frequently depending on the other operators in the plan.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 15, 2017 at 8:33 am
Grant Fritchey - Thursday, June 15, 2017 8:29 AMEstimated rows may be 1, but how many estimated executions? The two combine frequently depending on the other operators in the plan.
The interesting thing he's looking at is not the estimated rows coming out of the inner seek, which is indeed 1 row, but with the expected number of executions.
It's the number of rows coming out of the nested loop join operator itself, which is indeed just 1, when in fact every row from the outer input matches 1 row from the inner input.
June 15, 2017 at 8:50 am
I've just updated both statistics. The result is the same.
June 15, 2017 at 9:17 am
Ah, looked at the code. the ISNULL function in the JOIN criteria is going to result in no statistics being used on the column. Pre-2014 cardinality estimation will be one row. That's what's going on.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 15, 2017 at 9:30 am
Grant Fritchey - Thursday, June 15, 2017 9:17 AMAh, looked at the code. the ISNULL function in the JOIN criteria is going to result in no statistics being used on the column. Pre-2014 cardinality estimation will be one row. That's what's going on.
Yeah, that was one of my first thoughts as well, but the join that first estimates 1 row has a simple equality predicate for the join with no functions.
Also, as his most recent post points out, he gets the same result when reducing the query to just doing a join between those two tables, again with just a simple equality predicate, with no functions used anywhere.
In the past I've seen this happen because the statistics just had certain ranges wrong, even when they were updated with FULLSCAN, but nothing about these histograms would lead the estimate to be 0 or 1.
Perhaps those aren't the statistics used by the query? Try running the query using OPTION (QUERYTRACEON 9204, QUERYTRACEON 3604) just to confirm the statistics that are loaded.
I'll have to mull this one over a bit more.
June 16, 2017 at 3:00 am
Jacob Wilkins - Thursday, June 15, 2017 9:30 AMGrant Fritchey - Thursday, June 15, 2017 9:17 AMAh, looked at the code. the ISNULL function in the JOIN criteria is going to result in no statistics being used on the column. Pre-2014 cardinality estimation will be one row. That's what's going on.Yeah, that was one of my first thoughts as well, but the join that first estimates 1 row has a simple equality predicate for the join with no functions.
Also, as his most recent post points out, he gets the same result when reducing the query to just doing a join between those two tables, again with just a simple equality predicate, with no functions used anywhere.
In the past I've seen this happen because the statistics just had certain ranges wrong, even when they were updated with FULLSCAN, but nothing about these histograms would lead the estimate to be 0 or 1.
Perhaps those aren't the statistics used by the query? Try running the query using OPTION (QUERYTRACEON 9204, QUERYTRACEON 3604) just to confirm the statistics that are loaded.
I'll have to mull this one over a bit more.
This is real mistery. Here's is the stats
Stats loaded: DbName: main2, ObjName: dbo.TradeActive, IndexId: 3, ColumnName: IDPosition, EmptyTable: FALSE
Stats loaded: DbName: main2, ObjName: dbo.TradeActive, IndexId: 9, ColumnName: IDPosition, EmptyTable: FALSE
Stats loaded: DbName: main2, ObjName: dbo.Position, IndexId: 1, ColumnName: ID, EmptyTable: FALSE
Look at the plans I get in SSMS and Plan Explorer. They are different but even the hash is the same.
And the estimates in SSMS is 6876.76. And it is strange too. I have the foreign key from dbo.TradeActive to dbo.Position. And the dbo.TradeActive scan estimates is 13375 so I have to receive 13375 rows from the join.
Plan1.txt is the pesession file in fact. I rename it because of the forum restrictions.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply