Forceseek with Execution Plan Example In SqlServer 2008

Description: In this article I will describe what is a forced seek and how to use them in SQL Server 2008 with aN execution plan.

Content:

According to MSDN The FORCESEEK table hint forces the query optimizer to use only an index seek operation as the access path to the data in the table or view referenced in the query.

Here we can use this table hint to override the default plan chosen by the query optimizer to avoid performance issues caused by an inefficient query plan. For example, if a plan contains table or index scan operators, and the corresponding tables cause a high number of reads during the execution of the query, as observed in the STATISTICS IO output, forcing an index seek operation may yield better query performance.

FORCESEEK applies to both clustered and nonclustered index seek operations.

We can specify it for any table or view in the FROM clause of a SELECT statement and in the FROM <table_source> clause of an UPDATE or DELETE statement.

Now here I will show you an Example where I will show you a Forceseek with an Execution Plan.

Step 1:

Create a Database named "adventures work".

Step 2:

Now create two tables:

1. tblEnployee details(EmpId,EmployeeName,EmployeeAddress,PhoneNumber)
2. tblDeptDetails(DeptId,DeptName,Designation,salary,EmpId)

Step 3:

Now feed these two tables with data.

Step 4:

Now run the following query:

select * from tblDeptDetails
select * from tblEmployeeDetails

It will look like the following figure:



Here you have seen that we have Employee details and the department details.

Now we want to fetch the data from the 2 tables where the Employee salary is greater than 4000; the query is:

SELECT *
FROM tblEmployeeDetails  AS e
INNER JOIN tblDeptDetails  AS d
    ON e.Empid =d.Empid
WHERE d.Salary >5000

Now run the query; it will look like the following Figure 2:



Figure 2

While executing the query the database engine scans all the records and displays the matches.
Now suppose what if there is a high volume of data then scanning the records will be a lengthy process.
So for that we need to use a query optimizer with a FORCESEEK. In that scenario the required data will seek through the table and be displayed.

Step 4:

Now first enable the "Display Executed Query Plan" by clicking the short cut from the query window just like the following Figure 3 marked with red.



Figure 3:

Step 5:

Now we have the query plan. Now write the above code with FORCESEEK and see the result.

SELECT *
FROM tblEmployeeDetails  AS e
INNER JOIN tblDeptDetails  AS d WITH (FORCESEEK)
  ON e.Empid =d.Empid
WHERE d.Salary >5000

Now run the query. After that when you click the "Execution Plan" It will look like the following figure:



Figure 4 marked with red.

Here we are seeing that after running the query the cost is being displayed.

Conclusion:

So in this query we have seen how to use the FORCESEEK with the Execution Plan.

I am providing an "adventures work" backup file. You just have to restore it.