We are using SQL Server 2008 R2 on a Windows Server 2008 server.
We have some main functions which uses some Stored Procedures which has a lot of sub-queries inside of it.
I'll take one SP as an example; when running this SP sequentially it takes about 500ms, and we are ok with it (kinda), it has mainly select queries, some on the same table, it also has some temp tables created and filled with another SP.
We are now in the process of testing these main functions under multiple concurrent requests, and we found out right away that the SP's completion time jumps really quickly to seconds and rising...
after using some suggestions, I noticed that more than one spid is associated with multiple S locks, and that some processes are blocking themselves.
other than that, I'm uncertain as to what is making performance so poor.
I'm not aware if this description is too vague, I would love to give more information if needed, in the end I would like to know where you think should I look at in order to solve this problem.
Here is a portion of the execution plan, it doesn't look good...
Should I just add the indexes as specified?
what does all the parallelism sections in some of the queries' execution plan mean?
does this explain the performance problem?