WITH INDEX hint

Question

Post reply

WITH INDEX hint

cmartel 20772

Ten Centuries

Points: 1161
More actions
November 2, 2016 at 12:29 pm

#331397

I was recently trying to optimize a big SQL statement (a dozen of JOINs and APPLYs), commenting some joins and filters and looking at execution times and plans until I pinpointed the "culprit" JOIN that was making my statement run in minutes rather than in a few seconds.
An appropriate index existed in the joined table but it turned out that SQL Server Optimizer was not using it. Forcing SQL Server to use the right index using the WITH INDEX hint solved the problem.
I read some articles about the pros and cons of using the WITH INDEX hint and I would appreciate any enlightening commentary on the subject. More specifically, can it be that I reached some limits of the optimizer and using the WITH INDEX hint is the best solution or should I never use such hints and try rewrite my statement until it performs well at all times?

Viewing 8 posts - 1 through 7 (of 7 total)

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

ZZartin SSC-Dedicated Points: 30894 More actions · Answer 1

Did you try updating the statistics on the tables in question first?

cmartel 20772 Ten Centuries Points: 1161 More actions · Answer 2

I did update the stats while my statement was oversimplified and it had a positive effect. But with my original statement, the performance remained poor.

Alan Burstein SSC Guru Points: 61136 More actions · Answer 3

I use query hints from time-to-time (not too often) but only when there is no doubt that it's the right choice. The conventional wisdom seems to be that, if you are going to override the query optimizer's choices (what you are going when you use a query hint), then you should: (1) have a solid reason, (2) test your solution thoroughly and (3) understand the risks.

If by "solved the problem" you mean that the query was substantially faster, then you decision was likely a good one. I'd keep an eye on this query however. Is this query going to be faster next week, month, or year because of this hint? Does this index get badly fragmented? That's sometimes a reason that the optimizer wont pick it. Keep in mind that once you add that hint other indexes and execution plan options are limited as long as that hint exists.

I'm not trying to discourage you - just some food for thought.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

You should have compared the original poor queries Estimated Rows with the Actual Rows at various points in the plan. I am guessing you had a 3+ order of magnitude difference between them and that caused the optimizer to pick a scan/hash plan instead of a seek/loop plan (or perhaps vice-versa).

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 5

The question for me is, if that index really is superior, what was keeping the optimizer for choosing it. It does happen on occasion that the optimizer won't choose a valid index, but it's pretty darned rare. Usually it's because the index was insufficiently selective, the code didn't actually support good index use, something. It's entirely possible that with some tweaks an even better index could resolve the issue. Just speculating in the dark since we have zero details.

However, if you tested it thoroughly, and it works, leave it. Query hints in general are there to be used, but sparingly and appropriately. Most of the optimizer gets it right if the code is well structured, the indexes are well structured, and the statistics are up to date.

"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 6

Alan.B (11/2/2016)
Does this index get badly fragmented? That's sometimes a reason that the optimizer wont pick it.

The optimiser knows nothing about fragmentation of indexes, the index's layout or position on disk, hence these will not affect a query plan

I really need to blog on this, it comes up far too often

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 7

GilaMonster (11/2/2016)
Alan.B (11/2/2016)
Does this index get badly fragmented? That's sometimes a reason that the optimizer wont pick it.
The optimiser knows nothing about fragmentation of indexes, the index's layout or position on disk, hence these will not affect a query plan
I really need to blog on this, it comes up far too often

I can't think of a case where index fragmentation affects the optimizer either. IIRC it DOES affect physical-disk-read performance by affecting the read-ahead mechanism, which can have a negative effect on query performance.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service