0

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:

  1. set auto stats update async so that the query optimizer doesn't wait for the stats update to complete
  2. 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?

7
  • What DBMS is this about? Please tag your question appropriately. – mustaccio Sep 17 '20 at 19:40
  • Thanks! Noob mistake. :) – Kenny Vu Sep 17 '20 at 20:55
  • is the select runnning on a scheduled or ad hoc basis? – SEarle1986 Sep 17 '20 at 23:24
  • also, there is an extended events event called auto_stats which fires when stats are loaded. you can filter this on status to see when they update. You may also want to look into the query_post_execution_plan event but if this is a production server TEST THIS ELSEWHERE as there is some overhead. This will show the actual execution plans of the queries that have run so you can find the one for the problematic execution and investigate further. however rather than the showplan XE above, it would be better to use sp_whoisactive @get_plans = 1 when the slow query is running, if possible. – SEarle1986 Sep 17 '20 at 23:45
  • Further info on show plan XE kapa-consulting.sk/… – SEarle1986 Sep 17 '20 at 23:47
1

So its not that you get a bad plan, your issue is that you have somebody becoming a victim of the auto-stats, right?

Since you probably know what table(s) we're talking about, I suggest you disable auto-update at the table (or even stats) level. You can do this using sp_autostats. Now you don't affect the rest of the database. And of course handle the stats update on this stats yourself.

This assumes that you get OK plans even when stats are a bit out of whack. If not, then async can be an option, but you won't have control over when the load of this stats update will take place.

I wouldn't go the hint route unless you identify that you indeed get bad plans with old stats. So consider the plan quality a different aspect. They are related of course, but out-of-date stats doesn't necessarily mean bad plans.

If it is helpful, you can capture when auto-update stats happens with an XE trace. Either just count how many you have over a day, say. That wuuld be using the Event Counter XE target. Or capture the actual event (to a file target) if you need more details than "I had this many in total over the time of the trace running".

For instance, I have my "Looking for strange" trace for things like this (just counting how many - then modify the trace as you want to limit the events and/or add a file target).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.