Blocking Upsert Procedure in SQL Server – How to Resolve

blockingmergesql serversql server 2014

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.

Problem

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.

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):

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.

Best Answer

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.

Related Solutions

SQL Server 2014 – Blocking Issue with sp_releaseschemalock

sp_releaseschemalock is an undocumented external proc which simply releases the schema lock:

As per our past experience on this, we witnessed the same but never had the issue with sp_releaseschemalock being a culprit.

Kindly check the complete details when checking the blocking. That is ,you should not completely ignore the one that is getting blocked rather than completely dependent on blkd_by_spid, as blkd_by shows sp_releaseschemalock as culprit.

Also would like you to refer this

You should check your linked server configurations and make sure they are "collation compatible" checked. Otherwise the entire remote table is returned to the local calling server to execute the WHERE clause.

As mentioned, you need to go through the settings for the query getting executed via linked server because the primary server wont have the required statistical information when fetching the same from other server through its linked query.

Sql-server – deteriorating stored procedure running times

What is up with FROM part JOIN model ON 1=1? This the same as FROM part, model, which is a cartesian join and will result in a very large number of rows. Is that join supposed to be like that?

You will likely help us help you if you provide details about the tables involved. Please "script" the definition of the tables, along with any indexes defined on those tables.

This sounds like a classic case of parameter sniffing resulting in good plan/bad plan choices for various scenarios in your data.

You may be able to get more reliable performance by making SQL Server cache different plans for different scenarios by using sp_executesql, as in the following example:

CREATE PROCEDURE [dbo].[create_grid_materials2] 
(
    @partlistid bigint
    , @pid bigint
    , @masterid bigint
)
AS
BEGIN
    begin
        DECLARE @cmd NVARCHAR(MAX);

        SET @cmd = '   
        INSERT INTO material (partid, personid, modelID)
        SELECT 
            partid = part.id
            , personid = @pid
            , modelid = model.id  
        FROM part
            INNER JOIN model ON 1=1
        WHERE (
            model.masterid = ' + CONVERT(NVARCHAR(50), @masterid) + ' 
                AND model.modelSetID IS NULL
                AND part.partlistid = ' + CONVERT(NVARCHAR(50), @partlistid) + '
                AND (
                    part.partType = 100 
                    or part.partType=120 
                    or part.partType = 130
                )
            )
            AND NOT EXISTS (
                SELECT 1 
                FROM material AS a1 
                WHERE a1.partid = part.id 
                    AND a1.personid=@pid 
                    AND a1.modelid=model.id
                )';
        DECLARE @Params VARCHAR(200);
        SET @Params = '@pid INT';
        EXEC sys.sp_executesql @cmd
            , @Params
            , @pid = @pid;
    end
End

The above code will cause a new plan to be generated for each combination of @partlistid, and @masterid.

The presumption here is some combinations of those two variables lead to a very small number of rows, whereas some combinations lead to a very large number of rows.

Forcing a plan for each combination allows SQL Server to generate more efficient plans for each. I've explicitly not included @pid since you probably want to try it with a fairly small number of combinations first; adding a third variable to the mix will make for an exponentially larger number of possible plans.