2

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.

  • SQL version? Reads are on primary? How frequently is your data changing? Is it all queries that are having problems? Have you considered it to be a parameter sniffing issue? For the tables that you are rebuilding stats on, how many rows have changed at the point at which things get sick? Are you using async stats rebuilds? – Nic Mar 2 '17 at 16:18
  • @Nic 2012 SP3, updated the OP. The reads on primary are minimal and limited only to the users entering orders - roughly 20 users on this particular system. The data only changes during business hours, 8:00 AM to 5:00 PM. I'll need to research into parameter sniffing. In today's case, only ~20 rows were added to the primary order table and < 50 rows combined in related tables. We are not using async stat rebuilds. – PicoDeGallo Mar 2 '17 at 16:24
  • Instead of updating statistics, have you tried recompiling? What is the difference in execution plans from when it is timing out and when it is fine? You may want to use a plan guide. – Tara Kizer Mar 2 '17 at 16:29
  • @TaraKizer I have tried recompiling, and typically run DBCC DROPCLEANBUFFERS and DBCC FREESYSTEMCACHE ('ALL') WITH MARK_IN_USE_FOR_REMOVAL after I run the statistics updates. The execution times differ only < 1s from what I have observed when I manually run them. That's what is even more-so perplexing - the fact the same stored procedures are able to be manually called, but when call by the application, they time out. – PicoDeGallo Mar 2 '17 at 16:36
  • Have you looked at the connection settings differences between the manual calls and those through the app? Grabbing the plan for those that are bad, and then comparing to the good plan you get when running manually might provide some additional clues (especially looking at the parameters that are cached for the problem children) – Nic Mar 2 '17 at 18:07

Your Answer

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

Browse other questions tagged or ask your own question.