query plan suggestions

Hi All,

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.

Thank you. link text

files.zip (85.2 kB)
avatar image By Maahi 1 asked Dec 21, 2017 at 08:30 PM
more ▼
(comments are locked)
10|10000 characters needed characters left

1 answer: sort voted first

Since the issue is specifically related to multi-user testing, you will first need to identify what the bottleneck is that is causing the query to slow down.

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

avatar image By Hugo Kornelis 271 answered Dec 21, 2017 at 09:01 PM
more ▼
(comments are locked)
avatar image Maahi Dec 22, 2017 at 03:13 AM

Thanks Hugo.

10|10000 characters needed characters left
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

We are Moving!


Follow this question



asked: Dec 21, 2017 at 08:30 PM

Seen: 60 times

Last Updated: Dec 22, 2017 at 03:13 AM