We have a database that is getting a lot of inserts - sometimes around 1500 per second. We have a bit of software (3rd party) that performs these writes, and it has a buffer. If the SQL server cannot perform the writes fast enough, the buffer queues up the data, and inserts it when it can.
We've found that generally it inserts ok - but if for instance we put a heavy trigger on the table it's inserting to, the SQL Server won't being able to write quick enough, and it queues up - an undesirable scenario.
This is all fine; however, we've found that when we read out of this table at the same time as it's been written too, this appears to cause queuing as well; it seems the SQL server cannot write as quickly when we are reading from the table at the same time.
There are about 30k rows of data in the table - never more, but we need to find a way to read from this table at the same time as it's been written to, without causing SQL to slow down on the inserts. There's a single PK on the data table, and that's got a clustered index on it.
The queries against the table always reference the same PK.
Does anyone know why the reads might be slowing down the inserts, and what we might do about it?