Table Index HINT

  • Hi

    I've inherited a very poorly designed database. I have realized that some sprocs are doing Index scans on certain tables. When I apply

    <with (Index([index name))> after any from statements . It still does an Index scan on the selected table.

    How do I get it to do an Index seek.

    Where am I going wrong.

    Admitted this is my first attempt at the art of performance tuning.

  • There is allot of considerations.

    Do you have a where clause and joins. If so post it here so that a index recommendation can be made. In cases like this it is usualy the index or the where clause.

    >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
    Do not reinvent the wheel.
    >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

  • Applying hints is absolutely the last step in any tuning process. All you're doing is forcing the processor to only use that index for access, but because of the rest of the issues within the query, whatever they might be, it can't perform a seek.

    Instead of applying hints, you need to look at the execution plan for the query to determine what columns are being used to gather data, what columns are being output. This can lead you to creating indexes. But you may be hitting things like functions on columns, or implicit data conversions, that will prevent indexes from being used. You need to address any and all of these issues before you start trying to use index or query hints to fix the problem.

    Gail Shaw has some good primer articles on performance tuning over at SimpleTalk.com. Look those up. If you need a lot more detail than that, I'd suggest getting a copy of my book.

    "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

  • Part 1: http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

    p.s. In SQL 2005, you cannot force an index seek. You can force an index to be used but you have no control over how it will be used.

    If you want some help, post the query, the execution plan, table structure and index definitions and someone'll give you some pointers.

    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
  • GilaMonster (4/28/2010)


    Part 1: http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

    p.s. In SQL 2005, you cannot force an index seek. You can force an index to be used but you have no control over how it will be used.

    If you want some help, post the query, the execution plan, table structure and index definitions and someone'll give you some pointers.

    Hey Gail, long time... if I'd known I just had to use your name to get you to show up I'd be posting it all over the place.

    "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

  • Coincidence. Just browsing a bit while making phone calls. I'm still staying mostly away from the forums for various reasons.

    Please do post links to my stuff all over the place. I need the traffic. 😀

    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
  • Will do. I was being lazy with the link and got caught. Sorry :crying:

    "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

  • Thanks both, I will take your advice Grant this is not the last resort so I'll read up some more as recommended. Yes there are where and joins within the sproc. Once agin thanks for the speedy and informative posts.

  • Couple other articles that may be of interest: http://www.sqlservercentral.com/articles/Indexing/68439/ (3 parts)

    Plus this may be of some use: http://sqlinthewild.co.za/index.php/category/sql-server/execution-plans/ and http://sqlinthewild.co.za/index.php/category/sql-server/indexes/

    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
  • Grant Fritchey (4/28/2010)


    Will do. I was being lazy with the link and got caught. Sorry :crying:

    No worries. I have it in shortcut menu. I don't go searching for it every time I need it.

    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
  • Better to post table/index defintion and exec plan and get speedy results 😉

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Viewing 11 posts - 1 through 10 (of 10 total)

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