Forced PK_NewTasks index hint in _LockNextTask causes high CPU under concurrency

[berndverst]

Summary

The dt._LockNextTask stored procedure contains a hard-coded index hint WITH (INDEX (PK_NewTasks)) that forces SQL Server to use the clustered primary key index for the activity task dequeue UPDATE. Under high concurrency and sustained task backlogs, this hint forces a suboptimal execution plan that causes disproportionate CPU consumption and query latency on Azure SQL, even after scaling compute.

Problem Details

The forced hint

In logic.sql, the _LockNextTask procedure contains:

UPDATE TOP (1) NewTasks WITH (READPAST)
SET ...
FROM NewTasks WITH (INDEX (PK_NewTasks))
WHERE
    [TaskHub] = @TaskHub AND
    ([LockExpiration] IS NULL OR [LockExpiration] < @now) AND
    ([VisibleTime] IS NULL OR [VisibleTime] < @now)

The comment says: "The PK_NewTasks hint is specified to help ensure in-order selection."

Why the hint is problematic at scale

PK_NewTasks is defined as PRIMARY KEY (TaskHub, SequenceNumber) -- a clustered index. The WHERE clause filters on LockExpiration and VisibleTime, which are not in this index. The forced hint makes SQL Server evaluate these as residual predicates by scanning through clustered index rows until it finds one matching TOP (1) with READPAST.

With a backlog of locked or invisible tasks, this means scanning past many ineligible rows on every call. The existing IX_NewTasks_InstanceID index already INCLUDEs both LockExpiration and VisibleTime, but SQL Server cannot use it because of the forced hint.

Why the hint does not achieve its goal

The hint intends to ensure FIFO (in-order) activity task selection by SequenceNumber. However, UPDATE TOP (1) with READPAST is inherently non-deterministic -- SQL Server returns the first unlocked row it encounters, which may not be the lowest SequenceNumber. The hint provides a soft tendency toward ordering but not a guarantee, while forcing a costly plan.

Polling amplification

Each worker runs LockNextTaskActivityWorkItem in a tight loop with default settings:

• MaxConcurrentActivities = Environment.ProcessorCount
• MinActivityPollingInterval = 50ms
• Backoff resets immediately when a task is found

With a sustained backlog, backoff never engages. A deployment with N workers x M cores creates NxM concurrent polling threads, each calling _LockNextTask at up to 20 times/sec. This can easily produce hundreds of executions per second, all hitting the suboptimal forced plan.

Comparison with _LockNextOrchestration

The companion procedure _LockNextOrchestration uses UPDATE TOP (1) Instances WITH (READPAST) with a join to NewEvents -- without any forced index hint. This proves the pattern works correctly without the hint.

Proposed Fix

Option 1: Remove the hint (minimal change)

-- Before:
FROM NewTasks WITH (INDEX (PK_NewTasks))

-- After:
FROM NewTasks

This lets SQL Server choose the optimal plan based on current table statistics, consistent with _LockNextOrchestration.

Option 2: Add a covering index (performance optimization)

In addition to removing the hint, add a new index that covers the WHERE clause predicates:

CREATE NONCLUSTERED INDEX IX_NewTasks_LockNext
ON dt.NewTasks (TaskHub, LockExpiration, VisibleTime)
INCLUDE (SequenceNumber, LockedBy, DequeueCount)

This gives the optimizer an efficient path to find unlocked, visible tasks without scanning the clustered index.

Customer Workaround

Until a fix is released, customers can safely apply the following workaround:

1. Retrieve the current procedure definition:

   SELECT OBJECT_DEFINITION(OBJECT_ID('dt._LockNextTask'))

2. Re-create the procedure with the hint removed -- copy the full definition from step 1, replace FROM NewTasks WITH (INDEX (PK_NewTasks)) with FROM NewTasks, and execute the modified CREATE OR ALTER PROCEDURE.

3. Monitor Azure SQL CPU, _LockNextTask duration, and logical reads via Query Store for 24-48 hours.

4. Re-apply after NuGet upgrades -- the durabletask-mssql package uses CREATE OR ALTER PROCEDURE during schema create/upgrade, which will restore the original procedure. Re-apply the patch after any package upgrade.

Related Issues

• Missing Index #142 -- Missing index recommendations (Azure SQL recommends indexes that the forced hint prevents from being used)
• Change task scheduling to give more even flow of orchestrations #220 -- Task scheduling fairness discussion (directly involves _LockNextTask behavior)
• SqlException Transaction (Process ID 66) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. #193 -- Deadlock in ReadEntityStateAsync (separate stored procedure, but related SQL concurrency pattern)

[cgillum]

Thanks @berndverst for opening this and generating the analysis. I'm taking a look at the recommendations. The original reason for this hint was as a fix for persistent deadlocks in activity processing. Prior to this hint, concurrent scans for activity work items would do scans on this table in a non-deterministic way such that deadlocks were common under load. By forcing the query plan to always take the same route (thus taking all locks in the same order), deadlocks disappeared. This is why I'm hesitant to simply remove the hint - it might make the problems at scale worse. I'll do a follow-up to see whether the AI analysis agrees with this, and/or if there's a solution which won't introduce a new deadlock problem.

/cc: @sophiatev @torosent

[berndverst]

Based on initial testing by @cgillum it appears safe to remove the index hints - however this does not seem to improve the performance in our testing.

You can try the following:

 -- Backport of microsoft/durabletask-mssql PR #313
 -- Removes the forced PK_NewTasks index hint from _LockNextTask and
 -- adds a covering index IX_NewTasks_LockNext to support efficient
 -- seek-based lookups under high concurrency.
 --
 -- Safe to re-run. Run against your Durable Task SQL Server database.
 -- If you customized the schema name in DurableTaskOptions.SchemaName,
 -- change the value of @SchemaName below (default is 'dtm').
 
 SET NOCOUNT ON;
 SET XACT_ABORT ON;
 
 DECLARE @SchemaName sysname = N'dtm';
 DECLARE @qSchema    nvarchar(258) = QUOTENAME(@SchemaName);
 DECLARE @sql        nvarchar(max);
 
 -------------------------------------------------------------------------------
 -- 1) Create the covering index IX_NewTasks_LockNext on <schema>.NewTasks
 -------------------------------------------------------------------------------
 IF NOT EXISTS (
     SELECT 1
     FROM sys.indexes
     WHERE object_id = OBJECT_ID(QUOTENAME(@SchemaName) + N'.NewTasks')
       AND name = N'IX_NewTasks_LockNext'
 )
 BEGIN
     SET @sql = N'
         CREATE NONCLUSTERED INDEX IX_NewTasks_LockNext
         ON ' + @qSchema + N'.NewTasks (TaskHub, LockExpiration, VisibleTime)
         INCLUDE (SequenceNumber, LockedBy, DequeueCount);';
     EXEC sp_executesql @sql;
 END;
 
 -------------------------------------------------------------------------------
 -- 2) Replace _LockNextTask without the forced PK_NewTasks index hint
 -------------------------------------------------------------------------------
 SET @sql = N'
 CREATE OR ALTER PROCEDURE ' + @qSchema + N'._LockNextTask
     @LockedBy varchar(100),
     @LockExpiration datetime2
 AS
 BEGIN
     DECLARE @TaskHub varchar(50) = ' + @qSchema + N'.CurrentTaskHub()
     DECLARE @now datetime2 = SYSUTCDATETIME()
 
     DECLARE @SequenceNumber bigint
 
     BEGIN TRANSACTION
     -- *** IMPORTANT ***
     -- To prevent deadlocks, it is important to maintain consistent table access
     -- order across all stored procedures that execute within a transaction.
     -- Table order for this sproc: NewTasks --> Payloads
 
     -- Update (lock) and return a single row.
     -- TODO: Filter out tasks for instances that are in a non-running state (suspended, etc.)
     UPDATE TOP (1) NewTasks WITH (READPAST)
     SET
         @SequenceNumber = [SequenceNumber],
         [LockedBy] = @LockedBy,
         [LockExpiration] = @LockExpiration,
         [DequeueCount] = [DequeueCount] + 1
     FROM
         NewTasks
     WHERE
         [TaskHub] = @TaskHub AND
         ([LockExpiration] IS NULL OR [LockExpiration] < @now) AND
         ([VisibleTime] IS NULL OR [VisibleTime] < @now)
 
     SELECT TOP (1)
         [SequenceNumber],
         [InstanceID],
         [ExecutionID],
         [Name],
         ''TaskScheduled'' AS [EventType],
         [TaskID],
         [VisibleTime],
         [Timestamp],
         [DequeueCount],
         [Version],
         (SELECT TOP 1 [Text] FROM Payloads P WHERE
             P.[TaskHub] = @TaskHub AND
             P.[InstanceID] = N.[InstanceID] AND
             P.[PayloadID] = N.[PayloadID]) AS [PayloadText],
         DATEDIFF(SECOND, [Timestamp], @now) AS [WaitTime],
         [TraceContext],
         [Tags]
     FROM NewTasks N
     WHERE [TaskHub] = @TaskHub AND [SequenceNumber] = @SequenceNumber
 
     COMMIT TRANSACTION
 END';
 
 EXEC sp_executesql @sql;