I want to optimize this query execution to improve its duration.

SQL DBA 2013-05-28 06:44:52

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 " .

SQLkiwi 2013-05-28 07:29:13
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:

Plan Fragment

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 OPTION (LOOP JOIN) query hint! Nevertheless, the plan produced with that hint should help you identify why the optimizer is choosing Hash and Merge joins instead, and probably lead you in the direction of additional indexing which would make nested loops perform well, if possible.

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 SELECT in a view for whatever reason.

Kevin 2013-06-11 16:11:49
Who knew that SORT operations cause physical disk interactions? I always thought that would be indicated with a SPOOL operation. What's the difference then between SPOOLs and this kind of SORT operation? Thanks,


SQLkiwi 2013-06-16 03:40:01
Hello Mr Kline ๐Ÿ™‚

To make things even more complicated, spools don't always result in physical I/O either! Spools use a worktable in tempdb to optimize certain operations, provide phase separation, or for many other reasons, but like any table the worktable's pages are cached in buffer pool and only written to disk (on a page-by-page basis) if the server needs the memory for something else. There are a few extra subtleties to that but I don't want to write a book in a comment ๐Ÿ™‚

In general, yes, sorts are intended to be performed in memory, but there will always be cases where the memory reserved turns out to be insufficient – either because the server cannot possibly grant all the memory that would be needed or due to an incorrect estimation, as here. In any case sorts need a way to cope with the situation where the memory is too small, and that means spilling one or more sort runs to disk.