I have a table that is being used as a stack. There are around 200 connections to the server, any one of those connections, at any given time, may insert a record into the table. The table is highly volatile, records are being inserted and deleted all the time. It rarely contains more than 15-20 at any given time.
I have a process that runs every second. It connects to the server, runs a select on my stack table, and disconnects.
For years this has been running fine, with the process taking < 0.5 seconds to pull a record from the table. Of late, I am noticing intermittent timeouts (frequent) on this select. Any suggestions on where I can start troubleshooting this?
This is the select statement (the "id" column is a decimal, everything else is nvarchar):
SELECT TOP 1
@T_ID = id,
@T_TYPE = [type]
machinenum = @T_MachineNum
datetimepr <> 'INPROGRESS'
[date] = replace(convert(char(8),getdate(),11),'/','')
[time] <= replace(convert(char(8),getdate(),8),':','')
priority desc, [time] asc
And attached are the results of subsequent statistics running the same query.
The server is an AWS instance. The client that's having the problem is another AWS instance in the same subnet.
If I simply run "select * from stack with(nolock)" — I get a consistent return in about 420 ms. Always, from everywhere, at any time. As soon as I add a where clause to it — I get the problem described above.