Total Pageviews

Thursday, July 4, 2013

Index optimization - REBUILD vs. REORGANIZE

Index
optimization is probably one of the most critical task every database support
personnel has to perform on a regular basis. Based on DML operations in a
particular environment, we adopt various optimization tasks, steps and strategies
that suit our needs. Some tables or indexes may need frequent optimization,
some do not need it at all for a longer period of time.

To
optimize an index we have two options, REBUILD and REORGANIZE. Both work
differently and have different effects. There are some differences which we
should be aware of for better understanding of how each T-SQL command works and what does it do for us.

Good to Know some key
points:

1.When a non-clustered index is rebuilt, only the associate statistics for
this index will be rebuilt.

2.Rebuilding a clustered index does not rebuild associated non-clustered
indexes unless the keyword ALL is specified.

3.“HEAP” cannot be optimized. If “ALL” is specified and the underlying table
is a heap, the rebuild operation has no effect on the table. Any non-clustered
indexes associated with the table are rebuilt.

4.The rebuild
operation can be minimally logged if the database recovery model is set to
either bulk-logged or simple.

5.The options ONLINE = ON and IGNORE_DUP_KEY = ON are not valid while
rebuilding an XML index or a spatial index.

7.If index options are not specified, the existing index option values
stored in sys.indexes
will be used.

8.ALTER INDEX cannot be used to repartition an index or move it to a
different filegroup. This statement cannot be used to modify the index
definition, such as adding or deleting columns or changing the column order.

9.The values for ONLINE, MAXDOP, and SORT_IN_TEMPDB are not stored in the
system catalog. You need to specify this OPTION in the index rebuild statement.

10.Reorganizing an index is
always performed online. The process does not hold locks long term hence it
does not block queries or updates that are running.

11.When you execute ALTER INDEX ALL … on a table, only the statistics associated
with indexes are updated. Automatic or manual statistics created on the table will
not be updated.

12.Index REBUILD can be a
Parallel operation. Index REORGANIZE is always serial operation.

13.Rebuilding or reorganizing
small indexes (which has 128 or less extents) often does not reduce
fragmentation.

14.Reorganizing an index uses
minimal system resources and also compacts the index pages.

15.Reorganizing an index does
not update associate statistics.

Index Optimization Guideline:

The
guideline that Microsoft has provided in the MSDN is a general guideline regardless
of any DML operations happening in the database which need to be further reviewed
by the database administrator based on his/her workload scenario to establish a
better threshold.

The
sys.dm_db_index_physical_stats
can be used to determine fragmentation levelsin a specific index, in all indexes on a table or
indexed view, in all indexes in a database, or in all indexes in all databases.
The following table explains three important columns of the system function which
need to be researched closely:

Column

Description

avg_fragmentation_in_percent

The
percent of logical fragmentation (out-of-order pages in the index).

fragment_count

The
number of fragments (physically consecutive leaf pages) in the index.

avg_fragment_size_in_pages

Average
number of pages in one fragment in an index.

Action
threshold recommended by Microsoft.

avg_fragmentation_in_percent

T-SQL Command

>
5% and < = 30%

ALTER
INDEX REORGANIZE

>
30%

ALTER
INDEX REBUILD (All Edition)

ALTER
INDEX REBUILD WITH (ONLINE = ON) (Enterprise Edition)

Number
of Extents > 128

Will
be a good candidate for index optimization

The
above threshold is a recommendation only. As every environment is different
therefore it is a good idea to research the best threshold that will suit your
need.

DMV Query:

The
following DMV query can be used to pull detail information about indexes.

SQL Performance Monitor V3.8 (Updated 20 April 2018)

Search This Blog

About Me

For the past 10+ years, I have been working as a database administrator with Microsoft’s SQL Server technology closely where database performance, high availability and load balancing are the main focuses.
-------------------------------------I had the opportunity to work for Microsoft SQL Server PSS Team (Database Engine Team) and was able to assist numerous SQL Server implementations to improve day-to-day critical application performance challenges. I have experienced in planning, designing, developing, data migrating, technical support, and troubleshooting of issues ranging from small to high end OLTP applications.
------------------
--------------------------------------------IT Certification so far:
• MCSD, MCDBA, MCSE in 2000
• MCTS, SCSA, OCA in 2006
• MCITP SQL Server 2008 in 2011