Hints Are Not Always Better

Question

Hints Are Not Always Better

Steve Jones - SSC Editor

SSC Guru

Points: 736162
More actions
May 21, 2011 at 12:03 pm

#240648

Comments posted to this topic are about the item Hints Are Not Always Better

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

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

WayneS SSC Guru Points: 95460 More actions · Answer 1

This post makes me want to see Rob's pre-conference session this October at the PASS Summit. Hopefully he will get picked and many of us will get the chance to learn more nuggets like this one.

Agreed. (Which implies that I find a way to go there myself - either by getting selected to present myself, or work paying for it).

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Rob Farley SSCrazy Points: 2739 More actions · Answer 2

Thanks for your kind words, Steve. It'd be nice to get a precon spot, but I know I'm up against some excellent competition.

Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 3

Very well done Rob and Steve! Rob, I hope you do get a precon!

I wish more people would understand what "tuning" is all about. It's not about getting SQL Server to do what you want... Rather, it's about LETTING SQL Server do what you want. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Nadrek SSC-Insane Points: 20039 More actions · Answer 4

Jeff Moden (5/22/2011)
I wish more people would understand what "tuning" is all about. It's not about getting SQL Server to do what you want... Rather, it's about LETTING SQL Server do what you want. 😀

I respectfully disagree; I would say "tuning" is about achieving as close to an optimal total compromise as possible. "Total" refers to requirements gathering costs, design time costs, run time costs, maintenance costs. "Costs" refers to raw dollars, the effort and time of humans, various forms of computer effort (CPU, IO per device, etc.), wait times, and so on. I want maximum performance with minimal effort now and in the future; but how much I'll trade higher CPU for higher Reads (and vice versa) depends on the precise system in question.

I do use query hints; but I add comments documenting the differences Profiler recorded in the execution costs, so that in the future they can be revisited if they become problematic or are simply being evaluated.

I do agree with an end statement in the article 'The better question is “Is this index good for this query?”', noting that it's situational.

As far as the general "well, in the future/on another system the hint may become detrimental"; this is why you should use them carefully, sparingly, and after updating statistics, etc. on full size production data for your test runs.

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 5

Nadrek (5/23/2011)
Jeff Moden (5/22/2011)
I wish more people would understand what "tuning" is all about. It's not about getting SQL Server to do what you want... Rather, it's about LETTING SQL Server do what you want. 😀
I respectfully disagree; I would say "tuning" is about achieving as close to an optimal total compromise as possible.

I believe I just said that.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

Whenever I feel like adding and hint I ask myself something similar to this :

Am I really smarter than the best MS engineers who had years and trillions of tests to measure this engine and come up with a "best solution" desicion.

Whenever the answer is yes I use the hint... which has never happenned so far!

sknox SSChampion Points: 12388 More actions · Answer 7

Ninja's_RGR'us (5/23/2011)
Whenever I feel like adding and hint I ask myself something similar to this :
Am I really smarter than the best MS engineers who had years and trillions of tests to measure this engine and come up with a "best solution" desicion.
Whenever the answer is yes I use the hint... which has never happenned so far!

That's because you're asking the wrong question. A better question is: Is this particular use case so far different from the wide range which Microsoft's experts have seen, or so rarely used, that they have not optimized for it?

The answer to that question has a slightly higher probability of being "yes".

Going back to the editorial, it's about the situation, not the absolutes. There is no Best Practice. It's Best Practices, because of the different possible situations. On the bright side, that's why we get paid.

Rob Farley SSCrazy Points: 2739 More actions · Answer 8

Well, I got the pre-con! I'm surprised, so if you had any influence in that, Steve, I thank you. Now I just have to make sure I don't disappoint the punters.

Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 9

Steve Jones - SSC Editor

SSC Guru

Points: 736162

May 26, 2011 at 8:16 pm

#1330756

Congrats!