SQL SERVER – Puzzle – Usage of New Index Hints for Denali – 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.

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 for 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] | [Flipkart] printed book FREE (shipped anywhere in the world).

HINT: FORCESEEK, FORCESCAN

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

7 thoughts on “SQL SERVER – Puzzle – Usage of New Index Hints for Denali – ForceSeek and ForceScan

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

  2. 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’

  3. 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

  4. Hi All,

    The contest is not about how to use FORCESCAN or FORCESEEK. It is about writing a normal query and then using this hint and proving that it improves the performance.

    See if you can come up with that kind of example…

  5. 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

  6. 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’

  7. 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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s