Delete大量資料後SQL Server效能下降

今天給大家分享一個”刪除大量資料後SQL Server效能下降”的案例。一般而言，資料庫資料減少後，應該有助於提高SQL server的整體效能。可是在這個案例中，情況恰恰相反。

[@more@]

症狀

=========

- 刪除大量資料後SQL Server效能下降

- 一些儲存過程之前執行20分鐘左右，現在需要執行2-3個小時。

背景資訊

=========

- 大量資料透過DELETE語句而刪除

- 資料刪除後，客戶進行了相關的維護工作 : 重建索引和更新統計資料

- 效能變慢的儲存過程會對一些表做很多的”DELETE”，”INSERT”和”SELECT”操作。

調查

=========

- 相關的表都是堆( heap table)

- 這些表中並沒有大量資料

- DBCC CHECKCONTIG 結果顯示錶很大，但其頁的密度 (Page Density) 卻相當小。

DBCC SHOWCONTIG scanning 'tblA' table...

Table: 'tblA' (322816212); index ID: 0, database ID: 14

TABLE level scan performed.

- Pages Scanned................................: 1779939 à13.6GB

- Extents Scanned..............................: 223475

- Extent Switches..............................: 223474

- Avg. Pages per Extent........................: 8.0

- Scan Density [Best Count:Actual Count].......: 99.56% [222493:223475]

- Extent Scan Fragmentation ...................: 23.65%

- Avg. Bytes Free per Page.....................: 8059.1

- Avg. Page Density (full).....................: 0.43%

DBCC SHOWCONTIG scanning 'tblB' table...

Table: 'tblB' (1005246636); index ID: 0, database ID: 14

TABLE level scan performed.

- Pages Scanned................................: 215600 à1.6GB

- Extents Scanned..............................: 27269

- Extent Switches..............................: 27268

- Avg. Pages per Extent........................: 7.9

- Scan Density [Best Count:Actual Count].......: 98.83% [26950:27269]

- Extent Scan Fragmentation ...................: 38.87%

- Avg. Bytes Free per Page.....................: 7998.3

- Avg. Page Density (full).....................: 1.18%

原因

=========

- 當表上有聚集索引時，刪除操作會釋放空頁。然而，從堆中刪除行時，資料庫引擎可以使用行鎖定或頁鎖定進行操作。結果，刪除操作導致的空頁將繼續分配給堆。未釋放空頁時，資料庫中的其他物件將無法重用關聯的空間。

- 雖然表中沒有大量資料，但是它們擁有大量的幾乎為空的資料頁。掃描表因此變得十分花時間。

解決方案

=========

- 若要刪除堆中的行並釋放頁，我們可以使用下列方法之一。

· 在 DELETE 語句中指定 TABLOCK 提示。使用 TABLOCK 提示會導致刪除操作獲取表的共享鎖，而不是行鎖或頁鎖。這將允許釋放頁。

· 如果要從表中刪除所有行，請使用 TRUNCATE TABLE。

· 刪除行之前，請對堆建立聚集索引。刪除行之後，可以刪除聚集索引。與先前的方法相比，此方法非常耗時，並且使用更多的臨時資源。

參考資訊

=========

使用 DELETE 刪除行

Space that a table uses is not completely released after you use a DELETE statement to delete data from the table in SQL Server

結果

========

- 客戶的應用邏輯允許將表清空。因此我們選擇了TRUCATE TABLE的方法。完成後，出問題的儲存過程的效能都恢復到了從前。