Home Forums SQL Server 2017 SQL Server 2017 - Administration How many workers can I use and how many are active? RE: How many workers can I use and how many are active?

  • December 25, 2017 at 2:07 pm

    #1973389

    Lynn Pettis - Tuesday, December 19, 2017 3:19 PM

    The other thing, that Jeff hasn't said yet, check the dynamic code that is being generated as well as how it is being executed.  I solved some of our dynamic SQL issues by changing how it was generated and executed.  Many of our dynamic SQL code being generated where the same except for variable data that could be passed in as scalar variables or table valued parameters using sp_executesql.

    In the process I also found better ways to write the SQL as well.

    On that note, also check how it is being cached.  While poorly written and non-parameterized dynamic SQL may execute very quickly, it may have to compile each and every time it occurs.  We had such a thing... it was executing in 100ms (which is still about 10-20 times longer than it needed to) but it was taking between 2 and 22 SECONDS to compile and it had to compile on every call and it was being called an insane number of times every hour.

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