Reindex DB on regular basis

Question

Reindex DB on regular basis

Edvard Korsbæk

SSCertifiable

Points: 5999
More actions
December 15, 2016 at 11:15 pm

#319758

Hi all!
I have a database where app. 10.000 records are deleted/updated/inserted pr. day.
I have understood from other sources, that i should reindex the db on a regular basis.
Locally, where i backup/restores it up to many times pr. day, the speed has went down to a crawl.
Where do i find some 'Hands on' info on 'How to', and pro et cons?
The database is today a 2008R2, but wil get updated to 2012 or 2016 sooner or later
DB size: 26807,88 MB
Best regards
Edvard Korsbæk

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

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

Eirikur Eiriksson SSC Guru Points: 182948 More actions · Answer 1

Edvard Korsbæk (12/15/2016)
Hi all!
I have a database where app. 10.000 records are deleted/updated/inserted pr. day.
I have understood from other sources, that i should reindex the db on a regular basis.
Locally, where i backup/restores it up to many times pr. day, the speed has went down to a crawl.
Where do i find some 'Hands on' info on 'How to', and pro et cons?
The database is today a 2008R2, but wil get updated to 2012 or 2016 sooner or later
DB size: 26807,88 MB
Best regards
Edvard Korsbæk

Suggest you set up and use Ola Hallengren's SQL Server Maintenance Solution[/url] and then tweak the values for reorganize & rebuild as needed, i.e. lower thresholds during quiet days etc..

😎

Steve Jones - SSC Editor SSC Guru Points: 738960 More actions · Answer 2

Eirikur has good advice. You want to reindex, but when stats might get out of date, or fragmentation gets high. Ola's scripts, or the Minion scripts[/url], can help you here.

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

Actually, I suggest NOT rebuilding indexes. As a part of a large experiment based on some videos by Brent Ozar, I haven't rebuilt indexes on my main prod box since the 17th of January and performance has actually gotten better thanks to a phenomena that I call a "natural fill factor".

The key is to regularly rebuild statistics that need it.

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

Summer90 SSC-Dedicated Points: 32891 More actions · Answer 4

Reindexing can help.. but it depends. If you have auto stats on the database that should update your stats on the table(s). However, for heavy delete,insert type tables I run a sp_update stats on the database just to make absolutely sure stats are up to date. I had an issue a few years back where this type of scenario would play out and things would become very slow. All I would do is run the update stats process and things would go back to normal. After a couple of times I created a SQL Agent job that runs after a heavy delete/insert process completed and things have run find since then.

Jeff Moden SSC Guru Points: 1004648 More actions · Answer 5

JasonClark (12/20/2016)
Run an index maintenance process regularly. However, your index maintenance process only rebuild/reorganize the indexes that specifically require it:
http://dba.stackexchange.com/questions/4283/when-should-i-rebuild-indexes
this may help you.

The key here is that most of them actually require it even when classic indicators say they need it. The optimizer doesn't consider fragmentation and natural fill factors on NCIs keep them from exploding for size. The only time that a CI get's into trouble is if there are expansive (not to be confused with "expensive") updates that cause rows to grow or if the CI isn't based on the following principles.

1. Narrow

2. Ever Increasing

3. Immutable

4. Unique

5. Not Nullable

6. Fixed Width

if the 2nd or 3rd rule is violated, then you will need to rebuild CIs that have suffered large numbers of page splits to recover wasted disk space. Otherwise, rebuilding indexes is pretty much a waste.

The real key for performance for "maintenance" is that statistics must be updated and probably more often than you would think.

Just to reiterate what I've said previously, I haven't done any index maintenance on my prod box since 17 Jan 2016 and performance has actually gotten better thanks to that "natural fill factor" thing that I talked about. I also don't get the timeouts due to page splits like I used to after index maintenance.

Also, this isn't my original idea. Brent Ozar has been talking about this for quite a while and, despite the fact that I thought he was drinking bong water with two straws and it goes against everything that we've been taught, I tried it and, by golly, it bloody well works for me.

YMMV if you don't have CIs in the same condition that I do.

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