2

We experience hundreds of sessions going in suspended state on rare occasions.
When it happens there is no increase in terms of resource usage - CPU or memory. Rather there is a small amount of decrease 5-10% on CPU.
There is no single error on the SQL log.
I know this will be a blind question but what might be causing this? Any suggestions to check in SQL Server like plan cache etc..?

|improve this question
  • What are they waiting on? Sounds like a blocking chain. – Martin Smith Oct 21 '16 at 14:03
  • 1
    What does sp_WhoIsActive say? Or, at least, what is Activity Monitor telling about wait types? – Marcin S. Oct 21 '16 at 14:08
  • There should be a spid that must have been blocking another one and so on . Find that parent blocking spid and check what is it waiting for. That should give you an idea – karun_r Oct 21 '16 at 14:09
  • Foremost wait type is PageLatch_Up: 400 minutes of waiting over 1 minute! Second wait type is PageLatch_Sh: 166 minutes of waiting over 1 minute – Stackoverflowuser Oct 21 '16 at 14:43
  • My monitoring tool confirms there were no blocking sessions. – Stackoverflowuser Oct 21 '16 at 14:44
0

It was tempdb contention. Adding more tempdb data files and enabling trace flag 1118 solved the issue.

|improve this answer

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.