Puzzle – Usage of New Index Hints – ForceSeek and ForceScan

Today is Friday and tomorrow is the weekend. I just thought, let us explore something new but additionally give you all a quick puzzle to explore about index hints.

Puzzle - Usage of New Index Hints - ForceSeek and ForceScan book-800x453

SQL Server Denali has new Query Hint – FORCESCAN. In earlier version of SQL Server we already have Query Hint FORCESEEK but now the counter part also exists. The quick understanding is there will be cases when FORCESEEK or FORCESCAN will be helpful and improve the performance of the query.

Now here is the quick contest-

1) Write a Query where using FORCESCAN hint which will improve the performance of the query.

2) Write a Query where using FORCESEEK hint which will improve the performance of the query.

The winner will get my printed book SQL Server Wait Stats [Amazon] printed book FREE (shipped anywhere in the world).

Index Hints: FORCESEEK, FORCESCAN

The FORCESEEK table hint forces the query optimizer to use only an index seek operation. Table hints override the default behavior of the query optimizer for the duration of the data manipulation language (DML) statement.

Update: The puzzle is over at this point of time, but you are welcome to submit your answers.

Please leave your notes in the comments section of this blog.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Query Hint, SQL Index, SQL Server
Previous Post
SQL SERVER – DBA Quiz 2011 – All was well few moments before all went wrong – Reasons and Resolutions
Next Post
SQLAuthority News – SQL Server Quiz 2011 – All was well few moments before all went wrong – Reasons and Resolutions

Related Posts

7 Comments. Leave new

  • 1)SELECT ObjectName FROM ObjectItems WITH (FORCESEEK)
    WHERE ObjectType = ‘SQL_STORED_PROCEDURE’
    2)SELECT ObjectName FROM ObjectItems WITH (FORCESCAN)
    WHERE ObjectType = ‘SQL_STORED_PROCEDURE’

    Reply
  • Partha Pratim Dinda
    September 23, 2011 10:22 am

    1.
    FORSCAN

    SELECT ObjName FROM Objitems
    WITH (FORCESCAN) WHERE ObjectType = ‘SQL_STORED_PROCEDURE’

    FORSCAN and specified index

    SELECT ObjName FROM ObjItems
    WITH (FORCESCAN, INDEX(IX_ObjectId))
    WHERE ObjectType = ‘SQL_STORED_PROCEDURE’

    2.
    FORCESEEK

    SELECT ObjName FROM ObjItems
    WITH (FORCESEEK) WHERE ObjectType = ‘SQL_STORED_PROCEDURE’

    FORCESEEK and specified index

    SELECT ObjName FROM ObjItems
    WITH (FORCESEEK,INDEX (IX_ObjectType_CreateDate)) WHERE ObjectType = ‘SQL_STORED_PROCEDURE’

    FORCESEEK and specified index using at least the specified index columns

    SELECT ObjName FROM ObjItems
    WITH (FORCESEEK(IX_ObjectType_CreateDate(ObjectType,CreateDate))) WHERE ObjectType = ‘SQL_STORED_PROCEDURE’

    Reply
  • ForceScan
    ++++++++++++
    Select Emp.Name, Emp.Address, EmpPay.Salary
    From EmplyeeMaster Emp
    INNER JOIN EmployeePay EmpPay
    WITH (FORCESCAN)
    ON Emp.[ID] = EmpPay.[ID]
    Where Emp.[Salary] > 100000

    ForceSeek
    +++++++++++
    Select Emp.Name, Emp.Address, EmpPay.Salary
    From EmplyeeMaster Emp
    INNER JOIN EmployeePay EmpPay
    WITH (FORCESEEK)
    ON Emp.[ID] = EmpPay.[ID]
    Where Emp.[Salary] > 100000

    Reply
  • hello sir,
    i have created a website in asp.net with mssql. and there is a .cs file is generated automatic in temp file in local system,when we are going to upload this website ,this website is not getting that path?is there any idea to generate this file into our web folder

    Reply
  • Kalyanasundaram.K
    September 24, 2011 6:13 pm

    Hi pinal,

    I have not much idea about FORCESEEK & FORCESCAN.
    I searched and got the below information:

    FORCESEEK: It forces optimizer to use index seek only. Sometimes optimizer does not user proper plan and use index scan which cause high reads on the system. We can use forceseek here to force otpimizer to use index seek which can give better performance.

    FORCESCAN: Specifies that the query optimizer use only an index scan operation as the access path to the referenced table or view. The FORCESCAN hint can be useful for queries in which the optimizer underestimates the number of affected rows and chooses a seek operation rather than a scan operation.

    — FORCESEEK and specified index
    SELECT ObjectName FROM ObjectItems WITH (FORCESEEK,INDEX (IX_ObjectType_CreateDate)) WHERE ObjectType = ‘SQL_STORED_PROCEDURE’

    — FORCESCAN and specified index
    SELECT ObjectName FROM ObjectItems WITH (FORCESCAN, INDEX(IX_ObjectId)) WHERE ObjectType = ‘SQL_STORED_PROCEDURE’

    Reply
  • SELECT *
    FROM Sales.SalesOrderHeader AS h
    INNER JOIN Sales.SalesOrderDetail AS d
    ON h.SalesOrderID = d.SalesOrderID
    WHERE h.TotalDue > 100
    AND (d.OrderQty > 5 OR d.LineTotal 100
    AND (d.OrderQty > 5 OR d.LineTotal < 1000.00);

    Checking execution plan A clustered index seek operation is used to access the data in the Sales.SalesOrderDetail table.

    Reply

Leave a Reply