I have a problem with a massive amount of INSERTs that are blocking my SELECT operations.
Schema
I have a table like this:
CREATE TABLE [InverterData](
[InverterID] [bigint] NOT NULL,
[TimeStamp] [datetime] NOT NULL,
[ValueA] [decimal](18, 2) NULL,
[ValueB] [decimal](18, 2) NULL
CONSTRAINT [PrimaryKey_e149e28f-5754-4229-be01-65fafeebce16] PRIMARY KEY CLUSTERED
(
[TimeStamp] DESC,
[InverterID] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF
, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON
, ALLOW_PAGE_LOCKS = ON)
)
I also have this little helper procedure, that allows me to insert or update (update on conflict) with MERGE command:
CREATE PROCEDURE [InsertOrUpdateInverterData]
@InverterID bigint, @TimeStamp datetime
, @ValueA decimal(18,2), @ValueB decimal(18,2)
AS
BEGIN
MERGE [InverterData] AS TARGET
USING (VALUES (@InverterID, @TimeStamp, @ValueA, @ValueB))
AS SOURCE ([InverterID], [TimeStamp], [ValueA], [ValueB])
ON TARGET.[InverterID] = @InverterID AND TARGET.[TimeStamp] = @TimeStamp
WHEN MATCHED THEN
UPDATE
SET [ValueA] = SOURCE.[ValueA], [ValueB] = SOURCE.[ValueB]
WHEN NOT MATCHED THEN
INSERT ([InverterID], [TimeStamp], [ValueA], [ValueB])
VALUES (SOURCE.[InverterID], SOURCE.[TimeStamp], SOURCE.[ValueA], SOURCE.[ValueB]);
END
Usage
I now have run service instances on multiple servers that perform massive updates by calling the [InsertOrUpdateInverterData]
procedure rapidly.
There is also a website that does SELECT queries on the [InverterData]
table.
Problem
If I do SELECT queries on the [InverterData]
table they are proceeded in different timespans, depended on the INSERT usage of my service instances.
If I pause all service instances the SELECT is lightning-fast, if the instance perform rapid insert the SELECTs get really slow or even a timeout cancel.
Attempts
I'm done some SELECTs on the [sys.dm_tran_locks]
table to find locking processes, like this
SELECT
tl.request_session_id,
wt.blocking_session_id,
OBJECT_NAME(p.OBJECT_ID) BlockedObjectName,
h1.TEXT AS RequestingText,
h2.TEXT AS BlockingText,
tl.request_mode
FROM sys.dm_tran_locks AS tl
INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address
INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id
INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id
INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
This is the result:
S = Shared. The holding session is granted shared access to the resource.
Question
Why are the SELECTs blocked by the [InsertOrUpdateInverterData]
procedure that is only using MERGE commands?
Do I have to use some kind of transaction with defined isolation mode inside of [InsertOrUpdateInverterData]
?
Update 1 (related to question from @Paul)
Base on MS-SQL server internal reporting about [InsertOrUpdateInverterData]
following statistic:
- Average CPU-Time: 0.12ms
- Average Read processes: 5.76 per/s
- Average Write processes: 0.4 per/s
Base on this it looks like the MERGE command is mostly busy with reading operations that will lock the table!(?)
Update 2 (related to question from @Paul)
The [InverterData]
table as has following storage stats:
- Data space: 26,901.86 MB
- Row count: 131,827,749
- Partitioned: true
- Partition count: 62
Here is the (allmost) complete sp_WhoIsActive result set:
SELECT
command
- dd hh:mm:ss.mss: 00 00:01:01.930
- session_id: 73
- wait_info: (12629ms)LCK_M_S
- CPU: 198
- blocking_session_id: 146
- reads: 99,368
- writes: 0
- status: suspended
- open_tran_count: 0
Blocking [InsertOrUpdateInverterData]
command
- dd hh:mm:ss.mss: 00 00:00:00.330
- session_id: 146
- wait_info: NULL
- CPU: 3,972
- blocking_session_id: NULL
- reads: 376,95
- writes: 126
- status: sleeping
- open_tran_count: 1
([TimeStamp] DESC, [InverterID] ASC)
looks like a weird choice for the clustered index. I mean theDESC
part.