Sunday, April 12, 2020

SQL Server query hints which are not much popular but very powerful

I learned some new query hints which we can use when troubleshooting query performance issues. Those are mentioned below;

Option (force order, hash join) - forces the query to use follow the join order as it specifies in the query and also makes all the joins as hash joins

Option (Use hint ('ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS')) - to generate a query plan using the simple containment

Option ( Hash join) - to eliminate the merge join

Option ( force_Legacy_cardinality_estimation) - Force to use the legacy cardinality estimator

Option (Use hint ('ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS')) and (Force order ) - combine two query hints

References
Join containment assumption in the New Cardinality Estimator degrades query performance in SQL Server 2014 and later

Update introduces USE HINT query hint argument in SQL Server 2016

No comments:

Post a Comment