Why does this query slow down this much over time

I’m testing with a very simple database queue to see how fast I can process it. At the moment without even doing anything else then just retrieving it from the queue.

A simple CTE query selects one row from the queue and updates it’s status from 0 to 1 to be able to run the script in parallel or on multiple servers. The columns used in the WHERE clause are indexed and the ORDER BY is on the primary key. The queue table has been filled with a million records and status 0.

I run a very simple PHP script from the command line to see how many rows I can roughly handle in 10 seconds before it exits.

When I run this script with a fresh table and all rows have status 0, I can handle about 7000 requests in 10 seconds. Every subsequent execution without resetting the queue will make it slower and slower as time goes on. If I manually update 500000 rows to status 1 and run the script, it will only take about 50 per 10 seconds. Manually resetting all rows to status 0 will increase the speed again.

I have no idea what I could do to optimize this as indexes are in place, hardware is sufficient and query is about as simple as can be.

I’m using SQL Server on Windows Server 2022 and Ubuntu 24.04 for a simple PHP script are running on two separate VM’s on Proxmox with 32 cores and 32 GB ram provisioned to each server. Neither server every exceeds 10% CPU or 25% ram. The physical server has 256GB ram, 96 x AMD EPYC 9454P 48-Core Processor (1 Socket) and NVME SSD disks. Network between them is around 12 Gbits/sec according to iperf3.

The is my table’s definition:

CREATE TABLE [dbo].[testqueue] (
[id] [bigint] IDENTITY(1,1) NOT NULL,
[guid] [uniqueidentifier] NOT NULL,
[created] [datetime] NOT NULL,
[notbefore] [datetime] NOT NULL,
[status] [tinyint] NOT NULL,
[task] [nvarchar](255) NOT NULL,
[data] [nvarchar](max) NULL,
[completed] [datetime] NULL,
CONSTRAINT [PK_testqueue] PRIMARY KEY CLUSTERED ([id] ASC)
)
CREATE INDEX [ix_guid] ON [dbo].[testqueue] ([guid])
CREATE INDEX [ix_selector] ON [dbo].[testqueue] ([status],[notbefore])

And this is my PHP script:

$connection = new PDO('sqlsrv:Server='.$server.';Database='.$db.';', $user, $pass);
$connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$time  = time();

$count = 0;
$query = '
  WITH CTE AS (
    SELECT TOP 1 * 
    FROM [dbo].[testqueue] 
    WHERE [status]=0 
    AND [notbefore]<=getdate() 
    ORDER BY [id] ASC
  )

  UPDATE CTE 
  SET [status]=1
  OUTPUT INSERTED.id
';

$statement = $connection->prepare($query);
do {
  if ($statement->execute()) {
    //do something later
  }
  $count++;
  if (time() - $time > 10) {
    break;
  }
} while (true);

The execution plan doesn’t show any warnings for indexes and says 25% goes to a Clustered index Scan and 75% to a Clustered Index Update.