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.

SGoncalves (goncalves.sergiom (AT) gmail (DOT) com) writes:
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, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

On May 6, 11:44*am, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:
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.

On Fri, 6 May 2011 10:44:32 +0000 (UTC), Erland Sommarskog
<esquel (AT) sommarskog (DOT) se> wrote:

[snip]

Bubble sort? Why?

Sincerely,

Gene Wirchenko

Gene Wirchenko (genew (AT) ocis (DOT) net) writes:
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, esquel (AT) sommarskog (DOT) 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

On May 6, 10:49*pm, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:
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.

SGoncalves (goncalves.sergiom (AT) gmail (DOT) com) writes:
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.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) 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

On May 9, 10:59*pm, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:
How can I check the DETAILED mode?

Thanks in advance.

SGoncalves schreef op 10-05-2011 12:44:
http://msdn.microsoft.com/en-us/library/ms188917.aspx

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

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.