1. 索引重建和重組有什麼用？ 當修改表(UPDATE、INSERT、DELETE等)中數據，資料庫引擎自動維護索引的數據和結構。但是隨著修改次數的累積，可能會現： 索引中記錄的數據順序(邏輯順序)和數據的實際順序不一致(物理順序)，這也稱之為 外部碎片 。 索引頁的數據填充度變小(頁密度)，也稱之 ...
1. 索引重建和重組有什麼用？
當修改表(UPDATE、INSERT、DELETE等)中數據，資料庫引擎自動維護索引的數據和結構。但是隨著修改次數的累積，可能會現：
- 索引中記錄的數據順序(邏輯順序)和數據的實際順序不一致(物理順序)，這也稱之為外部碎片。
- 索引頁的數據填充度變小(頁密度)，也稱之為內部碎片。
有索引碎片是正常的，但是有大量的碎片，會降低查詢性能，可以通過重建和重組索引來減少或消除碎片。
2. 索引重建和重組有什麼區別？
- 重建是刪除索引並重新創建。通過這種方式移除碎片、回收磁碟空間(根據現有的或指定的填充因數壓縮(Compact)頁數據)、對相鄰頁中的索引進行重新排列。重組索引使用的系統資源最少。它在葉級層從左至右，重新排列葉級頁使之於索引的邏輯順序一致。同時也會對頁按填充因數進行壓縮。由此可知重建對於消除碎片和空間回收上的程度更高。
- 重建索引是單個事務，如果指定了ALL關鍵字，則所有的索引重建做為一個事務。重組索引(包括指定了ALL)，在內部會分解為多個較小的事務執行。重建事務回滾，需要回滾所有已經發生的修改。重組可以在任意時間點停止並且只回滾當前的某個較小的事務，已經發生的修改不會回滾(這個有點像DBCC SHRINKFILE)。
- 重組只能在ONLINE模式下，重建可以指定為ONLINE或者OFFLINE。
3. 索引重建時的ONLINE和OFFLINE選項是什麼意思？
顧名思義，表示重建索引的模式。
- OFFLINE時，會在表上獲取Sch-M鎖來阻止所有用戶的訪問，然後將舊索引的數據複製到新索引中,完成重建後才會釋放表鎖。
- ONLINE時，也是複製舊索引數據到新索引中，同時舊索引是可以讀寫的。重建過程中舊索引的修改操作同時會被應用到新索中，還有一個中間數據結構實現新舊數據的映射和修改衝突。在重建完成後，會使用Sch-M鎖定表非常短的時間，然後使用新索引替代舊索引，並釋放Sch-M。詳情參考：How Online Index Operations Work
- 本地臨時表的索引不能使用ONLINE模式。
- 相對來說，ONLINE要比OFFLINE使用更多的資源，但提供併發支持。
4. 在重組(或重建)大表的索引時，日誌文件變得很大，怎麼辦？
說明一下，小表的索引整理問題沒有太多意義。
資料庫的所有有損操作都需要記錄到日誌，這個跟哪種恢復模式沒有關係。也就是說從資料庫的角度來看，這些日誌都是它必須要寫的。我們要做的是：引導它少寫點日誌和提高寫日誌的性能。下麵是一些考慮點：
最重要考慮點：我整理索引的目的是什麼？消除碎片，回收空間，遷移數據等等？只有重建/重組索引才能達到我的目的嗎？
- 我們知道重組始終是ONLINE模式，它提供了併發支持，卻會使用更多資源。這些資源中就包括日誌。這很好驗證，構建兩個庫，創兩個同樣的表和同樣的索引，分別導入足夠多的會產生碎片的數據，截斷日誌後分別執行重組和重建，你會發現重組產生的日誌量要遠多於重建。
- 重建索引時的ONLINE和OFFLINE的選擇，要結合前一點和實際系統應用情況考慮。我們可以做一些準備工作，比如：重建前先截斷日誌，對日誌文件做一次手動增長來避免自動增長。
- 事務在提交或者回滾後才能被截斷，從前面的問題的，我們也知道重建的事務是原子性的，而重組被分成了多個小事務。也就說，在重建過程中，我們不能截斷它的日誌，而重組時可以截斷。同理，不要在顯式事務中使用ONLINE,這會導致顯式事務提交後，才能截斷日誌。
- 考慮使用 SORT_IN_TEMPDB選項。這個選項使得索引整理的事務日誌寫到tempdb，而不是用戶資料庫。這樣就減少了用戶資料庫事務日誌量，當然tempdb的空間要足夠。如果tempdb位於獨立的磁碟，就可以進一步的減少與用戶資料庫的存儲空間和性能的競爭。
- 如果可能,可以考慮切換到simple和bulk_logged恢復模式，索引的重建和重組可以利用最小化日誌減少日誌量。最小化日誌，它不對每一行數據記錄日誌，而是對頁和區的改變寫日誌。但是它不支持時間點還原。
如果需要預留日誌空間，索引大小的2~3倍會比較安全。
5. 在重建大表的索引時，數據文件也增長到很大了，怎麼辦？
索引重建過程中，舊索引結構和新索引結構是並存的，如果是ONLINE模式下，還有一個中間數據結構存在。如果涉及到數據排序操作，數據排序的臨時數據結構也是需要占用空間的。跟日誌的問題一樣，我們能做的是減弱，不可能杜絕。
- 合理配置MAXDOP選項。在SQL Server 2012/2014/2016 Enterprise上，可以使用多個處理器來執行與索引語句關聯的掃描、排序和索引操作。預設是0，由SQL Server引擎決定並行度。並不是越大越好，要根據系統和負載合理設置。
- 對於臨時的排序空間，它一次只能被一個索引操作使用，所以如果執行多個索引操作，只需要保證臨時排序空間與最大的那個索引一樣大即可。例如重建聚集索引，會同時重建相關的非聚集索引，只需要保證預留的空間與其中最大那個索引一樣大即可。
- 當SORT_IN_TEMPDB=ON時，臨時排序空間則位於tempdb(重建索引的事務日誌也在tempdb)。如=OFF，則排序空間位於當前用戶資料庫中。
- 對於ONLINE模式重建的中間數據結構的位置，由SORT_IN_TEMPDB決定，跟上一點一樣。
- ONLINE操作使用行版本控制，這樣讀取行時不需要S鎖，避免了併發的數據修改事務對索引操作的影響。使用了行版本，對於併發的數據修改操作，在tempdb中存儲相關的行版本數據也需要一些空間。
總結
- 索引整理優化，對tempdb的使用較多，而tempdb本身的配置也是需要優化的。如果可能，將索引和數據分開存儲，於性能和管理也有一定幫助。
- 將平時的一些零散的記錄整理彙總而成，如有疏謬，請輕拍。
