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.

SQL SERVER - OPTION(FAST N) Hint and Performance fastN-800x532

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.

SQL SERVER - OPTION(FAST N) Hint and Performance fastN

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)

, , ,
Previous Post
SQL SERVER – Top Reasons for Slow Performance
Next Post
SQL SERVER – Disable Parameter Sniffing with DISABLE_PARAMETER_SNIFFING Query Hint

Related Posts

4 Comments. Leave new

  • Mustafa ELmasry
    February 11, 2020 3:49 pm

    great notes , i tested and i test Option (OPTIMIZE FOR UNKNOWN ) it show to me the same results of without Fast , i used Statistics Parser to compare the Statistics to know how many pages reads and How many logical read and CPU time
    this means this option is good from performance wise or what
    (OPTIMIZE FOR UNKNOWN )
    Thanks

    Reply
  • Good observation, but this is expected and predictable behavior . The FAST N query hint is designed for applications that may cache a result set and page the results to the application; i.e. if I show the user pages of 100 results at a time, I may want FAST 100. This is not designed to address bad plans due to out of date statistics or parameter sniffing. Note: this is not new to SQL 2019

    Reply
  • You’re running the same data pull three times in a row. Are you sure the times shown in the plan aren’t simply because the first select is caching the data? Your two results make no sense together as the percentages shown in the first result are supposed to indicate what percentage of CPU time each select took.

    Reply
  • I would say his results are correct because they are expected. FAST N creates a row goal by optimizing the query plan for the number given. In the case you want to page 100 rows at a time, FAST N will retrieve the first 100 rows much faster with a nested loop vs having to hash everything in a hash join first. The entire query may take longer, but a .NET application can use those first 100 rows right away while waiting for the query to finish.

    Reply

Leave a Reply

Menu