判斷刪除大量記錄時是否有阻塞

我想知道是否有任何方法可以確定為什麼從 Sql Azure 中託管的數據庫中刪除 50​​K 記錄需要花費太多時間，比如 3 分鐘。我在主鍵上有一個聚集索引，在 ID 上有一個非聚集索引，在刪除查詢中的 where 條件下使用

DECLARE @RowsDeleted INTEGER
SET @RowsDeleted = 1

WHILE (@RowsDeleted > 0)
BEGIN
   DELETE top(100) FROM table WHERE ScenarioID= @ID
   SET @RowsDeleted = @@ROWCOUNT
END

有什麼辦法可以知道這張表上是否有鎖

索引：

ALTER TABLE [dbo].[ActCost] ADD  CONSTRAINT [PK_ActCost] PRIMARY KEY CLUSTERED 
(
   [ActCostID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
GO



Alter NONCLUSTERED INDEX [IX_ActCost_ScenarioID] ON [dbo].[ActCost] ([ScenarioID] ASC)
INCLUDE ( <list_all_other_columns_returned> );
GO


CREATE TABLE [dbo].[ActCost](
   [ActCostID] [int] IDENTITY(1,1) NOT NULL,
   [ActID] [int] NOT NULL,
   [ActCostTypeID] [int] NOT NULL,
   [Description] [nvarchar](200) NOT NULL,
   [Cost] [float] NOT NULL,
   [CostPerProductUnit] [float] NOT NULL,
   [CostPerEndProductUnit] [float] NOT NULL,
   [OtherValue] [float] NULL,
   [OtherID] [int] NULL,
   [Comment1] [nvarchar](200) NULL,
   [Comment2] [nvarchar](200) NULL,
   [OPerProductUnit] [float] NULL,
   [OPerHour] [float] NULL,
   [OCostPerUnit] [float] NULL,
   [OCostPerHour] [float] NULL,
   [PerfEnh_ProcessID] [int] NOT NULL,
   [PerfEnh_MillID] [int] NOT NULL,
   [ScenarioID] [int] NOT NULL,
CONSTRAINT [PK_ActCost] PRIMARY KEY CLUSTERED 
(
   [ActCostID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
)

GO

Azure 空間：可用 2 GB 已用：500MB

我有另一個具有相同數據但可用大小更多的數據庫，它工作正常我的意思是只需 20-30 秒即可完成相同的工作，這會是一個因素嗎？

阻塞檢查：

標題說阻塞，但問題說鎖定。我將處理前者，因為後者應該是給定的：是的，需要鎖定才能執行刪除。

要檢查阻止，請在執行刪除的視窗中：

SELECT @@SPID; -- make note of this

在另一個視窗中：

SELECT blocking_session_id, wait_type
 FROM sys.dm_exec_requests
 WHERE session_id = <spid from above>;

我知道這個 DMV 在最近兩次 SQL 數據庫刷新中可用，但不太確定您使用的是哪個版本。

現在，如果您遇到了攔截器，請檢查他們在做什麼。

SELECT r.[status], r.command, r.blocking_session_id, 
   r.wait_type, query = SUBSTRING(CONVERT(NVARCHAR(MAX), 
   t.[text]), r.statement_start_offset/2, 
   COALESCE(NULLIF(r.statement_end_offset,-1), 
   LEN(CONVERT(NVARCHAR(MAX),t.[text]))*2)/2)
 FROM sys.dm_exec_requests AS r
 OUTER APPLY sys.dm_exec_sql_text(r.[sql_handle]) AS t
 WHERE r.session_id = <blocking session>;

在這種情況下您無法執行DBCC INPUTBUFFER，因為在 SQL 數據庫中這受到了一定程度的保護——您只能針對您自己的@@SPID.

也可能是日誌文件自動增長是問題 - 如果此處的聚集索引刪除實際上必須從許多索引和/或索引視圖中刪除行，則問題可能是日誌瓶頸。我通常的首選是預設跟踪，但我不相信您可以在 Azure 中訪問它。值得慶幸的是，在您的場景中，我們可以看到由於顯示的等待類型而出現這種情況 - 您的查詢實際上被系統故意放慢了速度，因為您生成了太多的日誌活動。我不確定這個算法究竟是如何工作的，但如果它在您刪除 100 行時發生，那麼可能是時候遷移到具有更好“保證”性能的實例（這可能意味著支付更多）。簡單地在同一個計劃中啟動一個新實例甚至可能會有所幫助，因為您可能最終會使用新一代硬體，這些硬體自然應該更快地執行日誌操作，並且可以幫助您避免碰到調速器。

您還應該在循環內以某種頻率送出事務。否則，分塊並沒有真正的幫助，因為當您嘗試刪除最後 100 行時，您可能仍然持有前 100 行的鎖。更頻繁地送出較小的塊將對日誌產生較小的影響，並且可能對鎖定/阻塞也有影響。

DECLARE @RowsDeleted INT = 1, @counter INT = 1;

BEGIN TRANSACTION;

WHILE (@RowsDeleted > 0)
BEGIN
   DELETE top(100) FROM dbo.table WHERE ScenarioID = @ID;
   SELECT @RowsDeleted = @@ROWCOUNT, @counter += 1;
   IF @counter % 10 = 0
   BEGIN
     COMMIT TRANSACTION;
     BEGIN TRANSACTION;
   END
END

COMMIT TRANSACTION;

您還可以在循環中放置一個 waitfor - 也許暫停足夠長的時間將防止觸發日誌速率調節器。

使用“批量樣式”刪除 3 分鐘的 50k 記錄實際上並不算太糟糕。這也是減少鎖定/阻塞的好方法。任何時候你執行一個DELETE操作，都會有鎖。您在這裡所做的是通過一次只做 100 條記錄來最小化這種情況。

因此，要回答您的問題，是的，在刪除操作執行時，表上有鎖。這些鎖被最小化了，因為您在 100 個記錄批次中進行刪除。請記住，當您更新表時（實際上是任何 DUI 操作），您還必須對關聯的索引進行相同的更改。

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