5

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:

https://i.stack.imgur.com/CizDS.png

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?

Improve this question

2 Answers 2

Reset to default
3

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.

Improve this answer
5
  • 1
    I understand what you're saying, but would MySql remove that wait for an exclusive lock in the first place? That's what it seems to be with the research I've done.
    – AAA
    Feb 26, 2014 at 21:40
  • 1
    We are moving to AWS RDS at some point anyways. So I would rather switch to MySql and not pay the RDS MS extra costs. Question is whether to invest the time and money now or later.
    – AAA
    Feb 26, 2014 at 21:41
  • 1
    LCK_M_X is a minority of your waits, at least in the information given. I don't think Read-Committed is causing the CXPACKET waits. As to whether to invest time and money now or later, I don't think you've provided enough information for me to say, and I also think there's a subjective/business side to that.
    – Katherine Villyard
    Feb 26, 2014 at 21:50
  • 1
    Right. Wasn't really asking for an opinion on the move. Sorry if that was confusing. Was more asking if a move would help with the exclusive locks problems. But I think looking at the top expensive queries is where I could start for now.
    – AAA
    Feb 26, 2014 at 22:07
  • 1
    I just ran into this great description of CXPACKET and thought of your question. brentozar.com/archive/2013/08/…
    – Katherine Villyard
    Mar 3, 2014 at 15:02
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/

Improve this answer
1

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .