Detailed overview of Query Hints in T-SQL

Detailed overview of Query Hints in T-SQL

Query hints specify that the indicated hints are used in the scope of a query

Sadat Arefin Rafat's photo
Sadat Arefin Rafat
·

32 min read

Table of contents

I needed to learn SQL Server Query Hints as part of my work. So I wrote a documentation outlining the query hints with examples. First I put summary. Then the detailed discussion. I took help from BARD in some cases, especially to summarize the descriptions.

Summary for Most Useful Findings:

Best Group, Union, and Join Algorithms:

Group:

  • HASH GROUP: Best for large datasets and high cardinality grouping columns.

  • ORDER GROUP: Best for small datasets and low cardinality grouping columns.

Union:

  • CONCAT UNION: Fastest, but does not preserve order.

  • HASH UNION: Efficient for large datasets and preserves order.

  • MERGE UNION: Preserves order and avoids sorting, but requires both inputs to be sorted.

Join:

  • HASH JOIN: Best for large datasets without pre-sorting.

  • LOOP JOIN: Efficient when one input is small and the other is large and indexed.

  • MERGE JOIN: Best for large pre-sorted datasets.

FAST <integer> Hint:

  • Useful when retrieving the first n rows quickly is important (e.g., TOP n queries).

  • Informs the optimizer to estimate the number of rows returned as less than or equal to the provided integer.

  • Improves performance by guiding the optimizer towards efficient plans for smaller datasets.

OPTIMIZE FOR Hint:

  • Can improve performance if you know the most common data for a table.

  • Allows you to specify information like filter predicates and estimated rows to guide the optimizer towards a specific plan.

  • Useful for optimizing queries that are sensitive to specific data patterns.

USE HINT

Top hints for performance improvements:

  • ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS: This hint can significantly improve performance by allowing the optimizer to consider filter selectivity when estimating join selectivity. This leads to more efficient join strategies, especially for queries with complex filtering conditions.

  • DISABLE_PARAMETER_SNIFFING: This hint helps avoid suboptimal plans for dynamic queries with varying parameter values. Without this hint, the optimizer might leverage the initial parameter values during compilation, leading to inefficient plans for subsequent executions with different values.

  • QUERY_OPTIMIZER_HOTFIXES: This hint enables the latest optimizer hotfixes, potentially addressing specific performance issues and improving overall query execution efficiency. Applying these hotfixes can often offer significant performance improvements without requiring major changes to the queries themselves.

Useful Hints in specific situations:

  • ENABLE_HIST_AMENDMENT_FOR_ASC_KEYS: This hint can be beneficial for queries involving ascending keys by allowing the optimizer to consider amendments to histogram statistics, potentially leading to improved query plans.

  • DISABLE_DEFERRED_COMPILATION_TV: This hint can be useful for table variables with complex logic, forcing compilation before query execution and potentially improving performance.

  • ASSUME_PARTIAL_CORRELATION_FOR_FILTER_ESTIMATES: When dealing with correlated filters, this hint strikes a balance between assuming full independence and minimum selectivity, potentially leading to better plan choices compared to the two extremes.

Details

GROUP

  • HASH GROUP: Faster for larger datasets, but it requires more memory.

Good example:

SELECT CustomerID, SUM(OrderAmount) 
AS TotalOrderAmount 
FROM Orders 
GROUP BY CustomerID 
OPTION (HASH GROUP);

Bad Example:

SELECT
  CustomerID,
  SUM(OrderAmount) AS TotalOrderAmount
FROM Orders
WHERE OrderDate > '2023-10-01'
GROUP BY CustomerID
OPTION (HASH GROUP);
  • ORDER GROUP: Slower but uses less memory.

Good Example:

SELECT
  CustomerID,
  SUM(OrderAmount) AS TotalOrderAmount
FROM Orders
GROUP BY CustomerID
HAVING SUM(OrderAmount) > 1000
OPTION (QUERY GROUP);

Bad Example:

SELECT
  CustomerID,
  SUM(OrderAmount) AS TotalOrderAmount
FROM Orders
GROUP BY CustomerID
ORDER BY TotalOrderAmount DESC
OPTION (QUERY GROUP);

UNION

  • MERGE UNION: Faster for larger datasets, but it requires more memory.

Good Example

SELECT * FROM LargeTable1
UNION ALL
SELECT * FROM LargeTable2
OPTION (MERGE UNION);

Bad Example

SELECT * FROM SmallTable1
UNION ALL
SELECT * FROM SmallTable2
OPTION (MERGE UNION);
  • HASH UNION: Slower but uses less memory.

Good Example

SELECT DISTINCT CompanyName, City FROM Customers
UNION ALL
SELECT DISTINCT CompanyName, City FROM Suppliers
OPTION (HASH UNION);

Bad Example

SELECT * FROM Products
UNION ALL
SELECT * FROM Services
OPTION (HASH UNION);
  • CONCAT UNION: Fastest method but may not preserve the order of the data.

Good Example

SELECT * FROM Products
UNION ALL
SELECT * FROM Services
WHERE ServiceType = 'Consulting'
OPTION (CONCAT UNION);

Bad Example

SELECT * FROM Orders
UNION ALL
SELECT * FROM OrderDetails
OPTION (CONCAT UNION);

JOIN

  • LOOP JOIN: Best when one join input is small (fewer than 10 rows) and the other join input is fairly large and indexed on its join columns. Good Example
SELECT *
FROM Orders
INNER JOIN OrderDetails
ON Orders.OrderID = OrderDetails.OrderID
WHERE Orders.OrderID = 12345
OPTION (LOOP JOIN);

Bad Example

SELECT *
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
ORDER BY OrderDate DESC
OPTION (LOOP JOIN);
  • MERGE JOIN: Best when the join inputs are large and presorted on the join columns. It requires both inputs to be sorted on the merge columns. Good Example
SELECT *
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
WHERE Customers.Country = 'USA'
ORDER BY OrderDate DESC
OPTION (MERGE JOIN);

Bad Example

SELECT *
FROM Products
INNER JOIN Categories
ON Products.CategoryID = Categories.CategoryID
WHERE Products.ProductName LIKE '%Computer%'
OPTION (MERGE JOIN);
  • HASH JOIN: Best when the join inputs are quite large and not sorted on the join columns. Good Example
SELECT *
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
OPTION (HASH JOIN);

Bad Example

SELECT *
FROM Products
INNER JOIN Categories
ON Products.CategoryID = Categories.CategoryID
WHERE Products.ProductName LIKE '%Computer%'
OPTION (HASH JOIN);

(For most joins I experimented on, HASH JOIN works the best. LOOP JOIN works best on some specific cases. But MERGE JOIN always take similar or worse time than other joins)

DISABLE_OPTIMIZED_PLAN_FORCING

Optimized plan forcing is a feature in SQL Server that helps reduce compilation overhead for frequently executed queries. It works by storing specific compilation steps as an "optimization replay script" for reuse.

The DISABLE_OPTIMIZED_PLAN_FORCING hint allows you to disable this behavior for individual queries. However, it's essential to use this hint judiciously.

Good Example:

SELECT *
FROM LargeProductTable
WHERE ProductName LIKE '%SpecialOffer%'
OPTION (DISABLE_OPTIMIZED_PLAN_FORCING);

Why is this good in this scenario? This example illustrates a query with a filter that changes frequently, potentially leading to different execution plans each time. By disabling optimized plan forcing, you ensure the optimizer always considers the current data distribution and optimizes the plan accordingly, potentially improving performance in this dynamic scenario.

Bad Example:

SELECT COUNT(*)
FROM CustomerTable
OPTION (DISABLE_OPTIMIZED_PLAN_FORCING);

Why is this bad? This example disables optimized plan forcing for a simple COUNT(*) query on a static table. In this case, the optimizer is unlikely to benefit from recompiling the plan for each execution, and the hint adds unnecessary overhead. Disabling optimized plan forcing can potentially lead to performance degradation for such simple and frequently executed queries.

FAST <integer_value>

The FAST <integer_value> hint in SQL Server is used to optimize a query for fast retrieval of the first <integer_value> number of rows. This result is a non-negative integer. After the first <integer_value> number of rows are returned, the query continues execution and produces its full result set. Example:

SELECT TOP 1000 * 
FROM Orders 
ORDER BY OrderDate DESC 
OPTION (FAST 100);

(In cases, where we need partial data really fast, this one is super useful.)

FORCE ORDER

In a MERGE statement, the source table is accessed before the target table as the default join order, unless the WHEN SOURCE NOT MATCHED clause is specified. Specifying FORCE ORDER preserves this default behavior.

Good Example:

SELECT *
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
INNER JOIN OrderDetails
ON Orders.OrderID = OrderDetails.OrderID
OPTION (FORCE ORDER);

Using FORCE ORDER ensures that the optimizer joins Customers first, then Orders, and finally OrderDetails, reflecting the real-world relationship between these entities. This can be beneficial for scenarios where the order of data processing is crucial for the query's logic or performance.

Bad Example:

SELECT *
FROM Products
INNER JOIN Categories
ON Products.CategoryID = Categories.CategoryID
WHERE Products.ProductName LIKE '%Computer%'
OPTION (FORCE ORDER);

The FORCE ORDER hint is unnecessary because the filter likely reduces the data significantly before the join, potentially leading to inefficient processing.

KEEP PLAN

The KEEP PLAN query hint changes the recompilation thresholds for temporary tables, and makes them identical to those for permanent tables. Therefore, if changes to temporary tables are causing many recompilations, this query hint can be used. For example, if your queries are accessing the TempDB frequently and leading to your query to change the execution plan, you can use the query hint KEEP PLAN.

Good example:

SELECT *
FROM LargeProductTable
WHERE ProductCategoryID = 123
OPTION (KEEP PLAN);

The KEEP PLAN hint helps ensure that the optimizer continues to use a previously generated and efficient plan, potentially reducing recompilation overhead and improving performance. This is particularly beneficial if the query is frequently executed with the same filter and the data within the category is relatively stable.

Bad Example:

SELECT *
FROM Customers
WHERE Country = 'USA'
OPTION (KEEP PLAN);

KEEP PLAN hint might not be helpful. The data in the Country column can potentially change, leading to a cached plan that becomes outdated and inefficient over time. Additionally, the filter might reduce the data significantly before the plan is applied, potentially making it less relevant than a freshly generated one.

KEEPFIXED PLAN

Specifying KEEPFIXED PLAN ensures that a query is recompiled only if the schema of the underlying tables changes, or if sp_recompile is run against those tables. This hint is useful when you want to maintain a consistent query plan, even when the underlying data changes.

Good Example:

SELECT *
FROM #TempTable
OPTION (KEEP FIXED PLAN);

By using KEEP FIXED PLAN, you ensure that the initially generated plan is reused, avoiding performance overhead from recompilation, even if the data within the temp table changes slightly.

Bad Example:

SELECT COUNT(*)
FROM Customers
WHERE Country = 'USA'
OPTION (KEEP FIXED PLAN);

Using KEEP FIXED PLAN might not be advantageous in this scenario. The filter significantly reduces the data before the plan is applied, rendering the cached plan potentially irrelevant or suboptimal for the smaller dataset. Additionally, the COUNT(*) operation is usually not performance-critical, and recompilation overhead is minimal.

IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX

The IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX is a query hint in SQL Server that prevents the query from using a nonclustered memory optimized columnstore index. If the query contains the query hint to avoid the use of the columnstore index, and an index hint to use a columnstore index, the hints are in conflict and the query returns an error.

Good Example:

SELECT SUM(SalesAmount)
FROM FactSales
WHERE Year(OrderDate) = 2023
GROUP BY ProductID
OPTION (IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX);

This example calculates the total sales amount for each product in 2023. A non-clustered columnstore index might exist on the FactSales table. However, the WHERE clause filters the data significantly before the grouping operation. In this case, the optimizer might choose a plan that utilizes the rowstore index for the filtered data, potentially offering better performance than the columnstore index. Using IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX ensures that the optimizer considers only other available indexes, potentially leading to a more efficient plan.

Bad Example:

SELECT *
FROM Products
WHERE CategoryID = 123
OPTION (IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX);

This example retrieves all products belonging to category 123. A non-clustered columnstore index might exist on the Products table, optimized for retrieving data based on categoryID. Ignoring this index unnecessarily forces the optimizer to choose a different plan, potentially leading to suboptimal performance. In such cases, it's better to let the optimizer decide whether to utilize the columnstore index based on the query and data characteristics.

MAX_GRANT_PERCENT = <numeric_value>

The MAX_GRANT_PERCENT = <numeric_value> is a query hint in SQL Server that specifies the maximum memory grant size in percent of the configured memory limit that a specific query can use. The query is guaranteed not to exceed this limit. If the query is running in a user-defined resource pool, and if the query doesn't have the minimum required memory, the system raises an error. If a query is running in the system pool (default), then it gets at minimum the memory required to run. Good Example:

SELECT *
FROM LargeTable
OPTION (MAX_GRANT_PERCENT = 50);

This example selects data from a large table, potentially exceeding available memory. By setting MAX_GRANT_PERCENT to 50, you ensure the query doesn't consume more than half of the available memory. This is beneficial to prevent memory pressure and allow other applications to function smoothly.

Bad Example:

SELECT COUNT(*)
FROM SmallTable
OPTION (MAX_GRANT_PERCENT = 10);

This example counts data from a small table, requiring minimal memory. Using MAX_GRANT_PERCENT in this case might not be necessary, as the optimizer will likely choose a plan that utilizes minimal resources. Additionally, limiting the memory grant to 10% might not be beneficial for the small data set and might even lead to suboptimal performance due to unnecessary constraints.

MIN_GRANT_PERCENT = <numeric_value>

The MIN_GRANT_PERCENT = <numeric_value> is a query hint in SQL Server that specifies the minimum memory grant size in percent of the configured memory limit that a specific query can use. The query is guaranteed to get MAX (required memory, min grant) because at least required memory is needed to start a query.

Good example:

SELECT *
FROM ComplexDataCube
WHERE Date BETWEEN '2023-01-01' AND '2023-12-31'
OPTION (MIN_GRANT_PERCENT = 25);

This example retrieves complex data from a data cube filtered by a date range, requiring significant memory for calculations and aggregations. Setting MIN_GRANT_PERCENT to 25 guarantees that the query receives at least 25% of the available memory, preventing insufficient memory allocation and potential performance issues.

Bad Example:

SELECT TOP 10 *
FROM Products
ORDER BY ProductName DESC
OPTION (MIN_GRANT_PERCENT = 5);

This example retrieves the top 10 products ordered by name, requiring minimal memory. Using MIN_GRANT_PERCENT in this case is unnecessary and might even be detrimental. Reserving a minimum of 5% of memory for such a simple query can limit resources available for other applications without any performance benefits.

MAXDOP <integer_value>

The MAXDOP <integer_value> is a query hint in SQL Server that overrides the max degree of parallelism configuration option of sp_configure for a specific query. The value of <integer_value> specifies the maximum number of processors that can be used in parallel plan execution. A value of 0 means that SQL Server chooses the max degree of parallelism. This hint is useful for controlling the execution of a query when the server-wide configuration is not suitable for a specific query. Good Example:

SELECT *
FROM LargeTable
WHERE Year(OrderDate) = 2023
GROUP BY ProductID
ORDER BY TotalSales DESC
OPTION (MAXDOP 4);

This example calculates the total sales for each product in 2023 and orders them by sales amount. The aggregation and sorting operations can benefit from parallel processing on a multi-core CPU. Setting MAXDOP to 4 restricts the query to using at most four parallel threads, maximizing CPU utilization without overwhelming the system resources.

Bad Example:

SELECT COUNT(*)
FROM SmallTable
OPTION (MAXDOP 8);

This example counts data from a small table, requiring minimal processing power. Setting MAXDOP to 8 unnecessarily creates eight parallel threads for a simple operation, potentially leading to context switching overhead and resource contention. In such cases, relying on the optimizer's decision for DOP is more efficient.

MAXRECURSION <integer_value>

The MAXRECURSION <integer_value> is a query hint in SQL Server that specifies the maximum number of recursions allowed for a query. When the specified or default number for MAXRECURSION limit is reached during query execution, the query ends and an error returns. This can be useful to prevent a poorly formed recursive common table expression from entering into an infinite loop. For example, if you have a recursive query that's not terminating as expected, you can use the MAXRECURSION query hint to limit the number of recursion levels and prevent the query from running indefinitely.

Good Example:

WITH RECURSIVE EmployeeHierarchy (EmployeeID, ManagerID, Level) AS
(
    SELECT EmployeeID, ManagerID, 0 AS Level
    FROM Employees
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT Employees.EmployeeID, Employees.ManagerID, Level + 1
    FROM Employees
    INNER JOIN EmployeeHierarchy ON Employees.ManagerID =      EmployeeHierarchy.EmployeeID
)
SELECT *
FROM EmployeeHierarchy
WHERE Level < 5
OPTION (MAXRECURSION 5);

This example defines a recursive CTE to traverse the employee hierarchy. The MAXRECURSION hint is set to 5, ensuring the recursion stops after reaching the fifth level, preventing potential infinite loops in case of circular references or invalid data.

Bad Example:

WITH RECURSIVE SalesTree (OrderID, ParentOrderID, Level) AS
(
    SELECT OrderID, ParentOrderID, 0 AS Level
    FROM Orders
    WHERE ParentOrderID IS NULL
    UNION ALL
    SELECT Orders.OrderID, Orders.ParentOrderID, Level + 1
    FROM Orders
    INNER JOIN SalesTree ON Orders.OrderID = SalesTree.ParentOrderID
)
SELECT *
FROM SalesTree
OPTION (MAXRECURSION 0);

This example defines a recursive CTE to navigate the sales order tree structure. However, using MAXRECURSION 0 disables any limit on recursion levels. This can lead to an infinite loop if there are circular references or invalid parent-child relationships in the data.

NO_PERFORMANCE_SPOOL

The NO_PERFORMANCE_SPOOL is a query hint in SQL Server that allows users to enforce an execution plan that does not contain a spool operator. The spool operator helps improve a query performance because it stores intermediate results so that SQL doesn't have to rescan or re-compute for repeated uses.. However, in some cases, it can reduce the overall performance. This hint is particularly useful when you want to control the usage of spool operators in your query execution plan.

Good Example:

SELECT *
FROM LargeOrders
ORDER BY OrderDate DESC
OPTION (NO_PERFORMANCE_SPOOL);

This example retrieves all orders from a large table, ordered by date. The large data volume might require spooling to sort the results. However, NO_PERFORMANCE_SPOOL forces the optimizer to choose a different plan that avoids spooling. This can be beneficial if the sorting operation is expensive and alternative strategies like using an index are available.

Bad Example:

SELECT COUNT(*)
FROM SmallCustomers
WHERE Country = 'USA'
OPTION (NO_PERFORMANCE_SPOOL);

This example counts customers from the USA in a small table. The data size is likely small enough to perform the counting without needing spooling. Using NO_PERFORMANCE_SPOOL in this case is unnecessary and might even be detrimental. Avoiding spooling might force the optimizer to choose a suboptimal plan for such a simple operation.

OPTIMIZE FOR ( @variable_name { UNKNOWN | = <literal_constant> } [ , ...n ] )

The OPTIMIZE FOR ( @variable_name { UNKNOWN | = <literal_constant> } [ , ...n ] ) is a query hint in SQL Server that instructs the Query Optimizer to use either a specific value or to assume an unknown value for a local variable when the query is compiled and optimized. This hint can be useful in scenarios where the initial values of variables used during the compilation of a query are not representative of the values that will be used during actual query execution.

Example

-- Optimize for large datasets with high selectivity on Country and Price
SELECT *
FROM Customers c
WITH (OPTIMIZE FOR (@EstimatedRows = 10000, @FilterPredicates = 'c.Country = ''USA'' AND p.Price > 100'))
JOIN Orders o ON c.CustomerID = o.CustomerID
JOIN Products p ON o.ProductID = p.ProductID
WHERE c.Country = 'USA' AND p.Price > 100

PARAMETERIZATION {SIMPLE | FORCED}

Parameterization can help improve the performance of your queries by allowing SQL Server to reuse execution plans for similar queries, reducing the overhead of compiling and optimizing queries. SIMPLE: Good Example:

SELECT *
FROM Products
WHERE ProductName LIKE @SearchTerm
OPTION (PARAMETERIZATION SIMPLE);

This example searches for products based on a user-supplied search term stored in the @SearchTerm parameter. Using PARAMETERIZATION SIMPLE instructs the optimizer to attempt simple parameterization, which can benefit performance by allowing the query plan to be reused for different search terms without recompilation.

Bad Example:

SELECT COUNT(*)
FROM Customers
WHERE City = 'New York'
OPTION (PARAMETERIZATION SIMPLE);

This example counts customers in New York City. The filter is static and doesn't utilize the parameter. Applying PARAMETERIZATION SIMPLE offers no benefit and might add unnecessary overhead to the query processing.

FORCED: Good Example:

SELECT *
FROM Orders
WHERE OrderDate BETWEEN @StartDate AND @EndDate
OPTION (PARAMETERIZATION FORCED);

This example selects orders based on a user-defined date range through the @StartDate and @EndDate parameters. Using PARAMETERIZATION FORCED ensures the query plan is generated considering the parameters, potentially offering better performance for various date combinations.

Bad Example:

SELECT TOP 10 *
FROM Products
ORDER BY ProductName DESC
OPTION (PARAMETERIZATION FORCED);

This example retrieves the top 10 products ordered by name. The query is not parameterized and doesn't require dynamic plan adaptations. Applying PARAMETERIZATION FORCED might lead to unnecessary plan generation overhead without any performance improvement.

USE HINT

Important Fact: Some USE HINT hints may conflict with trace flags enabled at the global or session level, or database scoped configuration settings. In this case, the query level hint (USE HINT) always takes precedence. If a USE HINT conflicts with another query hint, or a trace flag enabled at the query level (such as by QUERYTRACEON), SQL Server will generate an error when trying to execute the query.

  • ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS: Tells the optimizer to consider filter selectivity when estimating join selectivity, potentially improving performance for specific queries. Good Example:
SELECT *
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
WHERE Country = 'USA'
OPTION (USE HINT ('ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS'));

This example joins Customers and Orders tables with a filtering condition based on the Country column. Using the hint forces the optimizer to consider the filter when estimating the cardinality of the joined data. This can be beneficial if the filter significantly reduces the data volume, leading to a more efficient join plan.

Bad Example:

SELECT COUNT(*)
FROM Products
INNER JOIN Categories
ON Products.CategoryID = Categories.CategoryID
WHERE ProductName LIKE '%Computer%'
OPTION (USE HINT ('ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS'));

This example joins Products and Categories tables with a filtering condition based on the ProductName column. However, the COUNT(*) operation ignores the join results. Using the hint in this case unnecessarily forces the optimizer to assume a dependency between the join and the filter, potentially leading to a suboptimal plan for the actual query goal.

  • ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES: Forces the optimizer to use the minimum selectivity of AND filters, potentially resulting in more conservative query plans. Good Example:
SELECT *
FROM LargeProducts
WHERE CategoryID = 123
OPTION (USE HINT ('ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES'));

This example retrieves products from a large table based on a specific category. The filter condition might significantly reduce the data volume, but the optimizer might underestimate this reduction due to inaccurate statistics or skewed data distribution. Using the hint forces the optimizer to consider the minimum selectivity, potentially leading to a more efficient plan that utilizes indexes or avoids unnecessary sorting for the filtered data.

Bad Example:

SELECT COUNT(*)
FROM Customers
WHERE Country = 'USA'
OPTION (USE HINT ('ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES'));

This example counts customers in the USA. The filter condition is likely highly selective, meaning it returns a small percentage of rows. However, the COUNT(*) operation ignores the specific data. Using the hint in this case might not be beneficial and might even be detrimental. Assuming the minimum selectivity might lead the optimizer to choose a suboptimal plan for the overall query goal.

  • ASSUME_FULL_INDEPENDENCE_FOR_FILTER_ESTIMATES: Assumes no correlation between filters, potentially leading to suboptimal query plans. Good Example:
SELECT *
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
WHERE Country = 'USA' AND OrderDate > '2023-01-01'
OPTION (USE HINT ('ASSUME_FULL_INDEPENDENCE_FOR_FILTER_ESTIMATES'));

This example joins customers and orders with two separate filter conditions on different columns. Assuming complete independence allows the optimizer to estimate the cardinality of each filter separately and potentially choose a more efficient join plan. This is especially beneficial if the filters are not correlated and significantly reduce the data volume.

Bad Example:

SELECT *
FROM Products
WHERE CategoryID = 123 AND ProductName LIKE '%Computer%'
OPTION (USE HINT ('ASSUME_FULL_INDEPENDENCE_FOR_FILTER_ESTIMATES'));

This example retrieves products based on both category and name. While the filters might be selective, assuming complete independence might not be accurate. The category filter might influence the relevance of the name filter, leading to a potentially suboptimal plan. In this scenario, analyzing the data distribution and filter correlation would be more beneficial than blindly applying the hint.

  • ASSUME_PARTIAL_CORRELATION_FOR_FILTER_ESTIMATES: Considers a certain level of correlation between filters, offering a balance between the two extremes. Good Example:
SELECT *
FROM CustomerDemographics
INNER JOIN Orders
ON CustomerDemographics.CustomerID = Orders.CustomerID
WHERE MaritalStatus = 'Married' AND OrderAmount > 1000
OPTION (USE HINT ('ASSUME_PARTIAL_CORRELATION_FOR_FILTER_ESTIMATES'));

This example joins customer demographics with orders and applies two filters: one on marital status and another on order amount. Assuming partial correlation acknowledges that married customers might have a higher propensity for placing larger orders. This information helps the optimizer choose a more efficient join plan considering the combined effect of the filters.

Bad Example:

SELECT *
FROM Products
WHERE ProductName LIKE '%Laptop%' AND CategoryID = 123
OPTION (USE HINT ('ASSUME_PARTIAL_CORRELATION_FOR_FILTER_ESTIMATES'));

This example retrieves products based on both product name and category. While there might be some correlation between these attributes, assuming complete dependency might not be accurate. The category filter might already significantly reduce the data, making the name filter less relevant and leading to a potentially suboptimal plan. Analyzing the data distribution and filter correlation would be more beneficial than blindly applying the hint in this scenario.

  • DISABLE_BATCH_MODE_ADAPTIVE_JOINS: Prevents the optimizer from adapting query plans based on batch execution statistics. Good Example:
SELECT c.CustomerID, o.OrderID, p.ProductName
FROM Customers c
INNER JOIN Orders o ON c.CustomerID = o.CustomerID
INNER JOIN Products p ON o.ProductID = p.ProductID
WHERE p.ProductCategory = 'Electronics'
OPTION (USE HINT('DISABLE_BATCH_MODE_ADAPTIVE_JOINS'));

By adding the DISABLE_BATCH_MODE_ADAPTIVE_JOINS hint, you force the query optimizer to stick to its original join plan, bypassing the dynamic decision-making. This can be beneficial if you know the optimal join type for your scenario and Batch Mode Adaptive Joins is causing unexpected performance issues.

Bad Example:

SELECT * FROM Employees
OPTION (USE HINT('DISABLE_BATCH_MODE_ADAPTIVE_JOINS'));

Batch Mode Adaptive Joins can often improve performance by choosing the most efficient join method. Disabling it without reason can negatively impact query execution time and resource utilization.

  • DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK: Disables adjustments to memory grants based on batch execution feedback. Good Example:
DECLARE @budget INT = 10000;

SELECT TOP 10 p.ProductID, SUM(o.OrderQuantity) AS TotalQuantity
FROM Products p
INNER JOIN Orders o ON p.ProductID = o.ProductID
WHERE o.OrderDate > DATEADD(day, -30, GETDATE())
  AND o.TotalPrice < @budget
OPTION (USE HINT('DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK'));

-- Execute another query or perform tasks here without interruption from memory grant feedback

SELECT * FROM Customers WHERE City = 'Seattle';

In this case, the memory grant feedback generated while calculating the sum for TotalQuantity might hinder the execution of the subsequent SELECT statement on Customers. Disabling feedback for the first query using the hint allows both statements to run smoothly without interference.

Bad Example:

SELECT * FROM Orders
OPTION (USE HINT('DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK'));

Memory grant feedback plays a crucial role in managing resource allocation for batch executions. Disabling it for every query can lead to inefficient memory usage and potentially harm overall server performance. It's vital to use this hint only when there's a specific need to control feedback behavior.

  • DISABLE_DEFERRED_COMPILATION_TV: Forces compilation of table variables before query execution. Good Example:
DECLARE @orders TABLE (OrderID INT, CustomerID INT);

INSERT INTO @orders (OrderID, CustomerID)
SELECT OrderID, CustomerID
FROM Orders
WHERE OrderDate > DATEADD(month, -6, GETDATE());

SELECT o.OrderID, c.Name
FROM @orders AS o
INNER JOIN Customers AS c ON o.CustomerID = c.CustomerID
OPTION (USE HINT('DISABLE_DEFERRED_COMPILATION_TV'));

Here, the DISABLE_DEFERRED_COMPILATION_TV hint forces the query optimizer to compile the table variable immediately, preventing TVD from potentially delaying optimization and leading to a suboptimal plan. This can improve query performance by ensuring accurate cardinality estimates and efficient join strategies.

Bad Example:

DECLARE @products TABLE (ProductID INT, Name NVARCHAR(50));

INSERT INTO @products (ProductID, Name)
SELECT ProductID, Name
FROM Products;

SELECT * FROM @products
OPTION (USE HINT('DISABLE_DEFERRED_COMPILATION_TV'));

In this case, TVD might not be causing any performance issues, and disabling it unnecessarily could actually lead to slightly slower execution due to immediate compilation. Additionally, this approach ignores potential benefits of TVD in simpler queries where it can optimize resource usage.

  • DISABLE_INTERLEAVED_EXECUTION_TVF: Prevents interleaved execution of multi-statement table-valued functions. Good Example:
-- TVF returning two result sets: orders and customers
SELECT * FROM dbo.GetOrdersAndCustomers(@startDate, @endDate)
OPTION (USE HINT('DISABLE_INTERLEAVED_EXECUTION_TVF'));

-- Subsequent statements using results from both sets
SELECT COUNT(*) FROM orders
WHERE OrderDate > GETDATE();

SELECT Name FROM customers
WHERE City = 'Seattle';

Here, the DISABLE_INTERLEAVED_EXECUTION_TVF hint forces the TVF to execute completely and return all result sets before proceeding. This allows the subsequent statements to access the full results instantly, potentially improving performance and eliminating unnecessary waiting.

Bad Example:

SELECT * FROM dbo.GetSimpleProductDetails(123)
OPTION (USE HINT('DISABLE_INTERLEAVED_EXECUTION_TVF'));

-- Simple subsequent statement immediately using TVF result
SELECT Price * Quantity AS TotalCost
FROM product_details;

In this case, interleaved execution might not significantly affect performance due to the simplicity of the TVF and subsequent statement. Disabling it unnecessarily can even decrease performance slightly because the statement wouldn't benefit from immediate data availability.

  • DISABLE_OPTIMIZED_NESTED_LOOP: Disables the use of optimized nested loop joins, potentially impacting specific query performance. Good Example:
SELECT o.OrderID, c.Name, p.ProductName
FROM Orders o
INNER JOIN Customers c ON o.CustomerID = c.CustomerID
INNER JOIN Products p ON o.ProductID = p.ProductID
WHERE o.OrderDate > DATEADD(month, -3, GETDATE())
OPTION (USE HINT('DISABLE_OPTIMIZED_NESTED_LOOP'));

In this case, the DISABLE_OPTIMIZED_NESTED_LOOP hint forces the query optimizer to consider alternative join methods besides ONL. If you have already identified that a HASH join would be more efficient, this hint provides you with a way to override the default decision and potentially improve performance.

Bad Example:

SELECT * FROM Products
INNER JOIN OrderItems oi ON Products.ProductID = oi.ProductID
INNER JOIN Orders o ON oi.OrderID = o.OrderID
OPTION (USE HINT('DISABLE_OPTIMIZED_NESTED_LOOP'));

In this case, the default ONL plan might already be the most efficient choice for this simple query with small cardinalities. Disabling it unnecessarily can actually lead to slightly slower execution due to the overhead of considering other join methods. Additionally, using the hint without understanding the query optimizer's decision-making process can mask potential issues with cardinality estimates or other optimization problems.

  • DISABLE_OPTIMIZER_ROWGOAL: Prevents the optimizer from adjusting row goals in queries, possibly affecting memory usage and performance. Good Example:
SELECT TOP 10 OrderID, CustomerID, OrderDate
FROM Orders
WHERE OrderDate > DATEADD(day, -7, GETDATE())
ORDER BY OrderDate DESC
OPTION (USE HINT('DISABLE_OPTIMIZER_ROWGOAL'));

In this case, the ROWGOAL optimization assumes the query only needs to retrieve the top 10 rows early on, potentially leading to suboptimal plan choices. Disabling ROWGOAL forces the optimizer to consider the entire result set and choose a plan suitable for the whole query, potentially improving performance for TOP N scenarios with complex logic.

Bad Example:

SELECT TOP 5 ProductID, Name
FROM Products
ORDER BY Price DESC
OPTION (USE HINT('DISABLE_OPTIMIZER_ROWGOAL'));

In this simple scenario, ROWGOAL optimization can efficiently stop scanning data once it finds the top 5 products based on price. Disabling it adds unnecessary overhead for the optimizer and might slightly decrease performance due to the lack of early stopping.

  • DISABLE_PARAMETER_SNIFFING: Stops the optimizer from using parameter values during compilation, potentially leading to suboptimal plans with dynamic queries. Good Example:
CREATE PROCEDURE GetOrdersByCategory(@category NVARCHAR(50))
AS
BEGIN
  SELECT * FROM Orders
  WHERE Category = @category
OPTION (USE HINT('DISABLE_PARAMETER_SNIFFING'));
END;

-- Call the procedure with multiple categories, ensuring consistent performance
EXEC GetOrdersByCategory 'Electronics';
EXEC GetOrdersByCategory 'Books';

In this case, the DISABLE_PARAMETER_SNIFFING hint forces the query optimizer to create a generic plan based on estimated statistics instead of relying on the first parameter value. This ensures consistent performance regardless of the actual parameter used, improving overall efficiency and predictability.

Bad Example:

SELECT * FROM Customers
WHERE City = @city
OPTION (USE HINT('DISABLE_PARAMETER_SNIFFING'));

In this simple case, parameter sniffing can actually improve performance by optimizing the query plan based on the specific city value. Disabling it unnecessarily adds overhead for the optimizer and might slightly decrease performance due to the generic plan used. Additionally, it removes potential benefits of parameter sniffing for queries where specific values can lead to efficient plan choices.

  • DISABLE_ROW_MODE_MEMORY_GRANT_FEEDBACK: Disables memory grant adjustments based on row-mode execution feedback. Good Example:
WITH OrdersAndCustomers AS (
  SELECT o.OrderID, c.Name
  FROM Orders o
  INNER JOIN Customers c ON o.CustomerID = c.CustomerID
)
SELECT *
FROM OrdersAndCustomers AS oc
INNER JOIN Products p ON oc.OrderID = p.OrderID
OPTION (USE HINT('DISABLE_ROW_MODE_MEMORY_GRANT_FEEDBACK'));

-- Subsequent statements can now execute without interruption from memory grant feedback
SELECT COUNT(*) FROM Employees;

UPDATE Inventory SET UnitsInStock -= 1 WHERE ProductID IN (SELECT ProductID FROM OrdersAndCustomers);

In this case, the frequent memory grant feedback triggered by the nested loops and subqueries in the CTE can hinder the execution of subsequent statements like the SELECT and UPDATE. Disabling the feedback for the initial query using the hint allows both statements to run smoothly without interference from feedback messages, potentially improving overall throughput.

Bad Example:

SELECT * FROM Products
OPTION (USE HINT('DISABLE_ROW_MODE_MEMORY_GRANT_FEEDBACK'));

Memory grant feedback plays a crucial role in managing resource allocation for row-mode execution. Disabling it for every query can lead to inefficient memory usage and potentially harm overall server performance. Additionally, it removes the valuable information provided by the feedback about potential memory pressure caused by the query.

  • DISABLE_TSQL_SCALAR_UDF_INLINING: Prevents inlining of T-SQL scalar user-defined functions, potentially impacting performance in specific cases. Good Example:
SELECT o.OrderID, c.Name, dbo.CalculateDiscount(o.OrderDate, c.City) AS Discount
FROM Orders o
INNER JOIN Customers c ON o.CustomerID = c.CustomerID
OPTION (USE HINT('DISABLE_TSQL_SCALAR_UDF_INLINING'));

In this case, the DISABLE_TSQL_SCALAR_UDF_INLINING hint prevents the query optimizer from inlining the CalculateDiscount UDF. This forces the UDF to be executed separately for each row, potentially avoiding the overhead of complex inlining and the size limitation issue. This can lead to improved performance and stability, especially for frequently called UDFs with intricate logic.

Bad Example:

SELECT p.ProductID, p.Name, dbo.GetProductNameInLanguage(@language) AS NameInLanguage
FROM Products p
OPTION (USE HINT('DISABLE_TSQL_SCALAR_UDF_INLINING'));

In this simple case, the GetProductNameInLanguage UDF might be a small function translating product names based on a single parameter. Inlining this UDF could offer slight performance gains by avoiding separate executions for each row. Disabling inlining unnecessarily adds the overhead of function call setup and teardown, potentially degrading performance instead of improving it.

  • DISALLOW_BATCH_MODE: Disables execution in batch mode altogether. Good Example:
SELECT o.OrderID, c.Name, p.ProductName
FROM Orders o
INNER JOIN Customers c ON o.CustomerID = c.CustomerID
INNER JOIN Products p ON o.ProductID = p.ProductID
WHERE o.OrderDate > DATEADD(month, -3, GETDATE())
OPTION (USE HINT('DISALLOW_BATCH_MODE'));

In this case, the DISALLOW_BATCH_MODE hint forces the query optimizer to consider single-row execution plans, potentially leading to more efficient join methods and faster performance for smaller data sets or specific query patterns. By preventing Batch Mode, you gain more control over the chosen plan and can potentially alleviate performance bottlenecks caused by its automatic optimizations.

Bad Example:

SELECT COUNT(*) FROM Employees
OPTION (USE HINT('DISALLOW_BATCH_MODE'));

Batch Mode excels in optimizing resource utilization for large datasets and bulk operations. Disabling it for a simple COUNT(*) query on Employees could actually decrease performance due to the overhead of single-row execution instead of leveraging Batch Mode's efficiency. Additionally, applying the hint without understanding the query's context and potential benefits from Batch Mode can lead to unnecessary resource consumption and suboptimal performance in scenarios where Batch Mode would be advantageous.

  • ENABLE_HIST_AMENDMENT_FOR_ASC_KEYS: Allows the optimizer to consider amendments to the histogram statistics for ASC keys, potentially improving query plans. Good Example:
SELECT o.OrderID, c.Name, p.ProductName
FROM Orders o
INNER JOIN Customers c ON o.CustomerID = c.CustomerID
INNER JOIN Products p ON o.ProductID = p.ProductID
WHERE o.OrderID > 1000 AND o.OrderID < 2000
OPTION (USE HINT('ENABLE_HIST_AMENDMENT_FOR_ASC_KEYS'));

In this case, the ENABLE_HIST_AMENDMENT_FOR_ASC_KEYS hint instructs the query optimizer to dynamically adjust the histogram (distribution) of the OrderID index based on the specific range filter. This allows for more accurate cardinality estimates, potentially leading to the selection of a more efficient join plan and reduced resource usage compared to the default estimates.

Bad Example:

SELECT * FROM Customers
WHERE City = 'Seattle'
OPTION (USE HINT('ENABLE_HIST_AMENDMENT_FOR_ASC_KEYS'));

In this simple query with an equality filter on the City column, the default cardinality estimates are likely sufficient. Applying the hint in this scenario adds unnecessary overhead for histogram adjustment without providing significant benefits. Additionally, the hint might not be applicable to non-range filters or descending indexes, leading to potential performance drawbacks in those cases.

  • ENABLE_QUERY_OPTIMIZER_HOTFIXES: Enables the latest optimizer hotfixes, potentially addressing specific performance issues. Good Example:
-- Query experiencing performance issues due to known optimizer bug
SELECT TOP 10 o.OrderID, c.Name, p.ProductName
FROM Orders o
INNER JOIN Customers c ON o.CustomerID = c.CustomerID
INNER JOIN Products p ON o.ProductID = p.ProductID
WHERE o.OrderDate > DATEADD(month, -3, GETDATE())
OPTION (USE HINT('ENABLE_QUERY_OPTIMIZER_HOTFIXES'));

In this case, the ENABLE_QUERY_OPTIMIZER_HOTFIXES hint instructs the query optimizer to include optimizations and bug fixes introduced in cumulative updates and service packs that haven't been applied to your current database version. This can potentially resolve known performance issues caused by optimizer bugs and significantly improve the query's execution plan and performance.

Bad Example:

SELECT * FROM Employees
OPTION (USE HINT('ENABLE_QUERY_OPTIMIZER_HOTFIXES'));

Applying the hint without specific reason or identified optimizations adds unnecessary overhead to the query optimizer. In simple cases like a SELECT * on Employees, this overhead can outweigh any potential benefit from hotfixes, even if they exist. Additionally, using the hint indiscriminately can mask underlying performance issues that might require different optimization strategies.

  • FORCE_DEFAULT_CARDINALITY_ESTIMATION: Forces the optimizer to use the default cardinality estimation model, potentially affecting performance in some cases. Good Example:
-- Query experiencing performance issues due to inaccurate cardinality estimates
SELECT o.OrderID, c.Name, p.ProductName
FROM Orders o
INNER JOIN Customers c ON o.CustomerID = c.CustomerID
INNER JOIN Products p ON o.ProductID = p.ProductID
WHERE o.OrderDate > DATEADD(month, -3, GETDATE())
OPTION (USE HINT('FORCE_DEFAULT_CARDINALITY_ESTIMATION'));

In this case, the FORCE_DEFAULT_CARDINALITY_ESTIMATION hint instructs the optimizer to revert to the default estimation method used in previous SQL Server versions. This might be more accurate for your specific scenario than the advanced methods introduced in later versions, leading to improved join plans and performance.

Bad Example:

SELECT * FROM Employees
OPTION (USE HINT('FORCE_DEFAULT_CARDINALITY_ESTIMATION'));

Applying the hint without understanding its impact or considering alternative solutions can be detrimental. In simple cases like a SELECT * on Employees, the default estimation method might already be accurate, and using the hint unnecessarily adds overhead to the query optimizer. Additionally, forcing the default method might not be suitable for complex queries or specific data distributions, potentially leading to worse performance than the advanced estimation techniques.

  • FORCE_LEGACY_CARDINALITY_ESTIMATION: Forces the optimizer to use the legacy cardinality estimation model, potentially impacting performance in some cases. Good Example:
-- Query experiencing performance issues due to new cardinality estimation
SELECT o.OrderID, c.Name, p.ProductName
FROM Orders o
INNER JOIN Customers c ON o.CustomerID = c.CustomerID
INNER JOIN Products p ON o.ProductID = p.ProductID
WHERE o.OrderDate > DATEADD(month, -3, GETDATE())
OPTION (USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION'));

In this scenario, the hint could have potentially helped revert to the more accurate cardinality estimates for your specific query, leading to better join plans and improved performance.

Bad Example:

SELECT * FROM Employees
OPTION (USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION'));

Applying the hint indiscriminately adds unnecessary overhead and might not offer any benefit for simple queries like this. Additionally, the legacy estimation methods might not be suitable for all data distributions and could even lead to worse performance in some cases.

  • QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n: Sets the query optimizer compatibility level to a specific version, potentially impacting performance and functionality depending on the chosen level. Good Example:
CREATE PROCEDURE GetTopOrders(@category NVARCHAR(50))
AS
BEGIN
  -- Specify compatibility level for subsequent queries within the procedure
  OPTION (USE HINT('QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_140'));

  SELECT TOP 10 OrderID, CustomerID, OrderDate
  FROM Orders
  WHERE Category = @category
  ORDER BY OrderDate DESC;

  -- Subsequent queries within the procedure can also benefit from the specified level
  SELECT SUM(Amount) AS TotalSales
  FROM Orders
  WHERE Category = @category;
END;

In this case, applying the QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_140 hint forces the query optimizer to behave as if it were running on SQL Server 2016 (compatibility level 140). This might enable the query to utilize specific optimizations or avoid limitations that were present in that version and are causing performance issues in the newer version.

Bad Example:

SELECT * FROM Customers
OPTION (USE HINT('QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_120'));

Applying this hint to a simple SELECT * query is unnecessary and can even be detrimental. It adds overhead to the optimizer and might force it to use outdated or inefficient optimization techniques that are not suitable for the newer version of SQL Server. Additionally, it can mask potential performance issues with the query itself or with newer optimization features available in the current version.

  • QUERY_PLAN_PROFILE: Provides detailed information about the chosen query plan, allowing for further analysis and optimization. Good Example:
-- Query experiencing performance issues
SELECT o.OrderID, c.Name, p.ProductName
FROM Orders o
INNER JOIN Customers c ON o.CustomerID = c.CustomerID
INNER JOIN Products p ON o.ProductID = p.ProductID
WHERE o.OrderDate > DATEADD(month, -3, GETDATE())
OPTION (USE HINT('QUERY_PLAN_PROFILE'));

In this scenario, the hint would have triggered the capture of the actual execution plan used by the query into the query_plan_profile Extended Event. This captured plan could then be analyzed separately using tools like SQL Server Management Studio to identify bottlenecks, potential plan regressions, or inefficient use of resources.

Bad Example:

SELECT * FROM Employees
OPTION (USE HINT('QUERY_PLAN_PROFILE'));

Applying the hint indiscriminately adds unnecessary overhead to the query execution and creates additional entries in the Extended Event table without providing any relevant benefit for simple queries like this. Furthermore, analyzing numerous captured plans from various unrelated queries can hinder troubleshooting efforts by cluttering the event database.

Reference:

  1. SQL Query Hint Documentation
SQL HintsSQLT-SQLSQL ServerMicrosoft