Hi , I have been working on this query plan from past 3 but days but I am not able to figure out what is the problem with this view. I have indexes built on all join columns as well as everywhere I see index seek in the execution plan. Also when I select properties of select operator it shows " Reason for early termination of statement optimization - TimeOut " .
By SQL DBA 16 asked May 28, 2013 at 06:44 AM
The most obvious causes for concern in this anonymized plan are the Sort operators. The query processor reserves memory for sorts based on the expected size of the data to sort; if the amount of memory reserved turns out to be too small, sort data has to be written to physical tempdb disk. This is known as a 'spill', and one example from your plan is shown below:
In this case, 3MB of data was expected, but 110MB was encountered. The underlying cause is an inaccurate cardinality estimation at some earlier point in the plan. With such a complex query with so many joins, inaccurate cardinality estimation is probably unavoidable, though you should certainly check that you are providing all the statistics the optimizer needs, including manually creating any useful multi-column statistics.
The sorts are required in this case because Merge Joins require sorted inputs. You might think to use Hash Join instead, but that type of join also requires a memory grant for the hash table, so spills can and do occur with that join type as well.
Nested Loops join does not require memory, so you could look at achieving that join type instead, though it will require more work than just adding an
Ultimately, the query is too deep (too many joins) to expect accurate cardinality estimation. I would normally recommend splitting the query into parts, at the point where cardinality estimation errors creep in, but you may be limited in what you can do if the query must remain a single
By SQLkiwi ♦ 6.6k answered May 28, 2013 at 07:29 AM