FORCESEEK in SQL 2005

  • Hi,

    I didn't find any way how to supply hint FORCESEEK in SQL 2005.

    Do anybody any idea?

    Tx

  • looks like a 2008 hint not 2005

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life
  • I know that FORCESEEK is available in 2008, but I need it in 2005 😉

  • It was introduced in 2008. There just is no equivalent in 2005.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • plavec (3/20/2009)


    I know that FORCESEEK is available in 2008, but I need it in 2005 😉

    Perhaps you could tweak query and indexes so that hints are not necessary. Post query, index definitions and exec plan here if you want a hand.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • plavec (3/20/2009)


    I know that FORCESEEK is available in 2008, but I need it in 2005 😉

    Are you sure you "need" it?


    * Noel

  • noeld (3/20/2009)


    plavec (3/20/2009)


    I know that FORCESEEK is available in 2008, but I need it in 2005 😉

    Are you sure you "need" it?

    yes, im sure 😎

    i tried SQL used for query prepared index, but SQL stil used table scan

  • Perhaps you could tweak query and indexes so that hints are not necessary. Post query, index definitions and exec plan here if you want a hand.

    query = view

    im trying use indexes on tables use from view.

    datamodel is strong that may be email will be better

  • plavec (3/21/2009)


    query = view

    im trying use indexes on tables use from view.

    datamodel is strong that may be email will be better

    Then please post the definition of the view, the definitions of the tables used by the view, the definitions of the indexes on those tables and the query that you're running against the view.

    Oh, and the execution plan, saved as a .sqlplan file, zipped and attached.

    We can probably help you either change the indexes or the query so that you get good performance without resorting to hints, but not without lots more info.

    If the optimiser is picking a table scan, it's because there's no appropriate index. Most likely the indexes you have are not covering and are not selective enough to be used. In cases like this, a table scan is more efficient that an index seek with lots and lots of bookmark lookups.

    Index seeks are not always the best way to run a query.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Tx for your help. We changed model and solved problem.

    But still I'd like to know if somebody knows how to makes compensation for FORCESEEK in 2005 or 2000.

  • plavec (4/1/2009)


    Tx for your help. We changed model and solved problem.

    But still I'd like to know if somebody knows how to makes compensation for FORCESEEK in 2005 or 2000.

    Nice to know you fixed the problem.

    There is no equivalent in 2000 or 2005. You need to go with traditional performance tuning and optimization methods. The best you can do is try to force the use of a particular index through an index hint. But honestly, hints should only be used as a last resort, especially something like the forceseek hint.

    Here's a query you can run against AdventureWorks2008

    SELECT*

    FROM HumanResources.Employee --WITH (FORCESEEK)

    WHERE SickLeaveHours = 59

    AND Gender = 'F'

    AND MaritalStatus = 'M'

    This results in a clustered index scan. If you comment out the WITH clause you can force the SEEK operation. It performs the seek against a different index and does a key lookup operation. The number of reads goes from 9 to 170. While I got a seek operation, because there's nothing magic about a seek, I hurt performance, not helped it. The answer in a case like this is not to force hints to try to change behavior but to create a covering index or possibly change the query to information already available through another index... there are lots of possibilities that don't involve hurting performance.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • I don't like to use hurting performance at any time therefor we changed model.:-)

  • we have a few of these going back to the old sql 2000 days. you need to use a hint

    select cola, colb, colc from sometable with(index=indexname) where colb = whatever

    might not be exact but the code is close

    only time i've seen this needed is when we had statistics problems and it would not pick up the index, if you have a composite index and not using all the columns in the where or in one case the select was grabbing so much data that it thought that a scan was better than a seek. in that case we changed the clustered index from the PK column to the column in the where clause and it went to clustered index seek and fixed the issue. the new clustered index wasn't on a unique column value, but it fixed that problem in that case

  • I misunderstand what do you mean with the statistics problem. Could you write more details about it?

  • I think he's referring to the statistics being out of date or insufficiently sampled. Just because you have auto-stats turned on doesn't mean that all the statistics are getting updated frequently enough. Sometimes, on some systems, you need to update the statistics manually. Further, most of the time statistics are sampled across the range of data. You can, when it's needed, perform a full scan of the data to update the statistics. By the way when an index is created or rebuilt, a full scan is performed, so be careful about updating statistics with sampled data right after a rebuild of the index.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply