I have a table for statistic values, it holds millions of records, which is defined like this:
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);
During updates with merge, sql server locks the whole table instead of pages/rows, @inTbl
is a key/value datatable passed as parameter
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);
So, I have 2 problems:
1) the merge sometimes takes forever to finish
2) updates like this wait for merge to finish:
UPDATE [dbo].[Statistic]
SET [Value] = @p0, [JsonData] = @p1
WHERE [EntityTypeId] = @p2
AND [ValueTypeId] = @p3
AND [Date] = @p4
AND [EntityId] = @p5
AND [AggregateTypeId] = @p6;
I have plans/locks files for the queries, but they are rather big, so here they are
before index rebuid: https://www.brentozar.com/pastetheplan/?id=S19EgxYIB
after index rebuild: https://www.brentozar.com/pastetheplan/?id=SyjexxtLH
What can be the problem? This happens occasionally and may sometimes go away after clustered index rebuild.
The clustered index goes fragmeted to 90+% in a day or so. How can I prevent this fragmentation?
Best Answer
Look at your
clustered index
, itskey
is48 bytes
long, it's not a good choice because your table is big enough and you have also 5nonclustered indexes
. All of them have these48 bytes
at everyindex level
, so every nonclustered index occupies at least twice of space it needs.IMHO, the first thing to do is, if possible, to change
clustered index key
, yourclustered index
can be defined onidentity
, it will be unique, always increasing, narrow, and this will reduce yorclustered index fragmentation
, and in case whenJsonData
field is never updatedclustered index fragmentation
will be 0.This will also decrease your
insert
time: now too much time is spent to logpage slits
caused by insert intoclustered index
.To your second problem:
lock escalation
. As you said, every batch contains 2000 rows in the source table, but they cause 3402 rows to be inserted(according toestimated plan
), and this is only forclustered index
. You have 5nonclustered indexes
, so in onestatement
you insert at least 6 * 2000 = 12000 rows, or maybe all 20412 rows if the estimations are correct.Lock escalation
triggers on 5000locks
perstatement
:and in your case they very probably are
row locks
, this is because of your clustered index key that is random. It could takepage locks
in case of insertion into always increasing key, but yourclustered key
is really random. And in any case insertions into nonclustered indexes are random too, so it's normal that server choserow locks
.So you can disable lock escalation on your table or split your batches in 1000 rows per batch or even less, this should be tested.
Here is a small repro in response on this comment: