執行sp_delete_backuphistory刪除備份紀錄時，效能不佳且造成CPU飆高
緣由 :
日前觀察到某些SQL的msdb有點大(約莫400MB上下)，因此檢查了一下是哪一些Table佔據了空間。
發現大多是跟紀錄SQL Backup有關的資料表，因此想說該清理一下這一些備份紀錄。然而當我執行
了該預存程序sp_delete_backuphistory後(清理2007-2010約莫12000筆紀錄)，該Session跑了約13分鐘
還跑不完而且還Height Cpu，當時該Session並沒有被Block也沒有Wait的事件。
因此我先將該預存程序停止讓CPU降下來，此時用sp_lock去看，發現該Session還lock很多資源，因
此判斷這樣停掉預存程序但其實交易並沒結束，因此當我直接關閉該Session查詢視窗時，SSMS彈跳
出一個訊息(如下圖所示)
看到上圖這樣的訊息，我當下傻了。我到底要按是還是否呢?緊急詢問楊老師，老師說該預存程序有
啟動交易，既然被我中止則需Rollback，因此選擇否。當我關掉該Session視窗後，剛剛被Lock的資源就
都釋放掉了，接下來我思索難道就先放著不刪嗎?會不會又發生同樣的問題呢?
後續處理 :
我想早晚都要面對，一定要找出為什麼。既然剛剛CPU飆那麼高，就去看一下Performance Dashboard有
沒有蛛絲馬跡。此時我們看一下耗用CPU資源報表可以看見如下圖所示，該Query只執行一次但該次使用
了407170.901 ms，也就是將近7分鐘的時間。
看了一下該Query的執行計劃時，突然發現SQL有建議建立一個missing index(如下圖)，此時讓我想到剛
剛在求教楊老師時，老師有提到以前他也有過執行sp_delete_backuphistory結果咬死，後來是建立index後
才解決。
下圖是SQL建議的missing index
為了確保建立該Index後對於執行sp_delete_backuphistory是有效果的，我先將該SQL的msdb備份還原到另
一台測試SQL上，然後執行一次sp_delete_backuphistory，發現大約3分多鐘可以跑完(測試機是SQL2012而
正式機是SQL2005，看來新版本的SQL效能上果然好很多)，在我建完Index後再執行一次sp_delete_backuphistory。
大概5秒內完成，因此可以斷定建立該Index是有很大的幫助。
因此我在正式機上建立該Index後，同時在執行sp_delete_backuphistory時也採用一次不刪太多資料為原則
(一次刪半年資料)，最後順利清掉大部分的(6年)備份紀錄。最後附上當時的CPU及Page Lookups的監控圖表，
Page Lookups會那麼高應該是因為要大量多次的Scan某張Table吧。
結論 :
定期刪除備份紀錄，如果很久才做一次的話建議以少量多次為原則。一次刪除大量有可能會造成效能問題外，
也會因此讓交易紀錄檔大幅成長。建立需要的Index會有助於提升該預存程序作業效能。
我是ROCK
rockchang@mails.fju.edu.tw
