Maintenance Plan Reindex Task

[pjw001]

I have been running reindex tasks within Maintenance Plans on a number of installations and they do not seem to do anything.
This applies to SQL2000 and, I think, also SQL2005.

Am I doing something wrong or is there a fault with the software?

 

[SQLScholar]

What do you expect them to do?

What makes you say it isnt doing anything?

Are you rebuilding or reorganising the indexes - or both?

Remember small indexes may not reduce fragmentation even with a rebuild.

As per MS:

http://msdn.microsoft.com/en-us/library/ms188388.aspx

Rebuilding or reorganizing small indexes often does not reduce fragmentation. The pages of small indexes are stored on mixed extents. Mixed extents are shared by up to eight objects, so the fragmentation in a small index might not be reduced after reorganizing or rebuilding it. For more information about mixed extents, see Understanding Pages and Extents.

----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[pjw001]

Thanks for the reply.

I would expect a reindex to reduce rowmodctr to zero.

Running DBCC DBREINDEX does, but the maintenance task does not.

Also, I have seen the maintenance task run in a few minutes on a 100 Gb database. It cannot possibly have done anything significant in this time.

I'm not sure what you mean by 'rebuilding or reorganising the indexes'. Please clarify.

Do you know what they mean by 'small indexes'?

Thanks.

 

[SQLScholar]

Rebuilding or reorganise. If i remember correctly there is a choice in the maintenance plan. Details below.

http://technet.microsoft.com/en-us/library/ms189858.aspx

No sure about small.... it is open to interpretation.

In terms of rowmodctr - are you updating the stastics after the index maintenance. If not then this figure will be incorrect - see here:

http://msdn.microsoft.com/en-us/library/ms190283.aspx

----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------