MS Sql Server getting overloading with 'Suspended' queries, mostly reads. Any way to fix this?

Solution 1:

The wait types shown in your screenshot are:

• LCK_M_X - Occurs when a task is waiting to acquire an Exclusive lock.
• CXPACKET - Occurs with parallel query plans when trying to synchronize the query processor exchange iterator. If waiting is excessive and cannot be reduced by tuning the query (such as adding indexes), consider adjusting the cost threshold for parallelism or lowering the degree of parallelism.

(Both of these were C&Ped from MSDN.)

MSDN suggests tuning your queries and/or adding indexes above. That's probably a better first step than just junking SQL Server and switching to something else. (If the problem is your queries, well, they'll still be inefficient on MySql, IMHO.) So, I'm going to recommend looking at Brent Ozar's wait stats resources and looking for things like the top ten expensive queries. Hopefully that'll point you in the right direction.

Solution 2:

Ok, figured out the issue.

The default settings of MS Sql use all cores of the server to execute large queries. We have large reads so it was stacking up a long line of queries waiting to be processed.

To fix it, I set the Max Degree of Parallelism to be 2 cores instead of unlimited (8 in our server) and I set the Cost Threshold of Parallelism to 10 (seconds) from 5, which is the time at which the parallelism will kick in for queries.

Both can be found in Sql Server Management Studio by right-clicking the server and choosing Properties, and then clicking on the Advanced tab.

I found this article which explained and solved the issue:

http://www.brentozar.com/archive/2013/08/what-is-the-cxpacket-wait-type-and-how-do-you-reduce-it/