Query with many joins and subqueries slowing the server if more than one execution concurrently

Juliano 2015-10-05 19:45:00

This query is executed in 1 minute and produces 13K rows. The time for execution seems to be acceptable for my client, however it is slowing down the server when more than one user is executing the query (in a stored procedure).

Aaron Bertrand 2015-10-08 19:13:59
This was anonymized using an older version of Plan Explorer, so hard to tell what the query is actually doing, but did you check to see if the other sessions were slowed down due to blocking, or if it is simply insufficient resources? What is the wait type in sys.dm_exec_requests when these things slow down? It will be difficult to tell from one (anonymized) plan what is slowing things down under higher concurrency.
SQLkiwi 2015-10-09 14:38:50
If you do upload a new query analysis file, please run the query directly from Plan Explorer, at a time when you are experiencing poor performance. There's really very little to be said about an estimated old-style anonymized plan, except you're probably running low on resources, as Aaron said.
Juliano 2015-10-09 18:19:00
I've updated the question with the execution plan without anonymizing it. I'm new to Plan Explorer and I don't know if you can see the SQL code that generated the execution plan. If you need it, I can add it to the question, however it is not small.
Aaron Bertrand 2015-11-06 20:54:11
Sorry, just circling back around to this after a two-week trip. The query text is truncated, so we can't see the whole thing, but that's a lot of joins, a lot of compute scalars, and some sorts that are leading to spills (likely due to under-estimates caused by out of date stats). I will suggest changing DISTINCT to GROUP BY, since in some cases the expressions applied to columns will happen first for the former (meaning more work) vs. after filtering for the latter. Also for the (@Member IS NULL OR something = @Member) bits, you might consider watching this video on what I call the "kitchen sink" procedure: https://youtu.be/O0A-QyzaRuk