SQL SERVER – OPTION(FAST N) Hint and Performance

SQL Server 2019 is indeed very well made software by Microsoft. Recently, I have a very interesting situation while working with SQL Server 2019, which I had not faced before about OPTION(FAST N). While busy with Comprehensive Database Performance Health Check, I did not have access to an earlier version of SQL Server so I did not test it with earlier versions.

I am very much against the hints in SQL Server as they usually slow down the query performance by giving implicit guidance to build SQL Server execution plan and stopping the SQL Server Engine to make the most optimal decision for SQL Server.

However, while working with a client, they showed me a very interesting behavior of the OPTION(FAST N) hint. Let us see it here.

OPTION(FAST N) Query Hint

Let us run the following three queries and measure there performance. Before running the queries do not forget to enable the execution plan for each of the queries.

SET STATISTICS IO, TIME ON
-- Query without no hint
SELECT *
FROM [AdventureWorks2017].[Sales].[SalesOrderDetail] sod
INNER JOIN [AdventureWorks2017].[Production].[Product] p ON p.ProductID = sod.ProductID;
-- Query with Fast 100 Hint
SELECT *
FROM [AdventureWorks2017].[Sales].[SalesOrderDetail] sod
INNER JOIN [AdventureWorks2017].[Production].[Product] p ON p.ProductID = sod.ProductID
OPTION(FAST 100);
-- Query with Fast 1 Hint
SELECT *
FROM [AdventureWorks2017].[Sales].[SalesOrderDetail] sod
INNER JOIN [AdventureWorks2017].[Production].[Product] p ON p.ProductID = sod.ProductID
OPTION(FAST 1);

Here is the execution plan for all three queries.

It is very clear from the execution plan that query with no hint is very expensive and query with the fast 100 is second most expensive. Whereas the query with the hint FAST 1 is the most efficient query. While the execution plan provides us this information, the reality can only be verified if check the query IO and TIME.

We can check the query IO and TIME by enabling the statistics IO and TIME on the queries. Here is what we get in our message table when we have enabled the statistics IO and TIME.

— Query with NO HINT
Table ‘SalesOrderDetail’. Scan count 1, logical reads 1246
Table ‘Product’. Scan count 1, logical reads 15
SQL Server Execution Times:
CPU time = 938 ms, elapsed time = 4162 ms.

— Query with Fast 100 Hint
Table ‘Product’. Scan count 0, logical reads 242634
Table ‘SalesOrderDetail’. Scan count 1, logical reads 1246
SQL Server Execution Times:
CPU time = 1219 ms, elapsed time = 4827 ms.

— Query with Fast 1 Hint
Table ‘Product’. Scan count 0, logical reads 242634
Table ‘SalesOrderDetail’. Scan count 1, logical reads 1246
SQL Server Execution Times:
CPU time = 1406 ms, elapsed time = 5262 ms.

Now let us compare the execution time and IO status for all the three queries.

It is very clear from the execution plan the query without any hint is fastest and also is reading the last pages from the products table. Whereas when you see the other two queries the execution time in terms of CPU and elapsed time both are higher and also the page reads are extremely high. The reason for this behavior is very simple, whenever you use any hint, the SQL Server engine stops using its own intelligence and starts following the direction provided by the query hint.

Summary

In our example, when we provide a hint of the fast 1 or fast 10 or any fast N, SQL Server immediately starts building the execution plan based on the number provided in the FAST N hint. The execution plan directly assumes that there are only going to be as many rows as many specified in the query hint and optimizes the execution plan according to it. Now if you have more or fewer rows than what is provided in the query hint, your execution plan will be very inefficient and that is exactly is happening in our case.

Once again this example validates my bias against query hint. Don’t use query hints, if possible. In 99.99% of the cases they are not needed and in the 0.01% cases when we need them, we do not easily discover them.

Reference: Pinal Dave (https://blog.sqlauthority.com)