We are running SQL Server 2016 and have a very simple stored procedure that does a select using a clustered index seek of a single row in a table with 140 million rows. In that table, we keep around the last 6 months of data, with roughly the same number of rows per day. On a daily basis, we delete the oldest day and insert the most recent day. The select stored procedure has isolation level set to read uncommitted (which I understand has its own issues, but we felt the risk of blocking was greater than the risk/likelihood of reading incomplete data).
Recently, the select query has been timing out at 30 seconds (a timeout value we set - not sure how long the query would have actually taken). The issue ultimately resolves itself anywhere between 30-120 seconds later.
I explored various possible causes, but my current theory is that the query optimizer detects out of date statistics, triggering a stats update on the table, which it waits to complete, and then creates an execution plan once the stats have been updated. This was confirmed by looking at the date/time the stats were last updated, which coincides exactly with the query slowdown.
Side note: the first time this issue occurred, it coincided with our daily insert of data. The second time it occurred, it didn't seem to coincide with the insert or delete. But both times, the slowdown coincided with a stats update. I'm not sure why the stats became outdated the second time.
I see a couple options here:
- set auto stats update async so that the query optimizer doesn't wait for the stats update to complete
- disable auto stats update and manually update stats nightly (would also probably specify a query hint in the stored procedure to use the clustered index, but not sure if that's necessary).
So, does my theory for the cause sound reasonable?
Which of my options is best?
Are there other options?