Hundreds of sessions queued in suspended state 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..?
It was tempdb contention. Adding more tempdb data files and enabling trace flag 1118 solved the issue.