• Perhaps partitioning isn't the answer.  For example, unless you can isolate and guarantee that each filegroup is on a separate spindle (or spindles), you're still going to get head thrashing at the disk level.  From what I've seen, partitioning not only isn't a panacea of performance, it frequently makes performance slower for multiple reasons.  Rumor has it that it's because it has to go through more than one B-TREE to get where it's going.  I've observed an increase in slowness on one particularly large table that I made the mistake of partitioning using Partitioned Tables technology.

    I've also found that, for those reasons and more, parallel processing is also NOT a panacea of performance.  If you do have guaranteed separation at the spindle level, then you might get some increase in performance.

    I say that because you still only have on log file, which is a performance bottleneck if you have poorly written code.

    Also remember that data must be in memory to be processed.  Separate filegroups aren't really going to help there because whatever data you're working with still has to be in memory before you can do anything with it.

    That leaves really only one thing... the code.  And that's where the performance needs to be.

    I'd stop looking for the proverbial "magic bullet" and start looking closely at where the performance actually needs to be... in the code.  Find your top 10 most costly queries (not to be confused with the longest running queries) and fix them.  If the queries are generated by ORM or managed code, consider moving that functionality to stored procedures.

    Either that or do like EBay did... buy hundreds of servers and assign just a couple of micro services to each one.  😉

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