By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,419 Members | 1,609 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,419 IT Pros & Developers. It's quick & easy.

Hints

P: n/a
I am kind of confused about the way SQL Server 2000 handles the hints
that users supply with their SQL statements.
From BOL, it seems that one can specify them with "WITH (...)" clauses

in SQL statements known as table hints. Sometimes, multiple uses of
this form in a statement is OK. Then there is the OPTION clause for
specifying statement hints. However, the documentation on OPTION
section discourages their use.

Being relatively new to SQL Server and still learning about it, what is
the general practice? Use hints or not? And if so, how (through WITH
or OPTION clauses)?

Cheers!

Jul 23 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a

<ne**********@yahoo.com> wrote in message
news:11*********************@f14g2000cwb.googlegro ups.com...
I am kind of confused about the way SQL Server 2000 handles the hints
that users supply with their SQL statements.
From BOL, it seems that one can specify them with "WITH (...)" clauses in SQL statements known as table hints. Sometimes, multiple uses of
this form in a statement is OK. Then there is the OPTION clause for
specifying statement hints. However, the documentation on OPTION
section discourages their use.

Being relatively new to SQL Server and still learning about it, what is
the general practice? Use hints or not? And if so, how (through WITH
or OPTION clauses)?


Generally, "don't".

Usually SQL server will make better guesses than you can.


Cheers!

Jul 23 '05 #2

P: n/a
>> what is the general practice? Use hints or not? <<

1) "Trust in the Optimizer, Luke!" It is usually smarter than you are.
What happens when you give a bad hint?

2) Once you write a query with a hint, that hint stays there. Even if
the pathological situation that made you use a hint heals up. Nobody
will dare remove it later, since it looks important.

3) Every product that supports hints has a different syntax and
underlying model, so your hint code will not port, and the logic of
your hint might not port either.

For example, in Sybase SQL Anywhere, you can give a guess as to what
percentage of the time a predicate will be TRUE. Their optimizer uses
that guess instead of it own computation to build the query. It is not
forced to use a particlar index or method. like other products.

Jul 23 '05 #3

P: n/a
nib
--CELKO-- wrote:
2) Once you write a query with a hint, that hint stays there. Even if
the pathological situation that made you use a hint heals up. Nobody
will dare remove it later, since it looks important.


That's funny! And too true!

Zach
Jul 23 '05 #4

P: n/a
(ne**********@yahoo.com) writes:
I am kind of confused about the way SQL Server 2000 handles the hints
that users supply with their SQL statements.

From BOL, it seems that one can specify them with "WITH (...)" clauses
in SQL statements known as table hints. Sometimes, multiple uses of
this form in a statement is OK. Then there is the OPTION clause for
specifying statement hints. However, the documentation on OPTION
section discourages their use.

Being relatively new to SQL Server and still learning about it, what is
the general practice? Use hints or not? And if so, how (through WITH
or OPTION clauses)?


Be very conservative with adding hints. In an ideal you would never have to
use them, but today I add two hints to one query: one index hint, and one
OPTION clause to turn of parallelism.

My general rule is that I add a hint, if 1) there is an apparent performance
problem and 2) there is an obvious choice of how the query plan should go.
The one hint I am the least conservative is OPTION (MAXDOP 1), because
even if the query would execute faster with parallelism, it will not at
least monopolize all processors in the machine. (And often parallel plans
are more ineffecient than the non-parallel plans.) The hint I am most
conservative of using is the join hint - you dump in a word between
INNER and JOIN, since this use of this hint results in a warning.

Whether to use WITH or OPTION depends on what you want to force. They
serve different purposes, therefore you cannot say that one is better
than the other.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #5

P: n/a
>> That's funny! And too true! <<

Remember the quote from early UNIX days? "There is nothing more
permanent than a temporary patch!"

Jul 23 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.