x

Index Fragmentation

Hi,

In order to deal with index fragmentation, I have set up a nightly maintenance plan that does index rebuild on line and 90% fill factor. But this maintenance plan does not de-fragment the indexes fully. therefore I do need to manually rebuild the indexes. I have a session with Microsoft Premium Engineers and they just informed that maintenance plan task can not completely does this de-fragmentation. I need to do that manual rebuild. But I am not happy with their answer as I can not understand why can't it do that? Can anyone explain me the reason of this? Also can we rebuild the index online through SSMS?

Regards Zahid
more ▼

asked Jan 11, 2012 at 07:56 AM in Default

Zahid gravatar image

Zahid
91 9 10 11

I agree, I would be curious as to why they are saying that the index plan is not completing. I am not a big fan of the plans, and even more so for databases that have any sort of production on them.

One think you may want to consider is using a script to rebuild your indexes. There are a couple really good ones out there:

SQLFool (http://sqlfool.com/2009/03/automated-index-defrag-script/) Ola Hallengren (http://ola.hallengren.com/)

One of the reasons that I recomend doing this is because this way you can re-index only those indexes that need it.
Jan 11, 2012 at 08:18 AM Chris shaw
(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

What reason did Microsoft give for the Maintenance Plan not completing?

Are these large or small indexes?
more ▼

answered Jan 11, 2012 at 07:59 AM

Kev Riley gravatar image

Kev Riley ♦♦
53k 47 49 76

MS did not explain the reason. They are small indexes. also I have tried with SQL command to rebuild index ALTER INDEX ALL ON dbo.TableName REBUILD WITH (FILLFACTOR = 90, ONLINE = ON)

Still, the Fragmentation that is Avg_Fragmentation_in_Percent is 93.

I think If i use wizard then it might de-frag the indexes. But it realy time consuming to rebuild one by one using wizard.

Any idea? Is it the fill factor? As fragmentation is of two types: Internal and External. I think Internal fragmentation has some relation with fillfactor.
Jan 11, 2012 at 08:46 AM Zahid
(comments are locked)
10|1200 characters needed characters left

Indexes that are less than 8 pages in size will be stored on mixed extents within the system. You can't defrag them. In fact, trying to defrag them can even cause fragmentation to increase, but this isn't harmful since the number of pages is so small.

As a general rule, I'd make sure that the indexes you're attempting to defrag are least about 100 pages. The Microsoft recommendation is above 1000 pages.

Since you're using the Maintenance Wizard, I don't think you can filter for page size. But just don't worry about it for the small indexes.
more ▼

answered Jan 11, 2012 at 08:50 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
101k 19 21 74

Thanks Grant. :)
Jan 11, 2012 at 09:06 AM Zahid
(comments are locked)
10|1200 characters needed characters left
I am not agree with Microsoft Premium Engineers that maintenance plan task can not completely does this de-fragmentation because I also implemented this on so many database and it works fine. Only valid reason of index not de-fragmentation is given by Grant & Kev.
more ▼

answered Jan 11, 2012 at 11:11 PM

Amardeep gravatar image

Amardeep
1.3k 87 88 89

(comments are locked)
10|1200 characters needed characters left
Is this bug hitting you: http://support.microsoft.com/kb/2292737 ?
more ▼

answered Jan 12, 2012 at 12:20 AM

Wilfred van Dijk gravatar image

Wilfred van Dijk
1.3k 19 23 30

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x131
x20

asked: Jan 11, 2012 at 07:56 AM

Seen: 1262 times

Last Updated: Jan 11, 2012 at 08:51 AM