Index rebuild suddenly takes 5 times as long ?!?

Question

Index rebuild suddenly takes 5 times as long ?!?

Mick Opalak

SSCommitted

Points: 1631
More actions
March 15, 2012 at 9:16 am

#270108

SQL2008 R2
We have a 12 Gb database that is highly transactional. Some indices get to 95% fragmentation in just 24 hours, so we reindex nightly. Our reindex job does a reogranization if fragmentation is below 30% and an index rebuild if its more than that, so most indices are rebuilt nightly.
This has been running in 20 - 30 minutes each night with no interuption to the end users (24 hour database/applicaiton) because we specify an ONLINE rebuild.
Suddenly the job is taking over 2 hours to complete and the end users get continuous timeouts while it is running, which is unacceptable to the business. I've been told that the database has not been altered in any way and no new indices have been created. I have a theory on what has happened, but no way to prove it. My hunch is that due to normal data growth over time, there is not enough free space in TempDB to do the rebuilds ONLINE, so the server is forced to do the rebuilds OFFLINE, which causes key indices to become temporarily unavailable, and puts locks on the base tables both of which cripple performance. Does this sound right? Should I just increase the amount of free space on the drive which contains TempDB?

Viewing 15 posts - 1 through 15 (of 22 total)

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

SS999 SSCommitted Points: 1663 More actions · Answer 1

Are there any activities that go around during the time of index rebuild? Try scheduling this activity without interfering any other jobs or schedules.

Try shrinking the tempdb database prior to rebuilding the indexes.

Mick Opalak SSCommitted Points: 1631 More actions · Answer 2

We already run the job during the period of lowest activity, but there is always activity. It is a 24 hour / day application.

How would shrinking TempDB beforehand help?

SS999 SSCommitted Points: 1663 More actions · Answer 3

You are releasing space to the OS from tempdb, which might get ur indexes rebuild little faster than usual when you are doing online.

adb2303 SSCertifiable Points: 7588 More actions · Answer 4

any changes to the environment or databases, i.e. LOB data, MAXDOP settings? Do you use SORT_IN_TEMPDB? Do you have an idea of what fragmentation was like before and after? Doing an index rebuild also updates statistics for that index (reorg doesn't), so that will add to the time taken. Your database recovery model and any HA/DR solution you have may also affect these types of jobs. Any further information you have will help

Mick Opalak SSCommitted Points: 1631 More actions · Answer 5

adb2303 (3/15/2012)
any changes to the environment or databases, i.e. LOB data, MAXDOP settings? Do you use SORT_IN_TEMPDB? Do you have an idea of what fragmentation was like before and after? Doing an index rebuild also updates statistics for that index (reorg doesn't), so that will add to the time taken. Your database recovery model and any HA/DR solution you have may also affect these types of jobs. Any further information you have will help

No changes to the database or SQL Server that I'm aware of.

Yes, we use SORT_IN_TEMPDB.

Fragmentation before for most tables is 75% - 95%. Fragmentation after the rebuild should be close to zero.

Divine Flame SSCoach Points: 15943 More actions · Answer 6

Mick Opalak (3/15/2012)
SQL2008 R2
We have a 12 Gb database that is highly transactional. Some indices get to 95% fragmentation in just 24 hours, so we reindex nightly. Our reindex job does a reogranization if fragmentation is below 30% and an index rebuild if its more than that, so most indices are rebuilt nightly.

Increase the fill factor for the indexes where you need to re-index it everyday.

Sujeet Singh

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 7

My recommendation would be to partition the larger tables using an ever-increasing constraint such as a transaction date. If you "align" the indexes with the partitions, the rebuilds will go much faster because it's very likely that only the last 1 or 2 partions will become fragmented.

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

gladys14g2 SSC Enthusiast Points: 104 More actions · Answer 8

Some indices get to 95% fragmentation in just 24 hours, so we reindex nightly. Our reindex job does a reogranization if fragmentation is below 30% and an index rebuild if its more than that, so most indices are rebuilt nightly.

Divine Flame SSCoach Points: 15943 More actions · Answer 9

gladys14g2 (3/15/2012)
Some indices get to 95% fragmentation in just 24 hours, so we reindex nightly. Our reindex job does a reogranization if fragmentation is below 30% and an index rebuild if its more than that, so most indices are rebuilt nightly.

???

Sujeet Singh

Mick Opalak SSCommitted Points: 1631 More actions · Answer 10

OK, followup questions:

If I issue an ALTER INDEX REBUILD command with ONLINE=ON, and there is not enough disk space or memory to do it ONLINE, will the SQL Server then rebuild it OFFLINE or will it simply fail and return an error message?

If I issue an ALTER INDEX REBUILD command with ONLINE=ON, is there a log file or some way for me to verify that it is indeed building the index ONLINE and not OFFLINE?

Thanks!

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 11

Ah! I just remembered what the cause of this slowdown may be. Did you (or someone) recently change the database from either the "Simple" or "Bulk Logged" recovery mode to the "Full" recovery mode?

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

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 12

SS999 (3/15/2012)
You are releasing space to the OS from tempdb, which might get ur indexes rebuild little faster than usual when you are doing online.

Oh, be careful now... that would lead to some pretty bad fragmentation of TempDB as well as making it real tough on large queries that need to use "work" tables as part of the query plan. It's also seriously not recommended to shrink TempDB unless you're in the Single User mode.

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

Mick Opalak SSCommitted Points: 1631 More actions · Answer 13

Issue Resolved: as I expected, after increasing the size of the drive that the data file for TempDB is on, the job went back to running in 20 minutes and not causing any interfering locks.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 14

I want to jump on the "you MUST adjust your fill factors to avoid such huge index fragmentation" bandwagon!! That is just crazy bad for your IO subsystem AND database application performance!!

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service