SQL Server Query Hints

Question

SQL Server Query Hints

VSSGeorge

SSCrazy Eights

Points: 8151
More actions
November 15, 2010 at 11:23 pm

#226641

Can anyone explain SQL Server Query Hints in detail, in a simple fashion.
Thanks in advance

Viewing 4 posts - 1 through 3 (of 3 total)

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

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 1

Junglee_George (11/15/2010)
Can anyone explain SQL Server Query Hints in detail, in a simple fashion.
Thanks in advance

There are so many hints, and would take up pages.

All hints are not always applicable to each type of query.

Basics in my opinion is:

1. Never, never "select * from" - identify the columns you need in the result set

2. if you don't need an "order by" - don't use it, except if you have some decent Indexes.

3. keep your code clean, don't use excessive statements, for simple results.

4. be careful with "LIKE" - identify full "looking for values" if possible.

the list can go on.... hope this gives you a tiny bit of an idea what not and what to do.

Also, have a read:

http://blog.sqlauthority.com/2009/01/20/sql-server-rules-for-optimizining-any-query-best-practices-for-query-optimization/

http://msdn.microsoft.com/en-us/sqlserver/bb671432.aspx

http://www.sql-server-performance.com/articles/dev/sql_best_practices_p1.aspx

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 2

Detailed explanation on Query Hints cannot be given as answers to questions on forums. Search for the same in Google or Books Online.

Also take a look the book Dissecting SQL Server Server Plans by Grant Fritchey.

http://www.sqlservercentral.com/articles/books/65831/

Chapter 5( Controlling Execution Plans with Hints ) has a list of the Query Hints together in one place.

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 3

Junglee_George (11/15/2010)
Can anyone explain SQL Server Query Hints in detail, in a simple fashion.

A paradoxial request. They are not simple if you get into the details. The others above offered some good advice for the details.

The simple version is that they allow you manual control of the declarative, and thus un-controllable, optimization process by forcing specific things to happen at different granularities. You can control query entirety, table access methodology, join mechanics, and other components. They need to be used with a very clear understanding of exactly what override you're putting in place, because you take a perfectly good query and ruin it if you're not careful.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA