Ms sql MERGE INTO 鎖定整個表以進行更新

我有一個統計值表，它包含數百萬條記錄，定義如下：

CREATE TABLE [dbo].[Statistic]
(
   [Id] [INT] IDENTITY(1, 1) NOT NULL
 , [EntityId] [INT] NULL
 , [EntityTypeId] [UNIQUEIDENTIFIER] NOT NULL
 , [ValueTypeId] [UNIQUEIDENTIFIER] NOT NULL
 , [Value] [DECIMAL](19, 5) NOT NULL
 , [Date] [DATETIME2](7) NULL
 , [AggregateTypeId] [INT] NOT NULL
 , [JsonData] [NVARCHAR](MAX) NULL
 , [WeekDay] AS (DATEDIFF(DAY, CONVERT([DATETIME], '19000101', (112)), [Date]) % (7) + (1)) PERSISTED
 , CONSTRAINT [PK_Statistic]
       PRIMARY KEY NONCLUSTERED ([Id] ASC)
);

CREATE UNIQUE CLUSTERED INDEX [IX_Statistic_EntityId_EntityTypeId_ValueTypeId_AggregateTypeId_Date]
ON [dbo].[Statistic] (
                        [EntityId] ASC
                      , [EntityTypeId] ASC
                      , [ValueTypeId] ASC
                      , [AggregateTypeId] ASC
                      , [Date] ASC
                    );

CREATE NONCLUSTERED INDEX [IX_Date] ON [dbo].[Statistic] ([Date] ASC);

CREATE NONCLUSTERED INDEX [IX_EntityId]
ON [dbo].[Statistic] ([EntityId] ASC)
INCLUDE ([Id]);

CREATE NONCLUSTERED INDEX [IX_EntityType_Agg_Date]
ON [dbo].[Statistic] ([EntityTypeId] ASC, [AggregateTypeId] ASC, [Date] ASC)
INCLUDE ([Id], [EntityId], [ValueTypeId]);

CREATE NONCLUSTERED INDEX [IX_Statistic_ValueTypeId]
ON [dbo].[Statistic] ([ValueTypeId] ASC)
INCLUDE ([Id]);

CREATE NONCLUSTERED INDEX [IX_WeekDay]
ON [dbo].[Statistic] ([AggregateTypeId] ASC, [WeekDay] ASC, [Date] ASC)
INCLUDE ([Id]);

ALTER TABLE [dbo].[Statistic]
ADD CONSTRAINT [PK_Statistic]
   PRIMARY KEY NONCLUSTERED ([Id] ASC);

在使用合併更新期間，sql server 鎖定整個表而不是頁/行，@inTbl是作為參數傳遞的鍵/值數據表

MERGE INTO Statistic AS stat
USING
   (SELECT inTbl.EntityId, inTbl.Value FROM @p0 AS inTbl) AS src
ON src.EntityId = stat.EntityId
  AND stat.EntityTypeId = @p1
  AND stat.ValueTypeId = @p2
  AND stat.Date IS NULL
  AND stat.AggregateTypeId = @p3
WHEN MATCHED THEN
   UPDATE SET stat.Value = src.value
WHEN NOT MATCHED BY TARGET THEN
   INSERT (EntityTypeId, ValueTypeId, Date, AggregateTypeId, EntityId, Value)
   VALUES
   (@p4, @p5, @p6, @p7, src.entityId, src.value);

所以，我有兩個問題：1）合併有時需要很長時間才能完成

2）像這樣的更新等待合併完成：

UPDATE [dbo].[Statistic]
SET [Value] = @p0, [JsonData] = @p1
WHERE [EntityTypeId] = @p2
     AND [ValueTypeId] = @p3
     AND [Date] = @p4
     AND [EntityId] = @p5
     AND [AggregateTypeId] = @p6;

我有查詢的計劃/鎖定文件，但它們相當大，所以它們在這裡

重建指數前：https ://www.brentozar.com/pastetheplan/?id=S19EgxYIB

索引重建後：https ://www.brentozar.com/pastetheplan/?id=SyjexxtLH

可能是什麼問題？這種情況偶爾會發生，有時可能會在聚集索引重建後消失。

聚集索引在一天左右的時間內碎片化到 90+%。如何防止這種碎片化？

我有一個統計值表，它包含數百萬條記錄

…

聚集索引在一天左右的時間內碎片化到 90+%。

看看你的clustered index，它key很48 bytes長，這不是一個好的選擇，因為你的桌子足夠大，而且你還有 5 個nonclustered indexes。它們都具有這些48 bytesat every index level，因此每個非聚集索引至少佔用它所需空間的兩倍。

恕我直言，如果可能的話，首先要做的是改變clustered index key，你clustered index可以在 上定義identity，它將是唯一的，總是增加，變窄，這將減少 yor clustered index fragmentation，並且如果JsonData欄位永遠不會更新，則為clustered index fragmentation0。

這也將減少您的時間：現在由於 insert intoinsert花費了太多時間來記錄日誌。page slits``clustered index

對於你的第二個問題：lock escalation. 正如您所說，每批在源表中包含 2000 行，但它們會導致插入 3402 行（根據estimated plan），這僅適用於clustered index. 您有 5 nonclustered indexes，因此在其中statement插入至少 6 * 2000 = 12000 行，或者如果估計正確，則可能插入所有 20412 行。

Lock escalation每5000locks次觸發statement：

除了在超過實例範圍的門檻值時升級鎖之外，當任何單個會話在單個語句中獲得超過 5,000 個鎖時，SQL Server 還將升級鎖。在這種情況下，選擇哪個會話將升級其鎖沒有隨機性。它是獲取鎖的會話。

在您的情況下，它們很可能是row locks，這是因為您的聚集索引鍵是隨機的。可能需要page locks插入始終增加的密鑰，但您clustered key確實是隨機的。而且在任何情況下，插入非聚集索引也是隨機的，所以伺服器選擇row locks.

因此，您可以禁用表上的鎖升級或將批次拆分為每批次 1000 行甚至更少，這應該進行測試。

---

以下是對此評論的回應：

插入不能鎖定（不能鎖定不存在的資源）

if object_id('dbo.t') is not null drop table dbo.t;
create table dbo.t(id int identity primary key, col1 varchar(10), col2 varchar(10));
create index ix_col1 on dbo.t(col1);
create index ix_col2 on dbo.t(col2);

begin tran
insert into dbo.t (col1, col2)
select top 1000 'aaa', 'bbb'
from sys.columns c1 cross join sys.columns c2;

select *
from sys.dm_tran_locks
where resource_type <> 'DATABASE'
     and request_session_id = @@spid
order by resource_associated_entity_id,
        resource_type;

rollback tran;

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