0

There were reports of slowness on our web application - screens hanging, some unable to log in. It's an OLTP system with approx 50 concurrent users. Upon investigation I found a few things:

  1. Number of connections to the backend were reaching the maximum 50.
  2. There were a number of deadlocks, some processes deadlocking themselves. These locks did however release when I refreshed couple seconds after.
  3. I ran sp_who2 and it returned 4 cases where an SPID was repeated 25 times (screenshot attached).

enter image description here

Am I correct in saying this is a result of parallelism? If so, is one obvious fix to amend our MAXDOP to a suitable setting and possibly change the threshold cost to higher than 5 second (server has 2 numa nodes, 24 threads total)?

If so, I'm thinking one drawback of this would be that overall performance on a day to day basis might be affected, as all the small queries will be using less resources and only the larger ones will get use of the full resources.

Please correct me if I'm wrong on that, I'm just looking to see if this change is advisable for an OTLP system and am looking to see possible negative impacts.

Improve this question
2
  • Please use sp_whoisactive from Adam Machanic instead of sp_who2. Do you have deadlock graph? Can you please include that as well.
    – Learning_DBAdmin
    Commented Nov 21, 2019 at 19:45
  • Parallel queries in a pure OLTP workload are often due to inattention to query and index tuning detail. In addition to reducing maxdop and cost threshold, view the execution plans for optimization opportunities.
    – Dan Guzman
    Commented Nov 22, 2019 at 2:03

1 Answer 1

Reset to default
1

Answering this question:

Am I correct in saying this is a result of parallelism?

Yes. Activity Monitor and sp_who2 display one row for each thread (execution context) involved. It is unnecessarily verbose and confusing, but Microsoft have not responded to requests to improve Activity Monitor. It is even less likely that the (technically undocumented sp_who2) will be updated.

Adam Machanic's sp_WhoIsActive is a much better alternative.

There is not enough information in the question to address your other points, but it does seem you might be running with an excessive degree of parallelism, possibly the default of zero (= 24 for your configuration).

Setting max degree of parallelism to 8 might be about right. See MAXDOP setting algorithm for SQL Server for detailed advice.

And, as Dan Guzman said:

Parallel queries in a pure OLTP workload are often due to inattention to query and index tuning detail. In addition to reducing maxdop and cost threshold, view the execution plans for optimization opportunities.

Improve this answer
2
  • Thanks a lot for your detailed feedback. I will use 'sp_WhoIsActive' from now on, looks really helpful. Let's say I set MaxDop to 8 and CT to 20 seconds, I'm wondering what will happen to my queries that run less than 20 seconds, and if they might now run slower. How many threads will be used to process them? Would a performance hit be expected on them?
    – Paul S
    Commented Nov 22, 2019 at 15:43
  • Kindly ask a new question about your above concern with relevant details and someone from forum will answer it. Moreover if above answer worked for you, please accept it.
    – Learning_DBAdmin
    Commented Nov 24, 2019 at 9:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.