• anujkumar.mca - Tuesday, December 26, 2017 6:57 AM

    Hey Guys,

    I stuck in a situation where I need to do parallel maintenance on few tables, Can someone please suggest a way to do it
    Example: There are 50 table name in a tables XYZ, I need to start index rebuild at 5 tables simultaneously in diff-2 sessions.
    Session 1 - rebuild index at Table A
    Session 2 - rebuild index at Table B
    Session 3 - rebuild index at Table C
    Session 4 - rebuild index at Table D
    Session 5 - rebuild index at Table E

    To be honest, I think this is a plan for doing things more slowly unless you can guarantee that 5 different spindles (1 for each table) will come into play.  "Going Parallel" is NOT a panacea for performance.  You do have to consider the capacity of the "pipe" and what you're going to do to the read/write heads on the disk(s) involved.

    As for your question, you could build a "maintenance job" that would call all 5 pieces of code in parallel or you could do the same by calling xp_CmdShell to call SQLCMD 5 times using the DOS "START" command.  I still think it's going to be a mistake performance wise unless the tables really are on 5 different sets of spindles.

    As a bit of a sidebar, if you've not carefully planned for the correct FILL FACTOR for each of these indexes, then the rebuilds could put a world of hurt on your system in the form of massive page splits.  Personally, I don't rebuild indexes on a general basis anymore (and haven't since 17 Jan 2016).  The only table that I have rebuilt the indexes on in those two years is one that suffers from regular deletes that don't free up entire pages for reuse.

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