This is a very general question so I don't give much specific information. I am more interested in parallelism and its possible drawbacks. Generally I am all for parallelism as something useful and positive.
I seem to come across more and more systems that have one or more stored procedure that have issues with too much parallelism. Is there a good way to find out if that really is the problem or if it is something else lurking about?
Usually the sql is a bit complex with maybe 5 or more joins and also some udf and even more complexity. So far I found these issues by examining everything from usual performance issues but also from more extreme situations with error messages like "Could not create thread..." and also deadlocks from multiple simultaneous copies of the same store procedure.
I am really curious how you guys do to find issues like these and also how you go about solving them. Recoding? Playing with
MAXDOP (max degree of parallelism)? And so on...