執行 DELETE 儲存過程，但發現 WHILE 循環中的性能隨著迭代次數的增加而下降

有一個快速的一般性問題。我有一張桌子，我正試圖清理一張桌子。我正在使用批次之間時間為 50 毫秒且每批次 2000 條記錄的WHILE循環進行刪除。WAITFOR DELAY真正的問題是，隨著時間的推移，刪除的記錄數量會隨著時間的推移而下降。請參閱以下內容：

Minute Number | Number of Records Deleted:

           1 | 162,000 
           2 | 116,000 
           3 |  80,000 
           4 |  72,000 
           5 |  62,000 
           6 |  38,000 
           7 |  38,000 
           8 |  34,000 
           9 |  20,000

想知道使用循環批處理刪除記錄是否有一些基本的東西WHILE會導致性能隨著循環的每次迭代而下降。我們一直在手動觀察性能，然後在第 5 到第 6 分鐘左右性能開始急劇下降時停止 proc，然後再次重新啟動部署。我們不認為這是一個直接的鎖定問題，因為當我們使用批量大小時，性能總是在 5 到 6 分鐘左右下降。

您說性能隨著執行次數的增加而下降，並且“重新啟動部署”可以解決它。

我不清楚你所說的那個特定片語是什麼意思，但假設它涉及停止循環，然後在一段時間後重新啟動它，那麼一種可能性是ghost records。

我創建了一個如下表（每頁一行以便於計算）

CREATE TABLE T
 (
    X INT PRIMARY KEY,
    Y CHAR(8000)
 )

INSERT INTO T
SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY (SELECT 0)),
                 'Y'
FROM   master..spt_values v1,
      master..spt_values v2

並假設您的DELETE程序正在清除某些升序列所標識的舊行。

SET STATISTICS IO ON;

DECLARE @i INT = 0;
WHILE @i < 10
 BEGIN
     SET @i+=1

     RAISERROR('Processing %d',0,1, @i) WITH NOWAIT

     DELETE TOP (2000) FROM T
     WHERE  X <= 50000

     /*50 MS Delay*/
     WAITFOR DELAY '00:00:00:050'

     SELECT ghost_record_count,
            version_ghost_record_count
     FROM   sys.dm_db_index_physical_stats(db_id(), OBJECT_ID('T'), NULL, NULL, 'DETAILED')
     WHERE  index_level = 0
 END 

結果

+---------------+--------------------+----------------------------+
| logical reads | ghost_record_count | version_ghost_record_count |
+---------------+--------------------+----------------------------+
|          2018 |               2000 |                          0 |
|          4025 |               4000 |                          0 |
|          6033 |               6000 |                          0 |
|          8026 |               7965 |                          0 |
|         10004 |               9944 |                          0 |
|         11989 |              11920 |                          0 |
|         13977 |              13902 |                          0 |
|         15963 |              15882 |                          0 |
|         17954 |              17864 |                          0 |
|         19943 |              19849 |                          0 |
+---------------+--------------------+----------------------------+

記錄不會立即刪除。它們被標記為幽靈，然後由後台任務清除。在上面的測試中，在 10 次迭代中很少有它們被清理，這意味著第十DELETE次的讀取次數是第一次的 10 倍。

可能在您的環境中，DELETEs 的發生率高於幽靈清理任務處理它們的速度，從而導致性能持續下降。

我已經看過幾十次了。總是因為表的統計數據逐漸過時。如果是這種情況，更新統計資訊或重新索引表應該可以恢復性能。通過任何方法讓您的批次重新編譯也可能會有所幫助。

引用自：https://dba.stackexchange.com/questions/44004