Query plans

Question

Query plans

sqlguru

SSC-Addicted

Points: 471
More actions
September 20, 2022 at 9:34 pm

#4090049

There is more than 1 plan for a particular query. I suspect it could be auto stats is sampling too low so enabling the persistent sampling. Any thoughts?
October 1, 2022 at 7:39 am

This was removed by the editor as SPAM

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

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

Grant Fritchey SSC Guru Points: 398904 More actions · Answer 1

More than one plan usually indicates different statistics distributions for the parameters involved in the query. Say, filtering on ID = 42 returns 10,000 rows and filtering on ID = 43 returns 3. You'll get two plans for the same query (after a recompile of course). Standard stuff. Is that directly related to the sampling rate? Maybe... but maybe not. If there are really 10,000 rows for one value and 3 for another, sampling is accurate. Get the estimated rows and compare it to the actuals, that will at least tell you how accurate the estimates are. Then, it may be sampling, sure. It may also just be a need for more frequent statistics updates, regardless of sampling.

"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: 1004401 More actions · Answer 2

Hasali wrote:

A commitment of appreciation is all together for the method, it turned out just immeasurable for me moreover!

Yeah... I'm thinking pre-amble to SPAM 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)

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

sqlguru wrote:

There is more than 1 plan for a particular query. I suspect it could be auto stats is sampling too low so enabling the persistent sampling. Any thoughts?

It could also be that the query isn't parameterized properly.

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