FORCESEEK Hint in SQL Server: A Deep Dive

In this Article we are presenting an in-depth overview of the SQL Server FORCESEEK hint, features, uses, possible advantages, and disadvantages of this hint.

We are going to discuss its behavior using several examples, how it can impact the execution plan of query and enhance performance under certain situations. We will also explain circumstances in which the best option may not be to use FORCESEEK.

Introduction to Query Hints

SQL Server query hints are statements providing the query optimizer with specific instructions that it should follow when query is being executed. They offer an option to control the default behavior of the optimizer and to control the execution plan. The query optimizer tends to arrive at sound choices, but there are cases when a hint could result in an efficient internal choice. But, it is important to apply hints wisely, as they can also be impeding in performance when done wrong.

What is FORCESEEK?

The FORCESEEK hint is a table hint that coerces SQL server to perform an index seek operation in order to retrieve data in a table or view. A query that can make use of an index to locate the desired rows directly is an extremely efficient method of retrieving data: this is known as an index seek. The query optimizer may select a different access method at a different cost estimate, like an index scan or a table scan, when the query does not have the FORCESEEK hint.

The FORCESEEK hint is used in the FROM part of a SELECT, UPDATE or DELETE statement. The simplest syntax has the following format:

SELECT column1, column2    
FROM TableName WITH (INDEX(IndexName), FORCESEEK)    
WHERE condition;   

TableName: This is the name of the table or view.
IndexWriter(IndexName): The specified index is the one that the SQL Server is to utilize when it is going to make a seek operation. An omission of the index name will make SQL Server select the index it thinks is most appropriate.
E.g. FORCESEEK: The hint that directs SQL Server to an index seek.

When to Use FORCESEEK

The FORCESEEK clue would be most useful in the following situations:

When you are sure a given index will offer the most efficient path to accessing the information: Since you know more about your data and the indexes, you may be able to find an index that the optimizer is not utilising, yet would lead to faster access to data.

When the query optimizer is selecting an inefficient scan operation: If the optimizer is always selecting a table scan or index scan that index seek would be a better fit, FORCESEEK can force the optimizer to use the seek. This comes in handy specifically with large tables and selective queries.

When the problem lies in parameter sniffing: In certain situations, the query optimizer may produce a suboptimal plan, depending upon the original parameter values (parameter sniffing). FORCESEEK may be used to stabilize the plan and eliminate performance deviations due to various parameter values.

Examples

Now we want to introduce some real life examples of using FORCESEEK. Suppose that we have a table named Customer which is structured as shown below:

-- Create the table  
CREATE TABLE Customers (  
    CustomerID INT PRIMARY KEY,  
    FirstName VARCHAR(50),  
    LastName VARCHAR(50),  
    City VARCHAR(50),  
    State VARCHAR(2),  
    ZipCode VARCHAR(10)  
);  
  
-- Insert 100,000 records  
WITH Numbers AS (  
    SELECT TOP (100000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n  
    FROM sys.all_objects a  
    CROSS JOIN sys.all_objects b  
)  
INSERT INTO Customers (CustomerID, FirstName, LastName, City, State, ZipCode)  
SELECT   
    n AS CustomerID,  
    'FirstName' + CAST(n AS VARCHAR(10)) AS FirstName,  
    'LastName' + CAST(n AS VARCHAR(10)) AS LastName,  
    'City' + CAST(n % 500 AS VARCHAR(10)) AS City,  -- 500 sample city variations  
    CHAR(65 + (n % 26)) + CHAR(65 + ((n/26) % 26)) AS State, -- Random 2-letter state  
    RIGHT('00000' + CAST((10000 + n % 90000) AS VARCHAR(10)), 5) AS ZipCode  
FROM Numbers;  

Let's create index's

CREATE INDEX IX_Customers_LastName ON Customers (LastName);  
CREATE INDEX IX_Customers_CityState ON Customers (City, State);  

Example 1:Advanced Forcing forced on LastName.

We want to select all the customers whose last name is a certain name.

SELECT CustomerID, FirstName, LastName, City, State  
FROM Customers WITH (INDEX(IX_Customers_LastName), FORCESEEK)  
WHERE LastName = 'LastName4';  

In this query we are specifically asking SQL Server to use the IX_Customers_LastName index, and to seek the index using the indexseek predicate.

Example 2: Enforcing a Seek on City and State.

Suppose we would like to locate all customers within a particular state and a city.

SELECT CustomerID, FirstName, LastName, City, State  
FROM Customers WITH (INDEX(IX_Customers_CityState), FORCESEEK)  
WHERE City = 'City3' AND State = 'DA';  

In this case, we are compelling the SQL Server to utilize the IX_Customers-CityState index and conduct an index seek which identifies the values of the city and state.

Example 3: FORCESEEK without an index.

When you do not include the index name, the SQL Server will select the index that it believes to be the most suitable to perform the seek.

SELECT CustomerID, FirstName, LastName, City, State  
FROM Customers WITH (FORCESEEK)  
WHERE City = 'City5' AND State = 'FA';  

Here, SQL server will determine the indexes available and then will select the index it assumes will allow the most efficient seek operation to the WHERE clause provided.

Potential Drawbacks

Though FORCESEEK may be useful, one must take into consideration the possible disadvantages:

Suboptimal Plans: Index seeking may not necessarily be the optimal approach. Depending on the query and data distribution, the query optimizer may have selected a different method of access (e.g. a table scan). In these situations, the use of FORCESEEK may result in performance decline.
Maintenance Overhead: The FORCESEEK hint may be invalid or suboptimal in case the underlying data or indexes are being changed. You will have to check the performance of FORCESEEK queries and change the hints accordingly.
Brittle Code: Code can be brittle by depending too much upon hints. The assumptions underlying the hints may result in the unexpected performance problems due to the changes in the database schema or data distribution.
Disregard of Statistics: Statistics is used by the query optimizer to determine the approximate cost of various execution plans. FORCESEEK may re-estimate these estimations and may end up with a non-optimal plan due to the observed data distribution.

Best Practices

Caution: FORCESEEK should only be used when you clearly understand how the query optimizer works and when you may get many benefits by forcing an index seek.

Test Thoroughly: It is always good to test the performance of the query as it is and as it is with the FORCESEEK hint to show that the query is actually helping to improve performance.

Performance Monitoring: Schedule a query performance check with the FORCESEEK periodically, to identify query-performance issues that emerge due to data or index changes.

Consider Alternatives: Alternatives Now FORCESEEK has a variety of alternatives available before using it, you can consider the alternatives like first optimize indexes, reconsider the statistics and also rewrite the query.

Record Your Clues: It is essential to explicitly record your reasons why you are using FORCESEEK and in which situations it will come in handy. This will assist other developers to agree on the rationale of the hint and abstain from making the changes that may adversely affect performance.

Conclusion

FORCESEEK hint in SQL server is an effective performance hint that can be applied on query execution plan and enhance performance in certain situations. But it is important to employ it wisely and understand the possible disadvantages. You can maximize your queries and make sure your SQL Server database is working to its fullest when you know when and how to utilize the FORCESEEK. Always be sure to test it out and check the performance because you need to know that the hint is truly benefiting you.