rezasparrow
commented
7 months ago I Think the problem is because of this link
Specifying FORCESEEK with parameters limits the number of plans that can be considered by the optimizer more than when specifying FORCESEEK without parameters. This may cause a Plan cannot be generated error to occur in more cases. In a future release, internal modifications to the query optimizer may allow more plans to be considered.
When the background jobs can not aggregate the AggregatedCounter and delete theme so after a while number of data increases and then that cause of deadlock in sql server
I have multiple server that they run jobs base on hangfire. but I got this error for running hangfire for running this query in sql server. I use sql server version 2022 and my hangfire version 1.8.11
this the query that run every time
(@count int)DECLARE @RecordsToAggregate TABLE ( [Key] NVARCHAR(100) COLLATE DATABASE_DEFAULT NOT NULL, [Value] INT NOT NULL, [ExpireAt] DATETIME NULL ) SET XACT_ABORT ON SET TRANSACTION ISOLATION LEVEL READ COMMITTED SET DEADLOCK_PRIORITY LOW BEGIN TRAN DELETE TOP (@count) C OUTPUT DELETED.[Key], DELETED.[Value], DELETED.[ExpireAt] INTO @RecordsToAggregate FROM [HangFire].[Counter] C WITH (READPAST, XLOCK, INDEX(0)) SET NOCOUNT ON ;MERGE [HangFire].[AggregatedCounter] WITH (FORCESEEK, HOLDLOCK) AS [Target] USING ( SELECT [Key], SUM([Value]) as [Value], MAX([ExpireAt]) AS [ExpireAt] FROM @RecordsToAggregate GROUP BY [Key]) AS [Source] ([Key], [Value], [ExpireAt]) ON [Target].[Key] COLLATE DATABASE_DEFAULT = [Source].[Key] COLLATE DATABASE_DEFAULT WHEN MATCHED THEN UPDATE SET [Target].[Value] = [Target].[Value] + [Source].[Value], [Target].[ExpireAt] = (SELECT MAX([ExpireAt]) FROM (VALUES ([Source].ExpireAt), ([Target].[ExpireAt])) AS MaxExpireAt([ExpireAt])) WHEN NOT MATCHED THEN INSERT ([Key], [Value], [ExpireAt]) VALUES ([Source].[Key], [Source].[Value], [Source].[ExpireAt]); COMMIT TRANand this is the error that I got
Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.