High sensitivity of SQL server 2016 to statistics ?

Question

High sensitivity of SQL server 2016 to statistics ?

Viewing 10 posts - 16 through 24 (of 24 total)

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

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

To be clear, the link you posted from stack overflow cites TF 4199 and I'm not talking about that. I'm talking about TF 9481, which fixed my issues when, like you, I migrated from 2012 to 2016.

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

martinreiser SSC Enthusiast Points: 197 More actions · Answer 2

In my opinion it is the same as "Legacy Cardinality Estimator" ON at database level.

If yes, I mention this on the very first page.

Martin

frederico_fonseca SSCoach Points: 16010 More actions · Answer 3

martinreiser wrote:

In my opinion it is the same as "Legacy Cardinality Estimator" ON at database level.
If yes, I mention this on the very first page.
Martin

yes it is the same thing - but better in a way as it applies only to that db

martinreiser SSC Enthusiast Points: 197 More actions · Answer 4

But is it is written on the very first page I do have it switched on - since the migration, I now about this switch. And I have still some problems, but fewer than without it.

On Database level.

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 5

Doing things at the database level isn't necessarily doing the same as doing something at the server level. I strongly recommend that you try TF 9481 at the server level and see if that fixes things for you. It did for me. YMMV.

The other choice is to go fix all the queries that still have the problem.

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

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 6

Sometimes the ultimate fix is a rewrite of the queries to a more optimal pattern.

"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

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 7

Grant Fritchey wrote:

Sometimes the ultimate fix is a rewrite of the queries to a more optimal pattern.

Totally agreed... sometimes, it's the only fix.

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

martinreiser SSC Enthusiast Points: 197 More actions · Answer 8

Today I have a peformance issue where setting legacy cardinality estimator ON helps.

From 100s to 1 sec. Really interesting.

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 9

martinreiser wrote:

Today I have a peformance issue where setting legacy cardinality estimator ON helps.
From 100s to 1 sec. Really interesting.

If you have individual queries, and you can edit them, it is possible to add a hint to use the legacy estimator. I shy away from recommending this stuff because usually, the query needs editing anyway, so if we can modify the code, why not try to generally improve it rather than prop it up with a hint. However, it's a good thing to know.

"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

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 10

martinreiser wrote:

Today I have a peformance issue where setting legacy cardinality estimator ON helps.
From 100s to 1 sec. Really interesting.

Heh... that's not just "help"... that's a freakin' miracle and we found enough of those where we just said "screw it" and set the whole server back to using the legacy Cardinal Estimator.

To be honest, I've not actually seen anywhere where the new Cardinality Estimator has actually made anything perform better. As I've been known to say, "Change is inevitable... change for the better is not".

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