SQL Query Performance problem

  • tony28

    SSCertifiable

    Points: 7061

    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 3 days, 16 hours ago by  tony28.



    How to post data/code on a forum to get the best help: Option 1[/url] / Option 2[/url]

  • muthukkumaran Kaliyamoorthy

    SSCoach

    Points: 19023

    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: 7061

    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



    How to post data/code on a forum to get the best help: Option 1[/url] / Option 2[/url]

  • muthukkumaran Kaliyamoorthy

    SSCoach

    Points: 19023

    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: 17087

    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: 995457

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "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: 995457

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "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: 7061

    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



    How to post data/code on a forum to get the best help: Option 1[/url] / Option 2[/url]

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

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