Rebuild：An index ‘rebuild’ creates a fresh, sparkling new structure for the index.

If the index is disabled, rebuilding brings it back to life.

You can apply a new fillfactor when you rebuild an index.

If you cancel a rebuild operation midway, it must roll back (and if it’s being done offline, that can take a while).

It is an online operation in Enterprise editions, offline in other editions, and uses as much extra working space again as the index size.

It creates a new copy of the index and then drops the old one, thus getting rid of fragmentation. It means that you will get a completely new index, with defragmented and contiguous pages.

Statistics are recomputed by default as part of this operation, but that can be disabled.

Reorganize: This option is more lightweight, using little extra working space. It runs through the leaf level of the index, and as it goes it fixes physical ordering of pages and also compacts pages to apply any previously set fillfactor settings.

it’s a defrag for indexes. Takes the existing index(es) and defragments the existing pages.

However if the pages are not in a contiguous manner, they stays like before. Only the content of the pages are changing.

This operation is always online, and if you cancel it then it’s able to just stop where it is (it doesn’t have a giant operation to rollback).

以下是Microsoft Reorganizing Index的一段描述

“Reorganizing an index defragments the leaf level of clustered and nonclustered indexes on tables and views by physically reordering the leaf-level pages to match the logical order (left to right) of the leaf nodes. Having the pages in order improves index-scanning performance. The index is reorganized within the existing pages allocated to it; no new pages are allocated.”

其中粗體字表示是重新排序intermediate level nodes point,而非所有Page內資料的排序:

While ReOrg will re-order the pages, it ONLY reorders them at the lowest level that intermediate level nodes point to them. After a ReOrg all pages across the entire Index are not guaranteed to be contiguous.

以下這段話有待商榷

Here’s a better explanation: dba.stackexchange.com/a/36817/6816 –

When doing a reorg of an index, if the index is spread across two or more physical files the data will only be defragged within the data file. Pages are not moved from one data file to another.

When the index is in a single file the reorg and reindex will have the same end result.

Some times the reorg will be faster, and some times the reindex will be faster depending on how fragmented the index is. The less fragmented the index then a reorg will be faster, the more fragmented the slower the reorg will be, but the faster a reindex will be.

Limitations and Restrictions

• Indexes with more than 128 extents are rebuilt in two separate phases: logical and physical. In the logical phase, the existing allocation units used by the index are marked for deallocation, the data rows are copied and sorted, then moved to new allocation units created to store the rebuilt index. In the physical phase, the allocation units previously marked for deallocation are physically dropped in short transactions that happen in the background, and do not require many locks.
• Index options cannot be specified when reorganizing an index.
• The ALTER INDEX REORGANIZE statement requires the data file containing the index to have space available, because the operation can only allocate temporary work pages on the same file, not another file within the filegroup. So although the filegroup might have free pages available, the user can still encounter error 1105: “Could not allocate space for object . in database because the ‘PRIMARY’ filegroup is full.”
• Creating and rebuilding nonaligned indexes on a table with more than 1,000 partitions is possible, but is not supported. Doing so may cause degraded performance or excessive memory consumption during these operations.

Reorganize index or Rebuild Index?

How much downtime can you spare?

Before you implement index maintenance, find out how much time tables can be offline in each of your databases. Then, figure out what operations you want to use.

Factors to consider:

• Standard Edition rebuilds ain’t awesome. If you’ve got SQL Server Standard Edition, index rebuilds are always an offline operation. Bad news: they’re also single-threaded. (Ouch!)
• Enterprise Edition rebuilds have gotchas. With SQL Server Enterprise Edition, you can specify an online rebuild — unless the index contains large object types. (This restriction is relaxed somewhat in SQL Server 2012). You can also use parallelism when creating or rebuilding an index — and that can save a whole lot of time. Even with an online rebuild, a schema modification lock (SCH-M) is needed at the time the fresh new index is put in place. This is an exclusive lock and in highly concurrent environments, getting it can be a big (blocking) problem.
• There’s a bug in SQL Server 2012 Enterprise Edition Rebuilds that can cause corruption. If you’re running SQL Server 2012 SP1 — SP2, parallel online index rebuilds can cause corruption. Read about your options here.
• Rebuilding partitioned tables is especially tricky. You can rebuild an entire partitioned index online– but nobody really wants to do that because they’re huge! The whole idea behind horizontal partitioning is to break data into more manageable chunks, right? Unfortunately, partition level rebuilds are offline until SQL Server 2014.
• Reorganizing can be pretty cool. ‘Reorganizing’ an index is always an online op, no matter what edition of SQL Server you’re using. It doesn’t require a schema mod lock, so it can provide better concurrency. Reorganizing only defragments the leaf level of the index. On large tables it can take longer than a rebuild would take, too. But as I said above, it’s nice that you can reorganize for a while and then stop without facing a massive rollback.

You Didn’t Answer the Question: Do I Use Rebuild or Reorganize?

Yeah, I totally dodged that question, didn’t I?

If you have a regularly scheduled downtime every weekend, you’re probably fine with straight up index rebuilds, even if you have Standard Edition. Single threaded offline index maintenance may not be the hottest thing in the world, but hey, if you’ve got time for it then embrace the simplicity.

If you have Enterprise Edition, embrace parallel index rebuilds– and use the ONLINE option for indexes that allow it if people need to access the database during your maintenance window.

If you have database mirroring or AlwaysOn Availability Groups, tread with caution– particularly with rebuilds. It’s easy to generate a ton of IO with index maintenance, and it could mean putting your secondaries or mirror so far behind that they can’t catch up.

其他的策略:

Index reorganization is a process where the SQL Server goes through the existing index and cleans it up. Index rebuild is a heavy-duty process where an index is deleted and then recreated from scratch with an entirely new structure, free from all piled up fragments and empty-space pages.

While index reorganization is a pure cleanup operation that leaves the system state as it is without locking-out affected tables and views, the rebuild process locks the affected table for the whole rebuild period, which may result in long down-times that could not be acceptable in some environments.

With this in mind, it is clear that the index rebuild is a process with a ‘stronger’ solution, but it comes with a price — possible long locks on affected indexed tables.

On the other side, index reorganization is a ‘lightweight’ process that will solve the fragmentation in a less effective way — since cleaned index will always be second to the new one fully made from scratch. But reorganizing index is much better from the efficiency standpoint since it does not lock the affected indexed table during the course of operation.

Servers with regular maintenance periods (e.g. regular maintenance over weekend) should almost always opt for the index rebuild, regardless of the fragmentation percent, since these environments will hardly be affected by the table lock-outs imposed by index rebuilds due to regular and long maintenance periods.

不考慮太多的策略:

avg_fragmentation_in_percent valueCorrective statement> 5% and < = 30%ALTER INDEX REORGANIZE> 30%ALTER INDEX REBUILD WITH (ONLINE = ON)*

Maintenance plans or custom scripts?

You can go the easy way and use SQL Server Maintenance Plans, but unfortunately they’re very simplistic: you can only say “rebuild all the indexes” or “reorganize all the indexes”. You cannot say, “If the index is 45% or more fragmented, rebuild it– otherwise do nothing.” If you don’t spend much time with SQL Server and you’ve got downtime available every weekend, this can be a decent option.

If you need to minimize downtime, custom index maintenance scripts are the way to go. Our favorite: Ola Hallengren’s maintenance scripts. These are super flexible, well documented, and … free! The scripts have all sorts of cool options like time boxing and statistics maintenance.

Some tips for using Ola Hallengren’s index maintenance scripts:

1. Download and configure them on a test instance first. There’s a lot of options on parameters, and you’ll need to play with them.
2. Get used the ‘cmdexec’ job step types. When you install the scripts you’ll see that the SQL Server Agent jobs run index maintenance using a call to sqlcmd.exe in an MSDOS style step. That’s by design!
3. Use the examples on the website. If you scroll to the bottom of the index maintenance page you’ll find all sorts of examples showing how to get the procedure to do different useful things.

Find out when maintenance fails

Don’t forget to make sure that your maintenance jobs are successfully logging their progress. Set up Database Mail and operators so jobs let you know if they fail.

— — — — — — — — — — — — — — — — — ------------------------------

總結

空間需求:

Rebuild Index:

1. 額外需要一個可以容納新Index的空間 + Log增長的空間
2. 不管現在的Index破碎率多高，它都會先建立一個新的Index，建完後再把舊的Index drop掉， 所以在Rebuild Index前必須確保現在的disk有足夠的空間容納新的Index大小

Reorganize Index:

1. 額外需要一個暫時的8K Page的空間 + Log增長的空間
2. 會先將Index Page都壓縮在一起，釋放用不到的頁面，其他的都會進行重排，讓它的順序都排對，相對起來空間需求小

速度：

Rebuild Index:

1. 因為Rebuild Index都是直接重建新的，故重建所需的時間會跟Index大小與table大小有關

Reorganize Index:

1. 因為Reorganize Index是處理碎片，故如果破碎率越高，碎片越多，重組時間就越長。所以微軟建議如果Index的破碎率小於30%則使用Reorganize Index

Lock：

Rebuild Index 可以online或offline執行，而Reorganize Index則一律是online執行。

以Rebuild Index來說通常我們使用Online模式建，這是為了避免影響線上的DB，而這兩種模式的差別為(詳細說明)：

Offline：

會讓這個Table在執行Index的開始到結束都會有Sch-M的鎖來Lock Table，導致整個Table無法更新與讀取

Online：(online執行的運作說明、鎖定模式說明)

在剛建立時會有短暫的share lock與intent-shared table lock，中間過程Table都是可以正常使用，直到最後結束時跟offline一樣會啟動Sch-M Lock來短暫封鎖所有外來的行為

是否可中斷：

Rebuild Index:

不可被中斷，所以一但開始執行則無法rollback，只能等他做完

Reorganize Index:

可以被中斷，一但被中斷會rollback回去，但須考量rollback成本

如果空間不夠，我們只能選擇Reorganize Index來做；如果空間夠，可以參考破碎率來選擇要用哪一種方式（如下圖），但是有些時候反而是需要依據當時情況來選擇（其他案例詳細說明）

參考來源:

Why, when and how to rebuild and reorganize SQL Server indexes
The purpose of the SQL Server index is pretty much the same as in its distant relative - the book index - it allows you…solutioncenter.apexsql.comRebuild or Reorganize: Configure SQL Server Index Maintenance
Editors Note: this post was updated in June 2014 to link to an online index rebuild bug that can cause corruption. Once…www.brentozar.comWhat are the differences between leaf and non-leaf pages?
Thanks for contributing an answer to Database Administrators Stack Exchange! Please be sure to answer the question…dba.stackexchange.comDetecting and resolving fragmented indexes - SQL Server
Applies to: SQL Server (all supported versions) Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics…docs.microsoft.comIs It possible to a rebuild Index with out taking instance offline?
Thanks for contributing an answer to Stack Overflow! Please be sure to answer the question. Provide details and share…stackoverflow.comhttps://solutioncenter.apexsql.com/online-vs-offline-sql-server-index-rebuild-in-sql-server/

Reorganise index vs Rebuild Index in Sql Server Maintenance plan
I researched on web and found some of good articles. At the and i wrote the function and script below which is…stackoverflow.comWhat are the differences between leaf and non-leaf pages?
Thanks for contributing an answer to Database Administrators Stack Exchange! Please be sure to answer the question…dba.stackexchange.comReorganize and Rebuild Indexes in the Database
This topic describes how to reorganize or rebuild a fragmented index in SQL Server 2017 by using SQL Server Management…www.beyondtrust.comhttps://dotblogs.com.tw/eva/2018/01/17/170505

• Tags:
• SQL Server

• ← 索引碎片99%怎麼回事?與Page Split有關係嗎? Internal Fragment vs Internal Fragment ? - SQL Server
• Practice 實踐經驗的重要 - 自我成長 →