Help with plan
This query is generated dynamically inside the SP.
Any chance to improve the performance without creating extra indexes.
I assume when you say "without creating extra indexes" that you realize that the heaps in this database are a major source of the performance problems (i.e. the RID Lookups) with this query, correct? A side thought, are the temp tables your own and, if so, can you index them? That may also help.
Here are some more thoughts:
- Why do you have TWO references to "count(1)" in the SELECT item list? Are they both necessary? Can you make do with one or even none?
- You didn't provide a table alias prefix in sum(VALVAL_ALT_CMB_AMT). Which table is that coming from? Believe it or not, SQL Server will have to spend cycles to figure that out when you don't declare it.
- The ORDER BY generates a ton of IO in tempdb, about 87Mb. The ORDER BY might be a requirement. But if it's something the client app already does or could do, you might save some work by removing it.
- JOIN versus WHERE: The first 4 items of the WHERE clause, plus the very last item, look like they should logically be in a JOIN clause. As it stands right now, there should not be a performance improvement by keeping only the remaining 2 items of the WHERE clause where they currently are outside of the fact that SQL Server may use that information to decide in what order to perform the join operations. It is possible that keeping the A.INQ_BASIS_NUM=1 as one of only two main WHERE clause search arguments that SQL Server will see that it can significantly cull that result set BEFORE performing its very IO intensive merge join operations
- Once you've implemented #4, you might further experiment with the FORCE ORDER hints so that SQL Server evaluates A.INQ_BASIS_NUM=1 first and A.AS_OF_TMS = '2016-03-23 23:59:59.000' second to greatly limit the total data managed throughout overall computation of the various plan operations. Again, that'll trim the result sets very early in query processing greatly reducing the total IO and memory utilized.
- Update your statistics. They seem badly skewed.
- Make sure tempdb is fast – you're doing a lot of spools and spills.
Hope that helps,