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..?
2
0
It was tempdb contention. Adding more tempdb data files and enabling trace flag 1118 solved the issue.
sp_WhoIsActive
say? Or, at least, what isActivity Monitor
telling about wait types? – Marcin S. Oct 21 '16 at 14:08