You are might be aware that before a query gets executed the Query optimizer will find the query plan from plan cache , Sometimes a very common query which returns big results behaves poorly because of sub optional plan.
I would like to show one example which we generally use most of time.
First we will create a table and add 10000 rows in it
The above SQL statements will create table “a” and will insert 10000 rows in to it.
Now we will execute the query
Select a1.* from a as a1 inner join a as a2 on a1.col1 = a2.col1 ;
Following is the Execution plan of above SQL Query
When we analyze the above plan we can easily come to know that the inner join is taking high cost taking this is because the SQL server query optimizer does not know that only one row of a1 matches with a2 which we know, It is because col1 column is clustered index and not unique clustered index. If we use HINT FAST N option it will tell query optimizer to fetch 1 row from inner query.
So the above query will become as
Select a1.* from a as a1 inner join a as a2 on a1.col1 = a2.col1 option (fast 1) ;
And the execution plan of above query will be
Here the efficient plan one that using option ( fast 1), so I would request you all kindly make use of HINTS get improve the SQL server performance.