SA0101 : Avoid using hints to force a particular behavior

Introduction

Using hints in SQL queries should be avoided as they can lead to rigid query execution plans, reduced adaptability, and potential performance degradation over time.

Description

When writing T-SQL queries, the SQL Server query optimizer is designed to choose the most efficient execution plan. However, manually overriding these default choices with query hints can lead to suboptimal performance or restrict the optimizer’s ability to adapt to changes in data or server resources.

For example:

-- Example of a query with a manual join hint that may cause issues
SELECT *
FROM Orders o
INNER HASH JOIN Customers c ON o.CustomerID = c.CustomerID;

This example forces the use of a hash join, which might not be the best choice for all data sizes or distribution. Instead of letting SQL Server decide dynamically, a fixed hint here can result in slower execution if data patterns change over time.

Forcing a specific execution plan can degrade performance as data and workload conditions evolve.
Excessive use of hints makes queries less flexible and harder to maintain, as each hint must be manually reviewed and adjusted when underlying data structures change.

How to fix

Avoid using query hints in SQL queries to maintain performance and flexibility.

Follow these steps to address the issue:

1.Review the SQL query for any JOIN , OPTIMIZE FOR , or other query hints that may be affecting performance.

2.Analyze the current execution plan without the hint using SQL Server Management Studio (SSMS) to determine if query performance is adequate. You can do this by executing the query and inspecting the execution plan.

3.Remove the hints and allow the SQL Server query optimizer to select the best execution plan based on current data and resource conditions.

If the performance is not satisfactory without hints, consider indexing strategies or query refactoring as a preferred alternative:

1.Identify missing indexes using the execution plan’s suggestions or the missing index DMVs (e.g., sys.dm_db_missing_index_details ).

2.Create or adjust indexes to support efficient query execution without forcing execution plans with hints.

3.Reformat queries to eliminate heavy operations or optimize join paths, potentially breaking down complex queries into simpler, more manageable parts.

For example:

-- Example of a query with removed hints
SELECT o.OrderID, o.CustomerID, c.CustomerName
FROM Orders o
INNER JOIN Customers c ON o.CustomerID = c.CustomerID;

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters

Rule has no parameters.

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix

3 hours per issue.

Additional Information

Hints (Transact-SQL)

Example Test SQL

--- Query Hints ---
SELECT *
FROM Sales.Customer AS c
INNER JOIN Sales.vStoreWithAddresses AS sa
    ON c.CustomerID = sa.BusinessEntityID
WHERE TerritoryID = 5
OPTION (MERGE JOIN);

-- Join Hints
SELECT p.Name, pr.ProductReviewID
FROM Production.Product p
LEFT OUTER HASH JOIN Production.ProductReview pr
ON p.ProductID = pr.ProductID
ORDER BY ProductReviewID DESC;

--- Table Hints ---
SELECT *
FROM Sales.SalesOrderHeader AS h
INNER JOIN Sales.SalesOrderDetail AS d WITH (FORCESEEK,NOLOCK) /*IGNORE:SA0101(line)*/
    ON h.SalesOrderID = d.SalesOrderID
WHERE h.TotalDue > 100
AND (d.OrderQty > 5 OR d.LineTotal < 1000.00);

UPDATE Production.Product WITH (TABLOCK)
SET ListPrice = ListPrice * 1.10
WHERE ProductNumber LIKE 'BK-%';

Analysis Results

	Message	Line	Column
1	SA0101 : Avoid using hints to force a particular behavior.	7	0
2	SA0101 : Avoid using hints to force a particular behavior.	12	11
3	SA0101 : Avoid using hints to force a particular behavior.	24	32

Analysis Rules