• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 411
  • Last Modified:

SQL Server query issue using index hints

On one server, using an index hint made the sql go very fast as expected.  So used the index hint.  But the same query, on another database server, performed much slower.  Funny thing was, when the index was removed, the query performed very fast. Why is that, and what can be done.  This is a conflicting behavior, how to solve this issue. Are there any other settings to look at, if any experts have seen this.
0
tapdev72
Asked:
tapdev72
4 Solutions
 
knightEknightCommented:
Try re-creating the index on the other server and see if it runs fast.  It may be that the index just needed to be rebuilt.
0
 
JestersGrindCommented:
Compare the execution plans look like on each server.  Do both servers have exactly the same indexes?  Is maintenance being performed on each server (i.e. rebuilding indexes, updating statistics)  If the proper indexes are in place and statistics are up to date, you shouldn't need index hints.

Greg

0
 
Anthony PerkinsCommented:
>>On one server, using an index hint made the sql go very fast as expected.  So used the index hint.<<
That is not a good enough reason.  Hints should only be used as a last resort for precisely the reason you have discovered.  Help the SQL optimizer by building efficient queries and don't force it to use an index that will make it grind to a halt.

>>Why is that, and what can be done.  <<
Don't use hints.  Optimize the query.

>>This is a conflicting behavior, how to solve this issue. <<
Not at all you just told the SQL optimizer to use a less than optimum plan when there were obviously better ones available.
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
ShogunWadeCommented:
Agree with acperkins.   you should avoind using hints where possible.

Perhaps if you could post your query and provide some detail about the tables and indexes we could help you tune it?
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
> Perhaps you have a caching problem.  Free your cache to make sure you are not using an older plan.
> Double check your stats
> Determine the lock escalation level of the query on each server.  Make sure they are the same.
> Using hints makes troubleshooting easy only for the experienced.  You should avoid them if you do not have a complete knowledge of what they are doing exactly.
0
 
tapdev72Author Commented:
Thanks for the helpful hints.  After doing maintenance work - like recreating indexes, updating statistics, the need for the index hint does not arise.  
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now