Pintable into cache

  • mtz676

    SSCrazy Eights

    Points: 8907

    Pintable into cache to avoid fragmentation

    I have a table(OLTP database) which gets fragmented every day and it goes through the defragmentation process every week.Which means I am doing the same process over and again every week. Can I pin such tables in the memory permanently or for a specific period of time and write back the table to disk only on schedule. Can this process decrease fragmentation of such tables?If this is possible how do I achieve this.

    FYI: https://www.brentozar.com/archive/2012/08/sql-server-index-fragmentation/

     

    • This topic was modified 1 month, 1 week ago by  mtz676.
  • Sergiy

    SSC Guru

    Points: 109784

    To avoid fragmentation you need to answer the question - why does it happen?

    you might need to change the indexing strategy on the table, so adding new records want cause significant fragmentation.

    and, if the inserts are truly random and there is no way to predict the order, you may use index padding.

    Having more details about your circumstances we could provide a better advice.

  • mtz676

    SSCrazy Eights

    Points: 8907

    Thank you but my question was can caching/pinning tables be an option to avoid fragmentation ?

  • Jeff Moden

    SSC Guru

    Points: 996843

    mtz676 wrote:

    Thank you but my question was can caching/pinning tables be an option to avoid fragmentation ?

    Maybe but  I don't believe that even In-Memory tables with delayed durability will help much and that comes at a higher price than what you really want to pay in a lot of cases.  The notion of "delayed durability" will also be a lot shorter than you're probably looking for.

    That being said, my real answer would be "No".

    I could leave you hanging there but that's not my nature nor Sergiy's... Sergiy tried to help you with the real problem that you're facing.

    That's what Sergiy was trying to get at...  You need to figure out why such massive fragmentation is happening so quickly and then fix that problem instead of creating other problems with a work around that probably won't be as effective as just fixing the original problem.  Sometimes  the fix is incredibly easy, especially on OLTP systems.

    I can help a whole lot there but you don't seem interested in that aspect.  If such a lack of interest on your part isn't true, post back with the CREATE TABLE for the table and be sure to include all constraints and indexes.  Also, please try to identify the Insert/Update and usage patterns...

    1.  Are you inserting just one row at a time or more and, if more, what's a typical batch size?
    2. Are you doing any updates to the data in the table.  If so, how soon after the inserts are you doing such updates and what are the typical columns being updated?
    3. When you SELECT from the table, is it generally one row, many rows, or a combination.
    4. How many rows are in the table?

    Also, if you're using the supposed but almost completely incorrect "Best Practices" of reorganizing between 5 and 30%  and rebuilding at over 30%, that could also be a part of the problem.

    --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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 720484

    You cannot pin tables into RAM. That was removed because of issues with the process many versions ago.

    You can look at in-memory tables, but this is a big, disruptive change, and it is hard to undo. I'd follow Sergiy's advice.

Viewing 5 posts - 1 through 5 (of 5 total)

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