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...
- Are you inserting just one row at a time or more and, if more, what's a typical batch size?
- 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?
- When you SELECT from the table, is it generally one row, many rows, or a combination.
- 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.
is pronounced "ree-bar
" and is a "Modenism
" for R
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?"
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)