Rebuild Index Job on a Large DB

Question

Rebuild Index Job on a Large DB

cooldude001

Mr or Mrs. 500

Points: 528
More actions
November 25, 2024 at 4:35 pm

#4488928

We have a db close to 3 tb and rebuilding/ reorganizing indexes weekly is taking more than 8 hrs.My goal is to reduce the time job is taking. Can anyone suggest a workaround, I'm interested on how the job can be managed in large environments, considering the maintenance window we are given is limited.
We are using Ola Hallengren script with Rebuild threshold of 30 and Reorganize threshold of 20.
(SQL 2019 enterprise edition with AOAG)
1)Has anyone tried dividing Ola Rebuild job into multiple jobs to run them in parallel. Ola jobs load data into commandlog table, will there be any issues/blocking if we do that?
2)Is there an option to stop and resume rebuild with Ola job, will there be any performance impact?
3)I looked into where it was taking time, Reorganizing large indexes was taking more time. Does reducing threshold and rebuilding them make it faster?
Appreciate any help on this..

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

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

Jo Pattyn SSC-Dedicated Points: 32352 More actions · Answer 1

Have a look at https://www.sqlservercentral.com/forums/topic/microsoft-recommendations-for-update-stats There is a whole discussion why you should not rebuilding / reorganizing indexes unless necessary

Jeff Moden SSC Guru Points: 1004425 More actions · Answer 2

Heh... "It's Shake'n'Bake and I helped"! 😀

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

cooldude001 Mr or Mrs. 500 Points: 528 More actions · Answer 3

Thankyou Jeff & Jo , do you suggest to completely turn off Rebuild index job?

As the tables get more and more fragmented, wouldn't it contribute to memory pressure as we are adding more no.of pages into memory.

If I run it less frequently and increase threshold values, what can be a good value to start with? (I'm using 20 and 30 for level1 and level2 now)

Reorganizing indexes can cause page splits, is it better to remove Reorganize and only have Rebuild then?

I'm using below values now

@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE