SQL Server query hints execution plan – Part1

Hi Friends,

Today we are going to discuss on SQL Server query hints execution plan. Let us consider following SQL statement;

USE [AdventureWorks2012]

SELECT *
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderDetail.ProductID

ExplanWithoutQueryHints

Imagine that our application is only concerned about First N rows at a time and similar execution plan shown in above figure can be an expensive one though it is chosen to be most appropriate to rerun records by SQL Server optimizer. We can include FAST hint to get these records quicker. After applying FAST hint in the statement, the new plan includes Index Scan and Key Lookup (read here Part1, Part2, Part3, Part4) on the clustered table and help to return FIRST N rows quickly and is shown in the following example.

USE [AdventureWorks2012]

SELECT *
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderDetail.ProductID
OPTION (FAST 10)

ExplanWithQueryHints

This hint is used to optimize a query to retrieve First N rows of records as quickly as possible and is helpful in situations where only first few rows are relevant for the query result. This is not applicable for the remaining rows that are returned by the query.

The downside is, remaining records may take longer time because the query is optimized for first N records and not for all. In the example, Sort operator (links) is efficient way to get the records sorted ProductID but this is a blocking operator i.e. SQL Server will not produce any records until Sort operation is completed.

You can find index to the execution plan series here and click on One operator a day to visit exclusive page for this series.

Happy learning!

Regards,

Kanchan

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook | Follow me on Twitter | Follow me on FaceBook

Avatar

About Kanchan Bhattacharyya

Kanchan is an astute IT professional, a seasoned SQL Database Administrator with 13+ years of industry experience. A calculated risk taker with deep technical knowledge and has gained proficiency in database consulting and solutions across different environments. Kanchan holds MBA degree in IT and an International Executive MBA in Project Management. He is very passionate about SQL Server and holds MCSE (Data Platform), MCSA – SQL 2012, MCITP – SQL 2008 certifications. Currently he is focusing on cloud and latest releases of SQL Server. When not working, Kanchan likes to spend his time reading on new technical developments specifically on SQL Server and other related technologies.

View all posts by Kanchan Bhattacharyya →

Leave a Reply

Your email address will not be published. Required fields are marked *