2

Setup

I have a simple table:

CREATE TABLE [dbo].[StringData](
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [DCStringID] [bigint] NOT NULL,
    [TimeStamp] [datetime] NOT NULL,
    [Hour]  AS (dateadd(hour,datediff(hour,(0),[TimeStamp]),(0))) PERSISTED,
    [Date]  AS (CONVERT([date],[TimeStamp],(0))) PERSISTED,
    [DCVoltage] [decimal](18, 2) NULL,
    [DCCurrent] [decimal](18, 2) NULL,
    [DCPower]  AS (([DCVoltage]*[DCCurrent])/(1000)) PERSISTED,
    [IsCompressed] [bit] NULL,
    [TimeStamp15Minutes]  AS (dateadd(minute,(datediff(minute,(0),[TimeStamp])/(15))*(15),(0))),
CONSTRAINT [PK_StringData1] PRIMARY KEY CLUSTERED 
(
    [TimeStamp] DESC,
    [DCStringID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

And I have an upsert procedure called [InsertOrUpdateStringData]

ALTER PROCEDURE [dbo].[InsertOrUpdateStringData]
@DCStringID bigint, @TimeStamp datetime, @DCVoltage decimal(18,2), @DCCurrent decimal(18,2)
AS
/****** avoid parameter spoofing ******/
DECLARE @DCStringID_Value AS bigint; SET @DCStringID_Value = @DCStringID;
DECLARE @TimeStamp_Value AS datetime; SET @TimeStamp_Value = @TimeStamp
DECLARE @DCVoltage_Value AS decimal(18,2); SET @DCVoltage_Value = @DCVoltage
DECLARE @DCCurrent_Value AS decimal(18,2); SET @DCCurrent_Value = @DCCurrent
/************/

MERGE [StringData] AS TARGET
USING (VALUES (@DCStringID_Value, @TimeStamp_Value))
    AS SOURCE ([DCStringID], [TimeStamp])
    ON TARGET.[DCStringID] = SOURCE.[DCStringID] AND TARGET.[TimeStamp] = SOURCE.[TimeStamp]
WHEN MATCHED THEN
    UPDATE
    SET [DCVoltage] = @DCVoltage_Value,
    [DCCurrent] = @DCCurrent_Value
WHEN NOT MATCHED THEN
    INSERT ([DCStringID], [TimeStamp], [DCVoltage], [DCCurrent]) 
    VALUES (@DCStringID_Value, @TimeStamp_Value, @DCVoltage_Value, @DCCurrent_Value);

Using

Many different application threads are using the [InsertOrUpdateStringData] procedure to upsert data to the table synchronous.

The table indexes are use well and one single excecution is quite fast and take around 31ms.

plan

clientstats

Problem

If another non-select table operation is excecuted (like insert bulk) the are block for a very long time.

whoisactive

Unfortunately [sp_WhoIsActive] is not showing me if the [InsertOrUpdateStringData] procedure calls are also blocking each other. But it looks like there is a lock chaining because its the only explanation for locking over 10,000ms like in my example.

Question

It seems that my upsert procedure is locking the complete table and slow down other insert operations.

Is the anything I can do to optimize my queries to avoid longtime blocking?

Update 1 - related to Nic answere

The lock happens to all non-select operations, as I wrote bulk insert is only a sample.

Please see here a 10sec lock simple merge call (blocking session 176 is a [InsertOrUpdateStringData] call):

merge lock

I also tried @get_locks=1 here is the result. Maybe it helps you with more details.

get_locks=1

Update 2 - related to Nic answere

Here is one example:

lock

sid 52 is blocked by sid 119 for over 40sec!? But sid 119 seems not to be blocked. I really not understand this.

1

Since your spid 119 is in [sleeping] state, I'd recommend you to check:

  • DBCC Opentran() to check whether there is any open transaction on 119 (high possibility to my experience)
  • sp_lock 52 to check what lock in spid 52 is NOT granted
  • sp_lock 119 to check what lock it holds, and you can trace the lock back to the resource (such as table or index or key etc)

Also maybe you can turn on READ_COMMITTED_SNAPSHOT on your database to avoid the blocking between read and write operation.

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.