SQL Server 2008 R2 - Reorganize Index taking too long

[SGoncalves]

Greetings everyone,

I'm administering a SQL Server 2008 R2 and I have a daily maintenance
plan that backs up a database and then reorganizes it's indexes and
updates statistics. Lately the reorganize index task is taking over 8
hours to complete.

This is not a normal behavior since the rebuild indexes task I run on
weekends takes less then an hour.
The database is almost 60 GB in size.

How can I fix this?

Best regards.

[Erland Sommarskog]

SGoncalves (goncalve...@gmail.com) writes:
> I'm administering a SQL Server 2008 R2 and I have a daily maintenance
> plan that backs up a database and then reorganizes it's indexes and
> updates statistics. Lately the reorganize index task is taking over 8
> hours to complete.
>
> This is not a normal behavior since the rebuild indexes task I run on
> weekends takes less then an hour.
> The database is almost 60 GB in size.

Stop reorganising the indexes. Monitor your fragmentation, to see whether
any action is needed at all.

Reorganisation uses a kind of bubble-sort algorithm, and in some situation
it can take some time to handle a fairly small amount of fragmentation.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[SGoncalves]

On May 6, 11:44 am, Erland Sommarskog <esq...@sommarskog.se> wrote:

> SGoncalves (goncalves.serg...@gmail.com) writes:
> > I'm administering a SQL Server 2008 R2 and I have a daily maintenance
> > plan that backs up a database and then reorganizes it's indexes and
> > updates statistics. Lately the reorganize index task is taking over 8
> > hours to complete.
>
> > This is not a normal behavior since the rebuild indexes task I run on
> > weekends takes less then an hour.
> > The database is almost 60 GB in size.
>
> Stop reorganising the indexes. Monitor your fragmentation, to see whether
> any action is needed at all.
>
> Reorganisation uses a kind of bubble-sort algorithm, and in some situation
> it can take some time to handle a fairly small amount of fragmentation.
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>

> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...

> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Thank you for your answer.

I have monitored the fragmentation and right now it is very high on
most of the indexes.

I've scheduled a rebuild for this weekend.

Thank you for your time.
Best regards.

[Gene Wirchenko]

On Fri, 6 May 2011 10:44:32 +0000 (UTC), Erland Sommarskog
<esq...@sommarskog.se> wrote:

[snip]

>Reorganisation uses a kind of bubble-sort algorithm, and in some situation
>it can take some time to handle a fairly small amount of fragmentation.

Bubble sort? Why?

Sincerely,

Gene Wirchenko

[Erland Sommarskog]

It may not be exactly bubble-sort. But what happens is that SQL Server
swaps place of two pages at a time. The advantage with this is that
REORGANIZE can be an online operation. But it may not always be the
most efficient.

REBUILD means a complete rebuild of the index, but this means that the
table is unaccessible while the rebuild is running. (Although, in
Enterprise Edition it is possible to do online rebuilds.)

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

[SGoncalves]

Greetings,

I've been checking fragmentation levels in the production database,
and I've encountered some curious (or not).
When I select the sys.dm_db_index_physical_stats I have many indexes
with 99% avg_fragmentation_in_percent, but when I go through the GUI
into the same indexes and check the fragmentation it show 0,01%.

How is this possible?

Best regards.

[Erland Sommarskog]

SGoncalves (goncalve...@gmail.com) writes:
> I've been checking fragmentation levels in the production database,
> and I've encountered some curious (or not).
> When I select the sys.dm_db_index_physical_stats I have many indexes
> with 99% avg_fragmentation_in_percent, but when I go through the GUI
> into the same indexes and check the fragmentation it show 0,01%.

Not sure what you see, but beware that if you run with DETAILED mode,
you will also get the intermediate levels in the index, and they are often
quite fragmented. On the other hand that is no cause for alarm.

[SGoncalves]

On May 9, 10:59 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:

How can I check the DETAILED mode?

Thanks in advance.

[Henk van den Berg]

SGoncalves schreef op 10-05-2011 12:44:

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

[Henk van den Berg]

Henk van den Berg schreef op 10-05-2011 12:51:

Oops, inadvertently hit ctrl-enter

I was gonna add:

mode | NULL | DEFAULT

Is the name of the mode. mode specifies the scan level that is used
to obtain statistics. mode is sysname. Valid inputs are DEFAULT, NULL,
LIMITED, SAMPLED, or DETAILED. The default (NULL) is LIMITED.

[Erland Sommarskog]

SGoncalves (goncalve...@gmail.com) writes:
> How can I check the DETAILED mode?

Well, if you don't know what it is, you cannot be fooled about it.

But else, sys.dm_db_index_physical_stats is described in Books Online.
When you see something you have not seen before, it's often a better
alternative to look in Books Online than posting a question and wait
for someone to answer.

[SGoncalves]

On May 10, 10:36 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:

OK.

Thank you very much.

Best regards.

P.S.: I'm still working on the database, but performance has been
greatly improved in the last few days.