SQL Server Interview Question #216

What is the OPTIMIZE FOR query hint?

Advanced Query Optimization Senior Advanced

Detailed Explanation

OPTIMIZE FOR tells the optimizer to compile a statement using a specified parameter value rather than the value that might otherwise be used at compilation.

OPTIMIZE FOR UNKNOWN asks the optimizer to use statistical distribution assumptions rather than optimize for a specific sniffed value.

These hints can stabilize some parameter-sensitive workloads, but they encode assumptions about data distribution. They should be used only after measuring the workload and understanding why the existing plan is unsuitable.

Code Example

SELECT Id, OrderDate, TotalAmount
FROM dbo.Orders
WHERE CustomerId = @CustomerId
OPTION (OPTIMIZE FOR (@CustomerId = 100));