Rebuild：An index ‘rebuild’ creates a fresh, sparkling new structure for the index.
If the index is disabled, rebuilding brings it back to life.
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.
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.
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)*
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:
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 可以online或offline執行，而Reorganize Index則一律是online執行。
在剛建立時會有短暫的share lock與intent-shared table lock，中間過程Table都是可以正常使用，直到最後結束時跟offline一樣會啟動Sch-M Lock來短暫封鎖所有外來的行為
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
SQL-Server–使用另一個Windows-Account操作SSMS-錯誤訊息--請輸入用於cprogram-filesql-server-management-studio-18common7idessmsexe" title="SQL Server–使用另一個Windows Account操作SSMS, 錯誤訊息 : 請輸入用於c:\program file\sql server management studio 18\common7\ide\ssms.exe