I have a Microsoft Sql Server that handles a moderate amout of traffic for a company software suite made up of multiple clients in java and c#.
It is getting overloaded with 'Suspended' queries. They eventually go through, but some are timing out (especially the writes). The queries are mostly reads.
Is there any way to speed this up, or fix it some other way?
Here is a picture of my Activity Monitor to show what I'm talking about:
Side note: We have planned on moving to MySql for some time now. Would that help at all? I know MS Sql is strong but with it's default of Read-Committed, could that be hurting anything?
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: