DBA -tasks and solutions

Question

DBA -tasks and solutions

Viewing 4 posts - 16 through 18 (of 18 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

ramana3327 (2/15/2016)
If you are not using enterprise edition, I think it would be ok with rebuild indexes weekly using maintenance plans

Don't use maintenance plans for index rebuilds, no matter what edition. The maintenance plan task is braindead and does way more work than needed.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ramana3327 SSCoach Points: 19722 More actions · Answer 2

Hallengren script rebuild/reorg the indexes based on the Index fragmentation %. It has to run everyday

For standard edition doesn't have the option to rebuild the indexes online. So we are doing weekly index rebuild instead of running everyday index maintenance also I think rebuild index requires less time and less log space compare to reorg

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

ramana3327 (2/16/2016)
Hallengren script rebuild/reorg the indexes based on the Index fragmentation %. It has to run everyday

It doesn't have to. Run it when you feel you need to.

So we are doing weekly index rebuild instead of running everyday index maintenance

Nothing wrong with that. Use Ola's scripts. They won't rebuild things that don't need rebuilding. Tweak the thresholds if you want more things rebuilt and less things reorganised, but don't use the very stupid maint plans that blanket rebuild absolutely everything even if the table hasn't had a data change in 5 years.

also I think rebuild index requires less time and less log space compare to reorg

You would be wrong there.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 4

ramana3327 (2/16/2016)
Hallengren script rebuild/reorg the indexes based on the Index fragmentation %. It has to run everyday
For standard edition doesn't have the option to rebuild the indexes online. So we are doing weekly index rebuild instead of running everyday index maintenance also I think rebuild index requires less time and less log space compare to reorg

It's actually more important to make sure your statistics are updated. Yeah, the rebuilds will do that for the indexes you rebuild but not for the indexes that you don't. Think of clustered indexes with ever-increasing leading keys. If you don't have problems with your nightly runs, you might actually slow down your GUI code by rebuilding the indexes (especially the non-clustered indexes) if the FILL FACTOR isn't correct for indexes on tables that take a lot of inserts.

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