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);
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.
If another non-select table operation is excecuted (like insert bulk) the are block for a very long time.
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.
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):
I also tried @get_locks=1 here is the result. Maybe it helps you with more details.
Update 2 - related to Nic answere
Here is one example:
sid 52 is blocked by sid 119 for over 40sec!? But sid 119 seems not to be blocked. I really not understand this.