Hints

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!<newtophp2000@.yahoo.com> wrote in message
news:1104122591.142300.19090@.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!|||>> 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.|||--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|||(newtophp2000@.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, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>> That's funny! And too true! <<

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