It looks like the first plan is the only plan using parallelism. There are some queries that when they use parallelism, the plan is built by sql to try and load a bunch of data from big tables into memory in parallel. This can sometimes result in a bad plan built because the statistics on the tables force the plan optimizer to grossly overestimate or underestimate certain operations. This is evident in your query since the in the first query, there is an excessive memory grant warning of over 3.5GB memory estimated to be used, and an actual memory usage of only 86MB.
You could try a couple different things or a combination of them.
- Add OPTION (MAXDOP 1) at the end of your query to remove parallelism for the execution of the query.
- Rebuild statistics on all tables.
- Further optimize your query
An example of further optimization would be that you have an inner join to the AccountsFundedDates table, where you select none of those columns or use that table as a linkage to join other tables to. There are some circumstances, such as when columns being joined on are not unique, that writing them as an EXISTS statement below in the WHERE clause can be faster than a join, as doing so can potentially reduce the total number of rows being built in memory by eliminating duplicates prior to the final select. I don't know if this specifically applies to you, but something to consider.
...
WHERE a.AccountStatus = @accountStatus_Active
AND EXISTS
(
SELECT 1
FROM dbo.AccountsFundedDates f
WHERE f.AccountId = a.AccountID
AND f.DateFunded IS NOT NULL
)
AND a.AccountType NOT IN ( @accountType_Internal, @accountType_Test )
...
You asked why it was different with rearranged joins or ran on a different server - If you change the joins around, sql treats it as a new query and builds a new plan for it with the statistics and information is has available to it, same as how the original query on a different server generated a non-parallel plan as well. A different first execution with different stats or different resources available will result in a plan that sql thinks is best at the time, even if it's bad.
