We have been experiencing an odd behavior in our application where various modules will begin to timeout in SQL Server 2012. Each time we stumble across this issue, we find that the statistics require update and that running
exec sp_updatestats fixes the issue. After updating index statistics, the timeouts go away.
However, the frightening issue is the frequency in which we have been experiencing the need to update the statistics, and the fact that nearly all of the statistics are showing a need to be updated across all of our tables related to order processing. Due to the frequency, we have setup a job to run
sp_updatestats prior to business hours at 7:30 AM.
This seemed to have calmed the issue while we continued to investigate until it occurred again today. Not 10 minutes after business opening, they were receiving timeouts. I immediately ran
sp_updatestats and the timeouts disappeared and application function returned to normal. The order volume since business opening was low (less than 20 rows added to the primary order table), yet the statistics became so bad between 7:30 AM and 8:10 AM that we began experiencing time outs.
A couple of notes:
- This behavior is reproducible on QA environments (but is completely random), and we have been trying to narrow down the cause when it does occur but have not had any success identifying the issue yet.
- WAIT STATS are close to perfect - contention with tempdb is minimal with very few instances of PAGELATCH_* waits showing.
- Auto statistics updates is set to on.
- We have a nightly maintenance plan that rebuilds or reorganizes indexes based on fragmentation level.
We are running two servers, a PRIMARY and REPLICA within an AlwaysOn Availability Group. The VMs are housed on Azure and have 8 logical processors, 28GB of memory, and all SSD drives. The data file layout looks like:
- E: - tempdb/templog - we have eight 8GB files with no auto growth set.
- F: - SQL data files. Each tenant is split into its own file group (.ndf).
- G: - SQL log file.
Has anyone experienced behavior such as this, or know what could be causing index statistics to go awry so quickly across so many tables? Any recommendations for diagnosing next time this occurs? Manually running the stored procedures and diagnosing the execution plans have proved rather unhelpful as the statistics its reading (Expected/Actual Rows) are the same.