query plan suggestions
Need some advise on the query plan analysis. There is stored proc within which a dynamic sql is formed and executed and it is taking more time when executed with concurrent user load testing. checking if any suggestions/guidelines/index recommendations or best practices around refactoring the code.
Attaching the query plan and stored proc source code.
Just looking at the execution plan will not cut it. As you write, it perorms good enough on a single user so the plan should look okay.
When it slows down on multiple connections, what is causing it? Set up some monitoring to find out. Are queries being blocked by row locks of other activity? Is the server memory starved and are the connection just waiting for memory before they can even start? Is something causing the query to be recompiled every time instead of using a cached plan and is all the optimization causing too much overhead? Are the queries actually fast but is the network overburdened so that all connections need to wait before they can send data? Is the disk subsystem hammered and if so, what is going on with your buffer pool? Etc etc etc etc