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:
- Number of connections to the backend were reaching the maximum 50.
- There were a number of deadlocks, some processes deadlocking themselves. These locks did however release when I refreshed couple seconds after.
- I ran
sp_who2and it returned 4 cases where an SPID was repeated 25 times (screenshot attached).
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.