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 Server 2012 SP3. 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.

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.

1
  • This is an old post but I suggest you try to capture the actual HTTP call via fiddler and then try to replay it using postman or SOAPUI. Just want to see if it makes any difference if you get the same error using the same HTTP call but from different source app.
    – Jason
    Dec 2, 2021 at 2:18

1 Answer 1

0

It runs fast manually when the app is timing out on the same call because you have a different execution plan than the app.

You have different SET options in SSMS than the app does.

See Slow in the Application, Fast in SSMS? by Erland Sommarskog - Tara Kizer

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.