Rajendra Gupta
Comparison with different values of query hint

Explore the SQL query Hint OPTION (FAST N)

July 2, 2020 by

In this article, we will introduce SQL queries hint and will analyze the OPTION(Fast ‘N’) hint in detail.

Introduction

Once the user submits any query to SQL Server, it creates an optimized and cost-effective execution plan depending upon the statistics, indexes, and data distribution. In most of the cases, it is an effective plan, and it does not require any intervention for a query performance if all other things like statistics, indexes are in proper form.

In case you are new to Execution Plans in SQL Server, you can refer to this article, SQL Server Execution Plans overview to understand about them.

SQL Server provides query hints in which you can force query optimizer to use a particular operator in the execution plan. To use a specific operator, SQL Server might need to change other operators as well. We have the following query hints in SQL Server.

Table Hints

Suppose you want to use a specific index for your query to avoid a table scan, we can use table hints. One of the popular table hints is WITH(NOLOCK) to avoid default transaction isolation level and avoid locking issues in Select statements.

SQL Queries Hints

It instructs query optimizer to apply the hint throughout the SQL query execution. Some examples are Recompile, MAXDOP, OPTIMIZE FOR UNKNOWN.

In this article, we will explore the query hint OPTION (FAST ‘N’) to improve the performance of a query for retrieving the specific number of rows quickly along with its impact.

Pre-requisites

In this article, I will use the following SQL Server environment.

  • SQL Server 2019 – 15.0.2070.41 – RTM – GDR
  • {AdventureWorks] sample database. If you do not have it in your instance, you can download the .bak file from GitHub and restore the backup file. Change the compatibility level as per the SQL instance version

Exploring OPTION(FAST ‘N’) hint in SQL queries

Suppose you execute the following SQL script that retrieves the records from using a Select statement. It uses an INNER JOIN between [SalesOrderDetails] table and [Product] table.

This query returns 121,317 rows and took 2 seconds on my laptop, as shown below:

Sample SQL Queries output

Let’s enable the actual execution plan for this query using the CTRL+M shortcut key. We will also enable statistics and IO profile for this session using the SET STATISTICS IO, TIME statement.

In the below actual execution plan, it uses a clustered index scan and index match ( inner join) as two costly operators.

Actual execution plan

If we look at the message tab, you can see logical reads, physical reads, CPU time, elapsed time. To analyze these outputs, we can copy the message tab output in statistics parser. You get output in a tabular format, and it helps you to analyze the output quickly.

Here, you can see we have a total of 1261 logical reads, out of which 1246 logical reads are for the [SalesOrderDetails] table.

Statistics IO

Sometimes we see that SQL Queries takes a long time to complete, and we want to view or display the specified number of rows as soon as possible in a client application. SQL Server provides a query hint OPTION(FAST ‘N’) for retrieving the N number of rows quickly while continuing query execution. This Fast query hint tells (forces) SQL Queries optimizer to give a specific number of rows (represented by ‘N’) quickly without waiting for the complete data set to appear.

You can think of it as a user application where you do not want the user to wait for their data. They can view N number of rows quickly as they refresh the web page. For example, let’s say we want to retrieve one row quickly so we can specify a query hint, as shown below in the above query.

It quickly populates one row in the output while the rest of the rows are being retrieved. Let’s view the execution plan of the query above with OPTION(FAST 1).

In the below execution plan, it changes the costly operators as below.

  • The Clustered Index Scan:35%
  • The clustered index seeks: 67%

We have not changed the query, but still, it changes the execution plan to satisfy the query hint specified.

Clustered Index Scan

The FAST ‘N’ query hint allows the optimizer to return a specified number of rows as quickly as possible for SQL Queries. Imagine you have a custom application where users put a specific condition and wait for data to appear.

It will increase user experience if the SQL query returns the results quicker. Suppose we use a FAST 75 table hint, to return the first 75 records of the results set while still working to return the remaining rows. It allows users to start working with the data before the rest of the screen loads with data.

Let’s look at the below image showing the tooltip for the select operator in the query without query hint and with query hint Option Fast(1).

  • The query optimizer uses 121317 estimated number of rows per execution without any query hint. We can note that the total number of rows returned by the above select statement in SQL Server is also 121317 rows
  • In the other case, we force SQL Queries optimizer to use the query hint. It uses the 1 row as the estimated number of rows per execution. You might get quick results for the specified number of rows, but it put SQL Server query optimizer to prepare a poor execution plan

SQL Queries hint

Similar to comparing the actual execution plan, let’s compare the logical reads of both the queries.

  • [SalesOrderDetail] table logical reads are the same in both queries 1246
  • You can note a significant increase in the logical reads for the [Product] table. Previously it had 15 logical reads, but we get 242634 logical reads for it with query hint

Comparison with different values of query hint

Let’s explore a few more examples with different values of ‘N’ in the query hint in a single query window. We use the Go operator to execute these queries in separate query batches.

  • Query with Option(fast 1)
  • Query with Option(fast 10)
  • Query with Option(fast 100)
  • Query with Option(fast 1000)

From the below screenshot, we see that as we increase the value in ‘N’ in the query hint, the overall cost gets increased in comparison with the other batches. It shows 57% query cost for FAST 1000 while it has a 5% query cost for FAST 1 SQL Queries hint.

OPTION (FAST N) values

In the above screenshot, we can see execution plans with different values of fast query hint. It highlights that query without any hint is fastest. In this case, SQL Server was able to create the optimized execution plan. Once we specify a fast query hint, optimizer stops thinking and prepares an execution plan by estimating the number of rows we specify in the hint. It works fine with the small data set and low value of fast query hint. As we increase the row counts, query cost becomes significant, and it might cause a performance bottleneck instead of getting the benefit of it.

Query hint is a sword of double edges. You should avoid using the hint and let optimizer creates the best execution plan. Sometimes you see an immediate benefit in query performance once you use a hint, but as data grows, it might not be suitable for your requirements. You should analyze the query plan, focus on the cost operators, resources, statistics, indexes, their fragmentations to fix issues in the long run.

Conclusion

In this article, we explored SQL Queries hints and focused on the OPTION(FAST ‘N’) hint. You should never use this hint in a production environment without proper testing in non-prod systems. If you plan to use them, look at the query execution plans, their costs, logical reads, and physical reads.

Rajendra Gupta
Latest posts by Rajendra Gupta (see all)

Related posts:

  1. SQL Query Optimization Techniques in SQL Server: Parameter Sniffing
  2. SQL Server Statistics and how to perform Update Statistics in SQL
  3. Parallel Nested Loop Joins – the inner side of Nested Loop Joins and Residual Predicates
  4. Top SQL Server Books
  5. SQL Server table hints – WITH (NOLOCK) best practices
168 Views