Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Server query issue using index hints

Posted on 2011-09-16
6
Medium Priority
?
408 Views
Last Modified: 2012-05-12
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
Comment
Question by:tapdev72
6 Comments
 
LVL 33

Accepted Solution

by:
knightEknight earned 500 total points
ID: 36551563
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
 
LVL 21

Assisted Solution

by:JestersGrind
JestersGrind earned 500 total points
ID: 36551579
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
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 500 total points
ID: 36552845
>>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 is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 18

Assisted Solution

by:ShogunWade
ShogunWade earned 500 total points
ID: 36554455
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
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 36554858
> 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
 

Author Comment

by:tapdev72
ID: 36898251
Thanks for the helpful hints.  After doing maintenance work - like recreating indexes, updating statistics, the need for the index hint does not arise.  
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

783 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question