SQL Query Performance problem

Question

Post reply

SQL Query Performance problem

tony28

SSCertifiable

Points: 7108
More actions
January 14, 2020 at 12:07 pm

#3715184
Hello,
I have only question about some interesting issues
I tested yesterday some query, where was problem with running. Long time wasnt some problem, but last week is problem with this.
Default MAXDOP is 8, there is 32 cores 64 logicals CPU, 2sockets . This is AG , I executed on Secondary server with Read where was 1-3% CPU because secondary replica is for reportings.
We found that with MAXDOP hint in query directly
- yesterday about 13-17hours was possible run this query only with MAXDOP 1,2,3,4
- yesterday after 18 and more was possible run only with MAXDOP 1,2
with more maxdop looks like "frozen", I checked also Live query view and know where was "frozen", but could not see some problem only CXPACKETS block on this session where i run.
- Today about 9- till now is possible run this query with all MAXDOP 1,2,3,4,5,6,7,8
I was thinking about update statistics, but problem is that is every day, and this query wasnt possible run longer time not only yesterday.
Does somebody experience or ideas what can i check else?
I am waiting now till night if there will be also problem or not.
- This topic was modified 4 years, 3 months ago by tony28.

Viewing 8 posts - 1 through 7 (of 7 total)

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

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 1

It could be a PSP issue, do you capture the execution plan to compare each time of run high vs low time taken.

What is the SQL version are you using it.

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

tony28 SSCertifiable Points: 7108 More actions · Answer 2

Hi,

plans are different

yesterday:

for maxdop 1 is diff

for maxdop 2,3 was same

another wasnt possibel to finish so i dont know

today

for maxdop 1 is diff

for maxdop 2,3,4 same

for maxdop 5,6,7,8 same

not sure if there is really MAXDOP or some another issue

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 3

Is it possible to share the execution plan.

Have you find the code developer why it has added a maxdop hint.

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

Jonathan AC Roberts SSCoach Points: 18253 More actions · Answer 4

tony28 wrote:

<li style="list-style-type: none;">
yesterday about 13-17hours was possible run this query only with MAXDOP 1,2,3,4
<li style="list-style-type: none;">
yesterday after 18 and more was possible run only with MAXDOP 1,2

What you mean by "MAXDOP 1,2,3,4" as it only has one parameter?

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

Jonathan AC Roberts wrote:

What you mean by "MAXDOP 1,2,3,4" as it only has one parameter?

I'm thinking that it means that he tried all 4 settings.

--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: 1003851 More actions · Answer 6

Tony... what is your Cost Threshold of Parallelism set to?

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

tony28 SSCertifiable Points: 7108 More actions · Answer 7

yes i tried all of these settings.

we have 50. I checked that this query costs about 1400-1500 for MAXDOP 1 , of course then is worked.

Only question is what can be issue, if someone has something like this, because now is still working without problem , so not sure what could be there, and i guess that maybe will not find till next same issue . But i couldnot find nothing except this CXpacket and only testing with MAXDOP .

Thanks