DISABLE_BATCH_MODE_ADAPTIVE_JOINS: Since SQL Server 2017 the SQL Server can delay the decision which join is the correct one for a query until after the first table scan.This can lead to situations where SQL Server massively underestimates the number of rows. This means that it assumes that a filter that filters 50% of the data on the entire table always does so, even if an equivalent filter has already been set previously. ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES: Basically, SQL Server assumes that if there are several consecutive filters, they are independent of each other.Specifically, since version 2014, SQL Server does not necessarily assume that values occurring in a ‘JOIN’ predicate must actually be present in both tables this assumption can be overridden with this hint. ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS: if you change this hint, the way SQL Server estimates the number of rows in JOIN statements.This is now a subset of the following attributes: This system view, added in SQL Server 2017, lists all possible query hints available in your database. If you would like to know which hints are possible on your system, the following query will help you: SELECT The keyword OPTION followed by brackets containing the different options for the query, followed by USE HINT followed by brackets containing the optimizer hints separated by commas. Generally, suggestions or hints for the optimizer are given at the end of the query. Occasionally, however, there are cases where the SQL Server miscalculates, either because of missing or outdated statistics, or because it misinterprets the query, or for some other reason makes an incorrect estimate of the query’s effort (if you want to know more about this, you should visit a PASS Essential by Grandmaster Ricken-San, for example. Normally, when you submit a query, the SQL Server will try to interpret the query and find the best possible execution plan for your query based on statistics, among other things.

Reasons not to use a ram optimizer