Post reply

SQL2022 UPDATED FROM SQL2014 - PARALLELISM ISSUES

  • August 27, 2023 at 12:15 am

    #4272348

    Hi

    We just updated from SQL2014 to SQL2022.

    Most everything went fairly well - except I have about 6 stored procs that move allot of data around that are suddenly not working. When looking at whoisactive they are generating CX waits. They stall all day with no results. They ran fine in SQL2014.

    I enabled the query store hoping that would give me a clue as to what is causing the waits for these statements. Parnellism is the issue at least that is what the query store is telling me for pretty much all the problem queries.

     

    22

    Many years ago I had a somewhat similar issue and was able to use trace flag 9481 on most of these statements to get them to run. No such luck after this update. Tried removing the 9481 and even tried several newer flags but no luck.

    Any thoughts on global type places I could set to see if it helps, flags, DOP, degree of parallelism settings? I tried changing to sql2014 compat mode and matched my old servers maxdop (6) and Parnellism settings (5). Tried changing compt to sql2022 and parellism to 15. Nothing seems to help.

    This is a new server with High Availability - old server had HA also fwiw.

    Any ideas?

    • This topic was modified 1 year, 3 months ago by  krypto69.

  • August 28, 2023 at 1:10 am

    #4273190

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • August 28, 2023 at 7:52 am

    #4273433

    • Have you rebuild all indexes and statistics after migration/upgrade ?
    • Did you compare old vs new SQLplans ?
    • How many indexes / indexed views are based on the target table  ?
    • Please post schema and sqlplan if you can.

     

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • August 28, 2023 at 9:29 am

    This was removed by the editor as SPAM

  • August 28, 2023 at 2:30 pm

    #4273781

    Johan Bijnens wrote:

    • Have you rebuild all indexes and statistics after migration/upgrade ?
    • Did you compare old vs new SQLplans ?
    • How many indexes / indexed views are based on the target table  ?
    • Please post schema and sqlplan if you can.

    I have to tell you that rebuilding all indexes is probably the worst thing in the world that can be done here.  In fact, index maintenance is seriously overrated.  Worse yet, most people follow the now old "Best Practices" method and I'm here to tell you that they were never intended to be take as "Best Practices" and that they're actually a WORST practice that actually perpetuates page splits and makes them much worse because most people have no idea how things like REORGANZE actually works and does not work.

    With the understanding that, contrary to what the title implies, it's not just about Random GUIDs... I just use those because they're the "poster child" for fragmentation and I actually destroy that myth and lay waste to what most people consider to be the "Best Practice" for index maintenance.  And, watch the outtake after the the Q'n'A section.  The 'tube is 82 minutes long but you won't want to miss a bit because it's a continuous barrage of proofs in the style you know me for.

    https://www.youtube.com/watch?v=rvZwMNJxqVo

     

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

  • August 28, 2023 at 6:01 pm

    #4273938

    CTFE of 5 is generally EXTREMELY low. Even 15 is pretty low. Is this in production or are you still testing it?

    Hopefully you are still testing - if you are I would increase it to at least 50 then test. (I do new implementations starting at 75)

    I am not a fan of globally disabling the modern CE in 2016+ would instead disable it where you need to disable it with hints or DB level if you have to.

    Do you have a paste the plan available?

  • August 31, 2023 at 9:11 pm

    #4277178

    Off-topic, but awesome to SEE Jeff Moden after all these years! The YT vid was a bit over my head but still fascinating.

    "If I had been drinking out of that toilet, I might have been killed." -Ace Ventura

  • August 31, 2023 at 9:56 pm

    #4277187

    autoexcrement wrote:

    Off-topic, but awesome to SEE Jeff Moden after all these years! The YT vid was a bit over my head but still fascinating.

    I've PM'd you to ask a favor, please.

     

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

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

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