Chapter 10 Notes: Controlling Execution Plans with Hints

OPTIMIZE FOR UNKNOWN

• Context: Used in a procedure to influence the execution plan for a predicate that might be highly selective or uncommon.

• Example snippet (from Listing 10-19):

  CREATE OR ALTER PROCEDURE dbo.AddressByCity
    @City NVARCHAR(30) AS
  SELECT AddressID, AddressLine1, AddressLine2, City, StateProvinceID, PostalCode, SpatialLocation, rowguid, ModifiedDate
  FROM  Person.Address
  WHERE City = @City
  OPTION (OPTIMIZE FOR UNKNOWN);
  GO
  EXEC dbo.AddressByCity @City = N'Mentor';

• Effect observed:

  • Even though Mentor is an uncommon city and the index is selective for the predicate, the plan produced is the generic plan rather than one tailored to a specific city value.

• Practical takeaway:

  • OPTIMIZE FOR UNKNOWN is a powerful hint but requires intimate knowledge of the data distribution and query workload.

  • Choosing the wrong value for OPTIMIZE FOR can hurt performance.

  • You should maintain and re-evaluate the hint as data changes over time.

• Extending to multiple variables:

  • If you need to control optimization for more than one variable, you can specify multiple hints in the OPTIMIZE FOR clause.

  • Example (as per Listing 10-20):

  OPTION (OPTIMIZE FOR (@City = 'London', @PostalCode = 'W1Y 3RA'))

OPTIMIZE FOR (specific values) and guidance

• Listing 10-20 demonstrates how to target optimization for multiple variables within a single query.

• Cautions:

  • Even with OPTIMIZE FOR, you should perform extensive testing before applying in production.

  • As data changes over time, re-evaluate whether the chosen target values remain appropriate.

• Practical contrast:

  • In many cases, OPTIMIZE FOR UNKNOWN is more stable than optimizing for a specific value because it avoids overfitting to a particular data snapshot.

RECOMPILE

• Context: The RECOMPILE hint forces a recompilation of the plan for the statement to which it is attached, using current values of all variables/parameters.

• Key points:

  • The hint can be applied to individual queries within a module.

  • For stored procedures, all statements including the one with OPTION(RECOMPILE) will still be in the plan cache, but the plan for the RECOMPILE statement itself will recompile on every execution.

  • This means the plan is not reused for that statement.

• Ad hoc vs prepared statements:

  • When used with ad hoc queries, the optimizer marks the plan as not cacheable (to avoid cache pollution), addressing ad hoc workloads concerns discussed in Chapter 9.

• Interaction with parameter sniffing:

  • RECOMPILE is a common remedy for bad parameter sniffing in parameterized SQL (dynamic or prepared statements).

• Example context (from Listing 10-21 and Listing 10-23):

  • A pair of similar queries with different parameter values exhibit different plans after recompilation.

  • With RECOMPILE, you can ensure the plan is optimized for the current parameter values rather than a sniffed value.

  • For prepared statements, you may still see plan cache behavior depending on whether the RECOMPILE hint is applied to the statement or the dynamic SQL, and whether the statement is cached.

Ad hoc workloads, parameterization, and parameter sniffing (concept overview)

• Ad hoc queries and the plan cache:

  • Ad hoc queries can cause cache bloat; this is discussed in Chapter 9.

  • If lack of parameterization is the root cause of performance issues, you can enable Optimize for Ad Hoc Workloads.

• Parameterization types:

  • Simple Parameterization (Chapter 9): Aims to parameterize literals in queries to promote plan reuse.

  • StatementParameterizationType property (in Execution Plan) can reveal whether a query was parameterized; Listing 10-23 demonstrates a failed attempt at Simple Parameterization.

• spprepare/spexecute workflow (prepared statements):

  • sp_prepare creates a plan for a parameterized statement once, intending to reuse it across executions.

  • sp_execute executes the prepared plan multiple times with different parameter values, leading to a single plan in the plan cache, often optimized for an unknown value rather than sniffed ones.

  • Example (pseudo-structure from Listing 10-22):

  DECLARE @IDValue INT;
  DECLARE @MaxID INT = 280;
  DECLARE @PreparedStatement INT;
  SELECT @IDValue = 279;
  EXEC sp_prepare @PreparedStatement OUTPUT,
      N'@SalesPersonID INT',
      N'SELECT soh.SalesPersonID, soh.SalesOrderNumber, soh.OrderDate, soh.SubTotal, soh.TotalDue FROM Sales.SalesOrderHeader soh WHERE soh.SalesPersonID = @SalesPersonID';
  WHILE @IDValue <= @MaxID BEGIN
      EXEC sp_execute @PreparedStatement, @IDValue;
      SELECT @IDValue = @IDValue + 1;
  END;
  EXEC sp_unprepare @PreparedStatement;

• Observations:

  • When you query the plan cache or the Query Store, you may see a single plan being reused for different parameter values because the optimizer did not sniff parameters (optimized for unknown).

  • If parameter sniffing causes performance issues for certain values, you can add OPTION (RECOMPILE) to force a fresh plan tailored to the current value (as in Listing 10-23).

  • Listing 10-23 demonstrates that neither plan is cached when RECOMPILE is used for prepared statements; two different plans may appear, but they are not cached.

EXPAND VIEWS

• Purpose: The EXPAND VIEWS hint forces the optimizer to bypass indexed views and go directly to the underlying tables.

• How it works:

  • The optimizer expands the referenced indexed view definition (the query that defines the view) and then runs optimization against those base tables rather than matching the expanded query to an indexed view.

  • The default behavior with indexed views is to try to match the query to a usable indexed view; EXPAND VIEWS disables this matching.

• View-level override:

  • You can override the behavior on a per-view basis using WITH (NOEXPAND) in the query for indexed views.

• Enterprise vs Standard:

  • Indexed view matching is an Enterprise feature; EXPAND VIEWS has no effect in Standard edition.

• Practical guidance and testing:

  • The effect of EXPAND VIEWS is highly workload-dependent; test to ensure performance does not degrade.

• Example (from Listing 10-24):

  SELECT vspcr.StateProvinceCode, vspcr.StateProvinceName, vspcr.CountryRegionName
  FROM Person.vStateProvinceCountryRegion AS vspcr;

• Observed behavior:

  • Using an indexed view can yield a plan that exploits the pre-materialized results, but applying EXPAND VIEWS can force a different plan (often worse) by expanding to base tables.

IGNORENONCLUSTEREDCOLUMNSTORE_INDEX

• Context: Columnstore indexes are efficient for aggregations but not always ideal for point lookups.

• Hint purpose:

  • IGNORENONCLUSTEREDCOLUMNSTORE_INDEX tells the optimizer to ignore any existing nonclustered columnstore indexes for the entire query.

• Limitations:

  • If the table has a clustered columnstore index, IGNORENONCLUSTEREDCOLUMNSTORE_INDEX does not affect its use.

• Practical takeaway:

  • Use this hint to avoid suboptimal columnstore index usage for certain query patterns, particularly aggregations vs point lookups.

Join Hints

• Purpose: A join hint forces the optimizer to use a specific join method for a particular join operation, rather than allowing the optimizer to choose.

• Join methods available:

  • LOOP, HASH, MERGE

• Key characteristics:

  • A join hint can override the optimizer's choice for the join method and can effectively force a particular join order (similar to OPTION (FORCE ORDER)).

  • Using join hints to fix join order or method can severely degrade performance if applied inappropriately.

  • Join hints apply to any query (SELECT, INSERT, DELETE) where joins occur.

  • You cannot force an Adaptive Join via hints (as of the material shown).

  • REMOTE join hint exists to force a join across a remote server; it has no effect on local execution plans (not explored in depth here).

• Demonstration plan (HASH join hint) – based on Listing 10-7 context:

  • Query structure involves a join among Production.ProductModel (pm), Production.Product (p), and Production.ProductModelIllustration (pmi).

  • Example form of a HASH join hint would be attached to the join clause between two inputs to force a HASH join:

  SELECT ...
  FROM Production.ProductModel AS pm
  INNER JOIN /* hint */ Production.Product AS p
    ON pm.ProductModelID = p.ProductModelID
  LEFT JOIN Production.ProductModelIllustration AS pmi
    ON ...
  OPTION (HASH JOIN)

• Practical caution:

  • Use join hints sparingly and primarily as a last resort when you have clear, repeatable evidence that the optimizer-selected plan is suboptimal for specific joins.

• Note: The detailed outcomes of the HASH join hint and the exact syntax for attaching it to the join clause are context-specific; the key takeaway is that join methods can be forced, but with risk of worse performance if misapplied.

Real-world implications and guidelines

• Hints are powerful but risky:

  • They can improve performance in edge cases, but incorrect hints can dramatically degrade performance and complicate maintenance.

  • Data distribution changes require ongoing re-evaluation of hints such as OPTIMIZE FOR, RECOMPILE, and EXPAND VIEWS.

• Testing and monitoring:

  • Always test hints in a staging environment with representative workloads.

  • Monitor plan cache, Query Store, and execution plans over time to detect regressions when data evolves.

• Maintenance mindset:

  • Treat hints as part of a broader performance tuning strategy, not as a long-term substitute for proper indexing, statistics maintenance, and query optimization.

• Connections to foundational topics:

  • Parameter sniffing and the role of plan compilation (Chapters referenced: Chapter 8 for RECOMPILE, Chapter 9 for ad hoc workloads, Chapter 16 for Query Store).

  • The balance between parameterization, plan reuse, and per-execution compilation.

  • The interplay between views (indexed views) and query optimization strategies.

• Formulas and numerical references:

  • There are no explicit mathematical formulas in this segment beyond general performance considerations; remember to treat cardinality estimation and density as underlying principles, which are elaborated in prior chapters (e.g., Chapter 8 on join selection and Chapter 9 on parameter sniffing and parameterization).

Key takeaways to study for the exam:

• OPTIMIZE FOR UNKNOWN is a plan-stabilizing hint that can bypass parameter sniffing but requires careful data knowledge and upkeep.

• OPTIMIZE FOR with explicit values can tailor plans to known data distributions but is fragile to data drift; use with extensive testing.

• RECOMPILE forces per-execution compilation, helpful for preventing sniffing issues but eliminates plan reuse for that statement.

• EXPAND VIEWS bypasses indexed-view matching and expands views to base tables; test performance due to possible plan degradation.

• IGNORENONCLUSTEREDCOLUMNSTORE_INDEX can steer away from columnstore indexes when not appropriate for point lookups unless a clustered columnstore index exists.

• Join hints (HASH, LOOP, MERGE) can override optimizer behavior for specific joins, but should be used sparingly due to risk of suboptimal plans; REMOTE join exists but not covered in depth here.