Table Index HINT

Question

Table Index HINT

Bobby Glover

SSCarpal Tunnel

Points: 4143
More actions
April 28, 2010 at 5:22 am

#146757

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.

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

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

OomBoom Hall of Fame Points: 3481 More actions · Answer 1

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

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 2

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 4

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

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

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 6

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

Bobby Glover SSCarpal Tunnel Points: 4143 More actions · Answer 7

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.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

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

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 10

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;-)