Nisal's Business Intelligence Blog

Learn, Grow and Share on Microsoft BI & Analytics Platform

Sunday, November 22, 2015


FORCESEEK and FORCESCAN are two Table Hint options you can use to avoid performance issues by enforcing SQL Server engine to execute by overriding what is chosen by cardinality estimation.

Lets see the usage of it. I used AdventureWorks2012 sample database and written a query by joining SalesOrderHeader and SalesOrderDetails table.

I set the Time and IO statistics to ON for getting the execution statistics. (Refer the below screen)

38,575 rows returned by execution the query. Now take a look at Actual Execution Plan of the query.

If you have a closer look at the Execution plan you might noticed that SalesOrderDetails table has used Table Scan though the tables contain clustered keys on OrderID columns and used for join condition.

Lets make a note of Statistics information too what we received by executing the query.(Please, make sure to clean the cache before you getting the execution IO and Time info in-order to get more accurate stat.

Now, lets use FORCESEEK table hint and see the execution plan.

If you see the execution plan now you might notice SalesOrderDetail table has used Index Seek instead of Table scan.

Statistics information for the query execution with FORCESEEK Table hint.

You can clearly see, there is a little difference of Execution times and with the FORCESEEK table hint execution performed well than earlier.

Note: FORCESEEK and FORCESCAN table hints we can use to override the default cardinality estimation. Most of the scenarios SQL Server engine produce and select the best and accurate execution plan. But, there are scenarios you may need to override by enforcing it, where table hints coming into play.

Every time when we enforce to engine we should make sure there won't be any performance bottle-neck after apply it. You can test queries by using IO and Time statistics whether you gain any performance.


No comments:

Post a Comment