Rebuild Indexes not working

Question

Post reply

Rebuild Indexes not working

navtec

Hall of Fame

Points: 3206
More actions
June 20, 2014 at 4:39 am

#396942

Hi,
I have a maintenance plan which rebuilds indexes on ALL databases on my instance. I'm running SQL Server 2008 R2 and it is fully up to date with it's service pack and CU.
It completes successfully within a minute or two but the next morning when I check, indexes are still heavily fragmented.
I manually rebuilt an index which was 99% fragmented and it has rebuilt it successfully.
But when my rebuild index maintenance plan runs it seems to ignore these fragmented indexes.
This is happening for clustered and non-clustered indexes.
Any help much appreciated.

Viewing 11 posts - 1 through 10 (of 10 total)

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

Beatrix Kiddo SSC-Dedicated Points: 32407 More actions · Answer 1

You're not looking at heaps, are you? Those won't be affected.

navtec Hall of Fame Points: 3206 More actions · Answer 2

Yes I do have heap tables on there but I just manually rebuilt an index for the MSDB database which was 99% fragmented.

This particular index in MSDB wasn't defragmented using my maintenance plan.

Lowell SSC Guru Points: 323518 More actions · Answer 3

check the size of the table; small tables under something like 1000 pages of data will not be affected by a reindex. that could be what you are seeing: small tables with 99% fragmentation, which is normal;

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

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

Really small table?

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

navtec Hall of Fame Points: 3206 More actions · Answer 5

The table in msdb is 6828MB.

Row count is 6385.

How would I work out if it's less than 1000 data pages?

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

zedtec (6/20/2014)
Hi,
I have a maintenance plan which rebuilds indexes on ALL databases on my instance. I'm running SQL Server 2008 R2 and it is fully up to date with it's service pack and CU.
It completes successfully within a minute or two but the next morning when I check, indexes are still heavily fragmented.
I manually rebuilt an index which was 99% fragmented and it has rebuilt it successfully.
But when my rebuild index maintenance plan runs it seems to ignore these fragmented indexes.
This is happening for clustered and non-clustered indexes.
Any help much appreciated.

Does your maintenance plan also have a "shrink" in 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)

navtec Hall of Fame Points: 3206 More actions · Answer 7

navtec

Hall of Fame

Points: 3206

June 20, 2014 at 9:22 am

#1723634

It used to until recently but not any more.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 8

zedtec (6/20/2014)
It used to until recently but not any more.

Ok. So when did you take that out and have you checked other jobs to make sure that none of them have DB Shrinks in them?

The other things to check are...

1. Is there an overnight job that repopulates or causes massive updates to the tables in question?

2. Are the clustered indexes on columns that are unique, narrow, not null, and ever-increasing?

3. Check the settings on the database... is it set for "autoshrink"?

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

navtec Hall of Fame Points: 3206 More actions · Answer 9

I took the shrink out 2 days ago.

Only one maintenance plan had the shrink. I have checked all of the maintenance plans since and they are all completing successfully including the index rebuild.

Auto shrink is not taking place.

Auto growth is taking place for the data and log files for all the databases.

As far as I know the clustered indexes are unique and not null.

navtec Hall of Fame Points: 3206 More actions · Answer 10

Also to add to that, index rebuilds were not taking place up to now.

Added this in when the shrink was taken out.

There aren't any overnight processes taking place which update the databases.