Start auditing query hints in modules and Query Store, rank the busiest statements, and test removal with measured plans and rollback.
Disable Row Goal in SQL Server: When the Hint Helps and When It Hurts
To disable row goal for one query, add the DISABLE_OPTIMIZER_ROWGOAL hint to it. This post builds a table where the row goal chooses a bad plan, shows the reads with and without the hint, and then shows a table where the row goal wins.
Multiple OPTIMIZE FOR Hints in One Query: Value or UNKNOWN
Multiple OPTIMIZE FOR hints in one query are written as one list: each parameter gets its own value or UNKNOWN. This post compiles the same query six ways, reads the compile values and estimated rows from the plan cache, and shows which combinations SQL Server accepts.
FORCE_DEFAULT_CARDINALITY_ESTIMATION: A Simple Explanation
FORCE_DEFAULT_CARDINALITY_ESTIMATION is a query hint that gives one query the default cardinality estimator, even when the database is set to the legacy one. This post builds a demo table, compares the row estimates of both models with the real count and shows how to prove which model a plan used. It also names the opposite hint and the permission difference.
How to Force Index on a SQL Server Query? – Interview Question of the Week #281
Question: How to Force Index on a SQL Server Query?
Answer: I personally do not like to force an index on any query. Let us learn together.
SQL SERVER – SQL_NO_CACHE and OPTION RECOMPILE Target Different Caches
OPTION (RECOMPILE) and SQL_NO_CACHE concern different cached objects. A banking customer using MySQL and SQL Server prompted my comparison.
KEEP PLAN Hint: Cut Temp Table Recompiles in SQL Server
The KEEP PLAN hint gives a temp table the same recompile threshold as a permanent table. This post counts statistics recompiles with Extended Events for a plain query, a KEEP PLAN query and a KEEPFIXED PLAN query. It shows where the hint helps, where it does nothing and what the stronger hint costs.