(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 
