Pintable into cache

Question

Pintable into cache

mtz676

SSCrazy Eights

Points: 9180
More actions
July 5, 2020 at 11:20 am

#3767545
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 3 years, 9 months ago by mtz676.

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

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

Sergiy SSC Guru Points: 110206 More actions · Answer 1

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.

_____________
Code for TallyGenerator

mtz676 SSCrazy Eights Points: 9180 More actions · Answer 2

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

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 3

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

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.

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

Steve Jones - SSC Editor SSC Guru Points: 734449 More actions · Answer 4

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.