Fragmented Indexes

Question

Post reply

Fragmented Indexes

Viewing 6 posts - 16 through 20 (of 20 total)

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

Ed Wagner SSC Guru Points: 287015 More actions · Answer 1

Jeff Moden (8/8/2015)

Lynn Pettis (8/7/2015)

delizat (8/7/2015)

Every situation is different, but this is the order of my backup jobs:

1. Reorganize/Rebuild index

2. Check integrity

3. Update Statistics

4. Backup database.

Please tell me you aren't updating statistics on indexes that you rebuilt.

Agreed. REBUILDing an index automatically rebuilds statistics. You only need to rebuild stats for indexes that have been REORGINIZEd.

Agreed, but the statistics generated by REBUILDing an index would have stats_date that would calculate to an age of near zero. Shouldn't the statistics maintenance procedure take that into account and know to not update them? Then again, I don't use Ola's scripts and I haven't checked out the two products Jason mentioned. Maybe I should. 😉

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 2

Ed Wagner (8/8/2015)

Jeff Moden (8/8/2015)

Lynn Pettis (8/7/2015)

delizat (8/7/2015)

Every situation is different, but this is the order of my backup jobs:

1. Reorganize/Rebuild index

2. Check integrity

3. Update Statistics

4. Backup database.

Please tell me you aren't updating statistics on indexes that you rebuilt.

Agreed. REBUILDing an index automatically rebuilds statistics. You only need to rebuild stats for indexes that have been REORGINIZEd.

Agreed, but the statistics generated by REBUILDing an index would have stats_date that would calculate to an age of near zero. Shouldn't the statistics maintenance procedure take that into account and know to not update them? Then again, I don't use Ola's scripts and I haven't checked out the two products Jason mentioned. Maybe I should. 😉

If he's using maintenance plans, they don't check statistics age or index fragmentation. The recommended solutions do.

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

Josh Murrah SSC Journeyman Points: 89 More actions · Answer 3

Another vote for Ola Hallengren's amazing scripts for index maintenance among other things.

I generally run it with best-practice settings + force statistics as often as I can, daily if possible or weekly. It's overkill, but heads off a lot of bone-headed questions from analysts like "I read that product X needs maintenance Y are you doing it" "yes, we do that daily".

delizat Say Hey Kid Points: 689 More actions · Answer 4

Lynn Pettis (8/7/2015)

delizat (8/7/2015)

Every situation is different, but this is the order of my backup jobs:

1. Reorganize/Rebuild index

2. Check integrity

3. Update Statistics

4. Backup database.

Please tell me you aren't updating statistics on indexes that you rebuilt.

Just a sarcastic comment and no constructive information? Every situation is different and there are no simple answers.

The post just prior shows the simple reorganize/rebuild maint step. It looks at fragmentation and decides to reorganize, rebuild, or do nothing. Depending on what it decides statistics will be run.

Here's a link from Paul S. Randal:

Ed Wagner SSC Guru Points: 287015 More actions · Answer 5

delizat (8/10/2015)

Lynn Pettis (8/7/2015)

delizat (8/7/2015)

Every situation is different, but this is the order of my backup jobs:

1. Reorganize/Rebuild index

2. Check integrity

3. Update Statistics

4. Backup database.

Please tell me you aren't updating statistics on indexes that you rebuilt.

Just a sarcastic comment and no constructive information? Every situation is different and there are no simple answers.

The post just prior shows the simple reorganize/rebuild maint step. It looks at fragmentation and decides to reorganize, rebuild, or do nothing. Depending on what it decides statistics will be run.

Here's a link from Paul S. Randal:

I don't think Lynn's post was a sarcastic comment - it highlighted something pertinent. For further information, see the posts by Jeff and I immediately after Lynn brought up the point.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 6

delizat (8/10/2015)

Lynn Pettis (8/7/2015)

delizat (8/7/2015)

Every situation is different, but this is the order of my backup jobs:

1. Reorganize/Rebuild index

2. Check integrity

3. Update Statistics

4. Backup database.

Please tell me you aren't updating statistics on indexes that you rebuilt.

Just a sarcastic comment and no constructive information? Every situation is different and there are no simple answers.

The post just prior shows the simple reorganize/rebuild maint step. It looks at fragmentation and decides to reorganize, rebuild, or do nothing. Depending on what it decides statistics will be run.

Here's a link from Paul S. Randal:

Not a sarcastic comment at all. A valid statement/question of concern.

I have seen DBAs that have rebuilt indexes and followed that with updating statistics using a 20% sample rate. Waste of time considering that the statistics on the indexes that were rebuilt were updated with a 100% sample rate while the index was rebuilt.