Rebuild Index first or Update statistics first?

Question

Rebuild Index first or Update statistics first?

Tac11

SSCertifiable

Points: 6980
More actions
November 16, 2014 at 2:03 pm

#313036

I am planning to use Maintenance plan to rebuild index and update statistics.
I know, rebuild indexes also update index statistics not the column. if I run rebuild index first then index statistics will be updated, then won't update statistics try to update index statistic again or it just ignores it because it is up to date?
Bottom line, which should run first Index rebuild or update statistics? Suggest please.

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

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

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 1

smtzac (11/16/2014)
I am planning to use Maintenance plan to rebuild index and update statistics.
I know, rebuild indexes also update index statistics not the column. if I run rebuild index first then index statistics will be updated, then won't update statistics try to update index statistic again or it just ignores it because it is up to date?
Bottom line, which should run first Index rebuild or update statistics? Suggest please.

For the very reasons you mention, my bottom line would be to not use Maintenance Plans to do anything of such importance. If you're not inclined to write your own, there are a lot of good scripts out there that take all of this into account.

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

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

If you are going to stick with maintenance plans (not the best for index/stats maintenance), then you can select 'column statistics' on the stats update task and only the non-index stats will then be updated.

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

Tac11 SSCertifiable Points: 6980 More actions · Answer 3

Thank you guys. One more question: Should update statistics job run first or Index rebuild job? Is it OK to use maintenance plan to run update statistic with column only? or is there any good script out there?

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 4

smtzac (11/17/2014)
One more question: Should update statistics job run first or Index rebuild job?

If you set the stats task to column stats only, it doesn't make the slightest difference which order you run them, as they're doing independent things.

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

Tac11 SSCertifiable Points: 6980 More actions · Answer 5

if i select 'All statistics' then should Index job run first and then update statistics? And how often should update statistic on large DB?

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 6

Don't select all statistics if you're rebuilding indexes. It's a waste of time. If you're rebuilding indexes (maint plan rebuilds all), then select 'column statistics' on your stats update job.

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

Tac11 SSCertifiable Points: 6980 More actions · Answer 7

Sorry, I still didn't get my answer. Generally which should run first? Rebuild index or Update statistics.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 8

If you set the stats task to column stats only, it doesn't make the slightest difference which order you run them, as they're doing independent things.

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