Post reply

Please Suggest Partition boundaries

  • December 22, 2015 at 8:52 pm

    #1847633

    Yes Agreed to all your comments..

    Let me switch back to basics and work on analyzing the data.

  • December 23, 2015 at 6:25 pm

    #1847864

    TheSQLGuru (12/22/2015)

    Jeff Moden (12/22/2015)

    TheSQLGuru (12/22/2015)

    Partitioning CAN make PROPERLY DESIGNED report queries go much faster when partition elimination can allow said queries to hit a small fraction of the total data. There is no guarantee that ANY of your queries can benefit from this.

    To be honest, I've never seen that to be true when compared to a properly indexed monolithic table and a good query. The only place I've ever seen it help with performance is on poorly formed code, SWITCHing in and out, and index maintenance on temporally partitioned tables that suffer few, if any, updates on earlier partitions. Not saying that it couldn't happen but I've not seen it happen so far and it actually makes sense why it wouldn't (except for junk code) and so have my doubts that it ever actually helps properly.

    There are three cases that come to mind:

    A) Concurrent access where partition-level locking lets multiple queries run at the same time because they don't block other partition accesses. This is obviously in systems where NOLOCK isn't the default mode for SELECTs and some updates may occur to the data during reporting periods.

    B) Tables where the number of rows and/or sizes of keys leads to exceptionally deep indexes over the entire table. The N extra page reads per seek can add up. And yes, there are access patterns on large fact tables at are most efficient using non-clustered index seeks as opposed to scans.

    C) Not a report query improvement, but various maintenance activities that can be done at the partition level instead of across the entire table can win as well.

    Item (C) is the reason I might partition along with the missing (D) of greatly reduced backups on temporal WORM tables (like audit tables). I've apparently not yet run into either (A) or (B). My "deepest" table is a paltry 50 million rows or so.

    --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 2 posts - 16 through 16 (of 16 total)

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