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.

Paul S
  • 11
  • 4

1 Answers1

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.

user195565
  • 11
  • 2