原文主旨：SQL Server Index Maintenance Performance Tuning for Large Tables
索引在資料庫上的存在已非常的久，一直以來也沒有什麼特別的部份可以再行說明，但是索引的維護卻是非常的重要，儘管大家已對索引的維護已感到麻痺，但是你還是需要非常的小心的進行索引的維護，而且是有效率的的，而這個工作無法盲目的經由書上所寫程序，並經由SQL Server自動的進行維護。
在不同的環境下使用不同的SQL Server，索引的維護可能會都混在一起，如何有效的指定索引的效能在不同的環境中，而在完全不同的查詢執行下，也可以保持一個良好的索引維護與效能是很重要的。
但是如何作到最好，一直有很多大的爭議。什麼是最好呢？根據官方建議當索引的分裂情況在5% ~ 30%就需要重組。當超過30%的時候就最好是重建。詳細的資訊，可以參考此鏈結。
到目前為止，依照建議值一切都會表現的很好，但是針對每一個不同的查詢與不同的環境是否應該會有一些不同的對方法呢？這是一個很真實的情況，但當你實際的了解這些數據後你就會更清楚
。在作者的環境中有一個表格，作者每日會新增1千到2千萬的資料到此資料表中，每天晚上作者會執行一個維護的Procedure將刪除大於n天的舊資料(約幾百萬筆)。所以約每天增加3-5百萬筆到資料表中。目前此資料表已有包含9億7千5佰萬筆右右。
在索引碎片程度的判斷上從SQL Server 2005以後就變得的非常的容易，你可以很簡單的使用動態管理函式來進行即可(sys.dm_db_index_physical_stats)。下列是每天執行時一部份的語法：
SELECT
OBJECT_SCHEMA_NAME(FRAG.[object_id]) + '.' + OBJECT_NAME(FRAG.[object_id]),
SIX.[name],
FRAG.avg_fragmentation_in_percent,
FRAG.page_count
FROM
sys.dm_db_index_physical_stats
(
DB_ID(), --use the currently connected database
0, --Parameter for object_id.
DEFAULT, --Parameter for index_id.
0, --Parameter for partition_number.
DEFAULT --Scanning mode. Default to "LIMITED", which is good enough
) FRAG
JOIN
sys.indexes SIX ON FRAG.[object_id] = SIX.[object_id] AND FRAG.index_id = SIX.index_id
WHERE
--don't bother with heaps, if we have these anyway outside staging tables.
FRAG.index_type_desc <> 'HEAP' AND
(
--Either consider only those indexes that need treatment
(FRAG.page_count > @IPageCnt AND FRAG.avg_fragmentation_in_percent > @IMinFragmentation)
OR
--or do everything when it is MaintenanceDay
@IsMaintenanceDay = 1
)
ORDER BY
FRAG.avg_fragmentation_in_percent DESC;傳回的資料列將新增至一個暫存的表格，並且確認是否有啟動一個維護視窗進行操作。
在Where的過濾條件中我會過濾多個情況：
- @IPageCnt: 預設值是128。任何值低於此門檻值時將會被忽略。這個好處應該很清楚，不需再特別解譯吧。
- @IMinFragmentation: 預設值是10%。任何值低於10%會被審略。
- @IsMaintenanceDay: 每週一次進行維護，其中作者可以維護全部的索引。致少作者是這麼認為的。
索引碎片等級的情況將會被回傳，作者將會進行重組或重建索引。如同上述中原廠的建議值，這一切都是完美的，只到一個星期一的早上作者走進辦公室時，剛好發現作者的索引排程已執行超過5個小時而且備份排程也卡住了。
下列是索引重整與重建的指令：
- ALTER INDEX…REORGANIZE 這是一個單一執行緒。意思上，無論有多少個CPU在你的機器上，這個操作只會有一個進行。所以當你進行一個索引的維護在一個大型資料表時，你就得耐心的等待了。
- ALTER INDEX…REBUILD 可能使用平行化，因此可以運行的更快，但是會使用較多的資源。
更多的說明，可能參考: Configuring Parallel Index Operations
所以，很清楚的不需在額外的研究確認與解譯為何作者的索引維護會要這麼久，當在大型資料表的索引進行重組的時候。
無論如何，這是一個相當驚人的發現，因為許多重要的執行皆透過此方式如sys.dm_db_index_physical_stats自已本身。雖然我們已經知道了這個事實，但是在有限的模式下，還是不得不掃描葉的層級，我簡單的預期這個不會非常的久，在我的案例中，在函式傳回結果之前，他將花費約30分鐘，而一半的時候是放在我們的日常維護的視窗中。大部份30分鐘的時間是在處理一個大型資料庫的碎片。而其他的表格只需要3-4分鐘的時間，這是一個可以接受的結果。
一旦我們發現了這一點，下一步就是試圖找到一種方法，使sys.dm_db_index_physical_stats運行得更快。一個比較直接的想法，就是將大型的資料表從
sys.dm_db_index_physical_stats中濾除。為了避免類似的情況發生在維設程序中，作者決定建立一個表格去儲存表格與索引最後的維護時間。這個表格可以透過 WHERE NOT EXISTS的方式加入 sys.dm_db_index_physical_stats 濾除的條件中。
無論如何，這個動作將無法圓滿的成功。執行的時候無法如預期般的下降。相關的原因與行為你可以參考Paul Randal Blog中的：inside sys.dm_db_index_physical_stats。因為DMF不支援 "predicate push-down" 的模式. 意思上就是代表WHERE NOT EXISTS將不會被處理到，因為 WHERE 會在最後的時候才會被執行，所以還是沒有真正的執行過濾。
PS:關於 "predicate push-down" 的說明，可以參考我的另一篇翻譯文章：維護記錄檔與索引的技巧。
大多數的dynamic management views都有支援 "predicate push-down"，只有當資料處理時才有相對應在WHERE的條件上。無論如何，sys.dm_db_index_physical_stats 是一個函數，不是一個檢示，所以他無法這樣運作。這意謂著你必須手動過濾出你所需要的索引進行重組或重建的動作。
對於這種無效的操作，作者另外想出了另一個高招：
SELECT
OBJECT_SCHEMA_NAME(FRAG.[object_id]) + '.' + OBJECT_NAME(FRAG.[object_id]),
SIX.[name],
FRAG.avg_fragmentation_in_percent,
FRAG.page_count
FROM
sys.indexes SIX
CROSS APPLY
sys.dm_db_index_physical_stats
(
DB_ID(), --use the currently connected database
SIX.object_id, --Parameter for object_id.
SIX.index_id, --Parameter for index_id.
0, --Parameter for partition_number.
DEFAULT --Scanning mode. Default to "LIMITED", which is good enough
) FRAG
WHERE
SIX.object_id <> OBJECT_ID('...big Table...')
ORDER BY
FRAG.avg_fragmentation_in_percent DESC;動機上透過 sys.indexes在 WHERE 條件中將有效的濾除大型資料表，而且同一時間可以配合DMF的object_id和index_id從sys.indexes之中。不幸的事，這個想法在第一次執行時就破滅了：
Msg 413, Level 16, State 1, Line 1
Correlated parameters or sub-queries are not supported by the inline function“sys.dm_db_index_physical_stats”.
Msg 413, Level 16, State 1, Line 1
Correlated parameters or sub-queries are not supported by the inline function“sys.dm_db_index_physical_stats”.
針對目前的情況，我們已經沒有任何的想法，所以決定來進行一些混合的方式，在此作者使用一個表格儲存資料庫中所有的索引的object_id、index_id、page_count和最後維護的時間。
對於資料表小於1,000,000的頁面時，可以考慮透過這個新表格去呼叫 sys.dm_db_index_physical_stats 與 object_id 和 index_id 取得從表格參數和是否進行重建索引，依照索引碎列的程度。如果這些的處理上仍然有足夠的時間在維護視窗中，我將重新取得另一列從表格去啟動另一個操作或存在的處理，藉以達到平行化處理。
對於表格超過1,000,000的頁面時，作者將重建一個單一的索引在各自的維護日，針對這些索引我沒有明確的決定索引碎片等級，而是馬上重建他。為了確認索引碎裂的程度，作者每隔三個禮拜進行一次觀察，並且確認是足夠的達到預期的碎裂程度。
作者同樣的決定透過重建取代重組的動作。這可能會需要花費較多的時間，但是選擇上這種方式可以取得一個平行的流程，因此減少執行時間對我們來說是有說服力的。無論如何，去避免引入新的瓶頸經由使用所有可用的處理在索引的操作上，在預先處理我會先決定處理的數量，並只有使用一半的Alter Index的語句。這仍然留下足夠的資源可以去進行其他的索引處理。到目前為止，這個策略的制定到目前為止都是非常的好，但是你不會明白，在一個10億行會發生什麼樣的變化。
下列是一些連結的參考。
原文網址：http://www.sql-server-performance.com/2011/index-maintenance-performance/
相關文章：
維護記錄檔與索引的技巧
http://caryhsu.blogspot.com/2011/05/blog-post.html
SQL Server 上大型資料表的索引效能調整與維護
http://caryhsu.blogspot.com/2011/08/sql-server.html
效能調整 - 填滿因數對資料庫的影響
http://caryhsu.blogspot.com/2012/02/blog-post.html
索引分析與維護建議
http://caryhsu.blogspot.com/2012/02/blog-post_14.html
關鍵字：SQL Server、Index Maintenance、Performance Tuning、Large Tables
沒有留言:
張貼留言