4

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?

  • @George.Palacios yes, RCSI is enabled, this is the first thing I do in new DBs now :) – xumix Sep 13 '19 at 10:12
  • Apologies - realised it's likely not relevant as you're running two update queries, not a SELECT and an UPDATE. What is the lock escalation setting set to for this table? – George.Palacios Sep 13 '19 at 10:13
  • Also, have a read of this to provide us with a minimal reproducible example, and if you could, post the "Slow" plan to PasteThePlan.com. I'd also be tempted to reword your question as you've actually asked 3 separate things: 1. Why is the merge slow? 2. Why is the merge blocking other update queries? 3. Why is the Clustered Index becoming fragmented? These are 3 very distinct questions – George.Palacios Sep 13 '19 at 10:14
  • @George.Palacios It is TABLE. What are the downsides of disabling the lock escalation? – xumix Sep 13 '19 at 10:15
  • 1
    @sepupic I don't think classifying the clustered index as "horrible" is constructive or helpful to the question. It is wide and prone to fragmentation and may not be the best choice. But to declare it "horrible" (twice) is a bit inflammatory. – AMtwo Sep 13 '19 at 13:59
3

I have a table for statistic values, it holds millions of records

...

The clustered index goes fragmeted to 90+% in a day or so.

Look at your clustered index, its key is 48 bytes long, it's not a good choice because your table is big enough and you have also 5 nonclustered indexes. All of them have these 48 bytes at every index 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, your clustered index can be defined on identity, it will be unique, always increasing, narrow, and this will reduce yor clustered index fragmentation, and in case when JsonData field is never updated clustered index fragmentation will be 0.

This will also decrease your insert time: now too much time is spent to log page slits caused by insert into clustered 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 to estimated plan), and this is only for clustered index. You have 5 nonclustered indexes, so in one statement you insert at least 6 * 2000 = 12000 rows, or maybe all 20412 rows if the estimations are correct.

Lock escalation triggers on 5000 locks per statement:

In addition to escalating locks when an instance-wide threshold is crossed, SQL Server will also escalate locks when any individual session acquires more than 5,000 locks in a single statement. In this case, there is no randomness in choosing which session will get its locks escalated; it is the session that acquired the locks.

and in your case they very probably are row locks, this is because of your clustered index key that is random. It could take page locks in case of insertion into always increasing key, but your clustered key is really random. And in any case insertions into nonclustered indexes are random too, so it's normal that server chose row 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:

inserts can't take locks (can't lock a resource that doesn't exist)

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;
  • Thank you for your insightful answer! – xumix Sep 14 '19 at 16:37
  • I'm in no way a DBA so I thought a clustered index with data organized by EntityId and it's stat data would minimize locks and allow faster lookups. What is your recommendation for clustered index here? – xumix Sep 14 '19 at 16:50
  • >>>I thought a clustered index with data organized by EntityId and it's stat data would minimize locks and allow faster lookups.<<<If almost all your queries filter by EntityId it can be chosen as clustered index key, but even in this case you should try to keep your clustered index key narrow, so make your clustered index on EntityId only, it will still help you to keep nonclustered indexes narrow too. In this case you should use a lower fill factor to minimize index fragmentation even if it will only mitigate fragmentation, not get it away – sepupic Sep 16 '19 at 7:00
  • But if your queries even filtering by EntityId return only some rows I recommebd make clustered index on identity field, and make a nonlustered index on EntityId. It's much more easier to rebuild small nonclustered index on EntityId than clustered index, and it will keep your (clusterd) table non fragmented – sepupic Sep 16 '19 at 7:04
  • thanks again, I'll try your recomendations – xumix Sep 16 '19 at 10:04
1

There are two parts to your question. The first is how to make the MERGE operation more reliably performant. I think that changing your merge statement into something closer to what I have below will help keep table locks to a minimum.

The use of the CTE allows for what I feel a more straightforward selecting of the data you would want to apply to the merge, as well as allowing the specification of ROWLOCK, UPDLOCK and HOLDLOCK hints which should further encourage the engine to lock just what it needs. It can and will still elevate to table locks if it needs to, this just helps.

;WITH CTE_Statistic AS
    (
    SELECT S.ID
        , S.EntityID
        , S.EntityTypeID
        , S.ValueTypeID
        , S.Value
        , S.[Date]
        , S.AggregateTypeID
        , S.JsonData
        , S.WeekDay
    FROM dbo.Statistic AS S WITH (UPDLOCK, ROWLOCK, HOLDLOCK)
    WHERE EXISTS (SELECT TOP (1) 1 FROM @p0 AS P WHERE P.EntityID = S.EntityID)
        AND S.EntityTypeId = @p1
        AND S.ValueTypeId = @p2
        AND S.Date IS NULL
        AND S.AggregateTypeId = @p3 
    )
MERGE INTO CTE_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 AND stat.value <> src.value 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);

The second is that as you have given examples, that these indexes fragment quickly, degrading performance. This is due to your index structure (especially your clustered index) allowing what I assume are middle of the index inserts. This is great if you value reads over writes (as most of us do), but you need to take that into account.

I would start by specifying a fill factor for your indexes so that when they are rebuilt, they allow some space for out of order inserts. I would set your clustered index to a fill factor of 70, and start the rest at 90.

For example (this example assumes you have enterprise, if not set ONLINE=OFF and/or use reorganize instead.

ALTER INDEX [IX_Statistic_EntityId_EntityTypeId_ValueTypeId_AggregateTypeId_Date] ON dbo.Statistic REBUILD WITH (ONLINE=ON, FILL_FACTOR=70);  

This will take up more space on disk, but disk is usually cheap.

  • If the granularity of locks will be ROWLOCK (that OP already has, I bet), table escalation is guaranteed – sepupic Sep 13 '19 at 12:58
  • That depends on how many are updates vs inserts. I'm going to edit my merge statement a bit to prevent unnecessary updates. – Jonathan Fite Sep 13 '19 at 13:08
  • If you look into his plan, you'll see that he has nearly 3402 inserts into 6 indexes (3402 per index). If they are done with rowlocks be sure they will be escalated to table lock – sepupic Sep 13 '19 at 13:21
  • inserts can't take locks (can't lock a resource that doesn't exist) and won't count toward the tipping point. I think the real win is going to be fixing his indexes so they don't fragment as badly. – Jonathan Fite Sep 13 '19 at 13:22
  • I updated my answer with a small repro that shows how insert acquires locks on PK and nonclustered indexes – sepupic Sep 13 '19 at 13:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.