Same Execution Plan different stats

Question

Same Execution Plan different stats

Viewing 15 posts - 31 through 45 (of 48 total)

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

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

anvegger - Thursday, October 18, 2018 6:30 PM
Jeff Moden - Thursday, October 18, 2018 5:53 PM
Doing a DELETE isn't the way to fix this problem. Fixing the code is. And it's not an accidental CROSS APPLY... it's an accidental CROSS JOIN due to a lack of proper criteria.
Sorry - misstyping : CROSS JOIN I meant ... The query is very simple. There is nothing to be even look at: a half a dozen of tables simply INNER JOIN to produce a zero result set over WHERE clause that filters an un-existing location. I am lost and my brain stopped working

So,.. if it's such a simple query, why did some nimrod decide to add an index hint to the query? 😉 Try removing that hint (which is actually a directive) and see what happens.

--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)

Jack Corbett SSC Guru Points: 184393 More actions · Answer 2

I know you have said you updates stats, but did you do the update stats on both databases AFTER you restored them to this instance?

What is the source version of SQL Server that these databases were restored from?

Are they both in 2016 compatibility?

Are the database settings for cardinality estimation the same?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

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

Jack Corbett - Thursday, October 18, 2018 7:30 PM
I know you have said you updates stats, but did you do the update stats on both databases AFTER you restored them to this instance?
What is the source version of SQL Server that these databases were restored from?
Are they both in 2016 compatibility?
Are the database settings for cardinality estimation the same?

I could certainly be wrong but I believe that the Index usage "hint" in the query is what's biting him.

--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)

DesNorton SSC-Insane Points: 24278 More actions · Answer 4

anvegger - Thursday, October 18, 2018 2:58 PM
Schedule 175982 45016 KB 22904 KB 21928 KB 184 KB
Schedule 179796 46136 KB 23384 KB 22344 KB 408 KB
Many thanks for the wonderful picture DesNorton, but again - my actual number of records are increased in a small fraction of total numbers not in many times:
All tables are in a normal grow ratio - 10 % or less, it should not be 50 times slow... in stat that is my concern Or I am thinking in a wrong way totally.
Thanks for your help

As Jeff stated, the issue could be with the index hint.
It could also be with your stats. Were they updated with the default sampling, or with a FULL SCAN.

How to post data/code on a forum to get the best help.

anvegger SSC Veteran Points: 251 More actions · Answer 5

DesNorton - Thursday, October 18, 2018 10:33 PM
As Jeff stated, the issue could be with the index hint.It could also be with your stats. Were they updated with the default sampling, or with a FULL SCAN.

Many thanks DesNorton for your reference. That hint was introduced just to prove that both queries using 100% identical execution plan - object by object. That index was missing on Database D and I just added and forced using it. , Of cause all the tricks with INDEX and STATISTICS have been promoted several times, INDEX REBUILD, UPDATE STATISTICS WITH FULL SCAN, NORECOMPUTE - all these tricks were tested with no improvement. Something deeper than that is causing the slowness.

Many thanks foe looking into that

anvegger SSC Veteran Points: 251 More actions · Answer 6

Jeff Moden - Thursday, October 18, 2018 6:54 PM
Try removing that hint (which is actually a directive) and see what happens.

Nothing is happening. Unfortunately.

anvegger SSC Veteran Points: 251 More actions · Answer 7

I know you have said you updates stats, but did you do the update stats on both databases AFTER you restored them to this instance?
AV: Yes - all UPDATE STATISTICS have been executed AFTER the RESTORE on the new instance

What is the source version of SQL Server that these databases were restored from?
AV: That is unknown - the environment have been dormant for at least 6 months

Are they both in 2016 compatibility?
AV: Yes (130 ) on both

Are the database settings for cardinality estimation the same?
AV: Yes - both databases have value of '0' :
LEGACY_CARDINALITY_ESTIMATION 0

anvegger SSC Veteran Points: 251 More actions · Answer 8

BTW that INDEX hint along with an INDEX itself did not change execution plan or stats - simply used a different index to produce the same amount of data consuming the same amount of resources
LEFT JOIN SchedulePeriod sp WITH (INDEX (IX_N_SchedulePUResUsch)) ON s.ScheduleId = sp.ScheduleId AND s.LicenseeId = sp.LicenseeId AND s.LocationId = sp.LocationId AND sp.ScheduleRepeatRuleId != 4

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

anvegger - Friday, October 19, 2018 7:16 AM
BTW that INDEX hint along with an INDEX itself did not change execution plan or stats - simply used a different index to produce the same amount of data consuming the same amount of resources
LEFT JOIN SchedulePeriod sp WITH (INDEX (IX_N_SchedulePUResUsch)) ON s.ScheduleId = sp.ScheduleId AND s.LicenseeId = sp.LicenseeId AND s.LocationId = sp.LocationId AND sp.ScheduleRepeatRuleId != 4

Then you need to investigate the data and the code and see why scalability has such a terrible effect. Again, I believe it's because there's some missing criteria that simply causes and accidental cross join (many-to-many join) that's cause relational multiplication. It may even be that the design of the data doesn't allow for further isolation and it's going to take you some time to science it out and fix it. There's likely no magic bullet to be had here.

--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)

anvegger SSC Veteran Points: 251 More actions · Answer 10

Yes you right Jeff - my next logical step would be to sync data in both cases (databases) and make sure that I eliminate or prove the impact of a data. DELETE will not help obviously but if I get the same execution stats from the same sets of my data - that will be a proof of the data is bad. Will keep digging into it further

Thanks for your help - Kindda makes sense to blame Hidden CROSS JOIN

Jeffrey Williams SSC Guru Points: 90252 More actions · Answer 11

anvegger - Friday, October 19, 2018 7:57 AM
Yes you right Jeff - my next logical step would be to sync data in both cases (databases) and make sure that I eliminate or prove the impact of a data. DELETE will not help obviously but if I get the same execution stats from the same sets of my data - that will be a proof of the data is bad. Will keep digging into it further
Thanks for your help - Kindda makes sense to blame Hidden CROSS JOIN

Before you do that try schema qualifying all tables in the query.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

llefebvre7 SSC Rookie Points: 27 More actions · Answer 12

The cached plan size is different, why?
Do you have Query Store on for both DBs?
Are the system generated stats the same in both DBs?
I find it interesting that the large table, indexes are seeks vs. scans.

anvegger SSC Veteran Points: 251 More actions · Answer 13

Jeffrey Williams 3188 - Friday, October 19, 2018 8:09 AM
Before you do that try schema qualifying all tables in the query.

Good point - I do my best to check it out Thanks Jeff!

anvegger SSC Veteran Points: 251 More actions · Answer 14

llefebvre7 - Friday, October 19, 2018 8:14 AM
The cached plan size is different, why?
Do you have Query Store on for both DBs?
Are the system generated stats the same in both DBs?
I find it interesting that the large table, indexes are seeks vs. scans.

Excellent point: Yes Query Store is ON on both DBs: Statistics Collection Interval is different The rest is identical: 1h (Database A) vs 15 min (Database D)

anvegger SSC Veteran Points: 251 More actions · Answer 15

anvegger - Friday, October 19, 2018 8:18 AM
Good point - I do my best to check it out Thanks Jeff!

Used a full object qualifiers on all tables: no impact