SQL Server Query Optimizer performs a very good job in the generation of execution plans. However, due to query complexity, skewed data distribution, suboptimal database design and badly written code, it makes sometimes very bad cardinality estimations that lead to slow performing execution plans. Because of wrong estimations, it can choose inappropriate plan operators (for instance Nested Loop Join instead of Hash Join), or a query can get significantly more or less memory granted for the execution than it is required. Sometimes, it simply assumes a fixed cardinality of 100 or 1.
SQL Server 2017 introduces query processing improvements that will adapt optimization and address the aforementioned ...
