Any idea why the filter limiting to 5K instead of 320K rows is done at the end ?

jdt 2014-03-21 09:40:42

Bascialy the filter is correct as it limits to 5K rows the 320K rows available but as ity's don at the end instead of doing it at the begining of the process, SQL SERVER manipulates 320K rows including nvarchar fields and needs more than one GB of ram to do that

SQLkiwi 2014-03-22 00:20:56
It's hard to know what the predicate "ScalarString12" is in an anonymized plan. Generally, the query optimizer is good at pushing predicates as far down the tree as possible – I can only imagine it has a good reason not to do that here. Perhaps the predicate references a result from the right outer join? Provide better problem information if you can, and I'll look at providing a more useful answer 🙂