2

Are hints really necessary for every sql statement? We have a dba who is anal about it and asks us to put hints on every select and update statements in our stored procs. Is this really necessary?

ConcernedOfTunbridgeWells
  • 64,444
  • 15
  • 143
  • 197
Satish
  • 3,020
  • 7
  • 35
  • 47

5 Answers5

9

Not usually. Putting them on everything sounds like overkill.

The documentation says

Because the SQL Server query optimizer typically selects the best execution plan for a query, we recommend that join_hint, query_hint, and table_hint be used only as a last resort by experienced developers and database administrators

Ed Guiness
  • 34,602
  • 16
  • 110
  • 145
7

Your DBA is wrong.

From MS:

Because the SQL Server query optimizer typically selects the best execution plan for a query, we recommend that join hint, query hint, and table hint be used only as a last resort by experienced developers and database administrators.

Galwegian
  • 41,475
  • 16
  • 112
  • 158
  • Hmm.. He's "wrong" if he says they are necessary. If he merely wants they on every query regardless, that may make him anal, but not "wrong". – James Curran Nov 05 '08 at 15:48
  • @James, if the DBA is asking them to put hints on every select and update query, then I think he's wrong. No? – Galwegian Nov 05 '08 at 15:51
1

Hints are merely hints. They help the optimizer to do the best job possible. But like any optimization, you should focus on the statements that are actually problems.

Ned Batchelder
  • 364,293
  • 75
  • 561
  • 662
  • In this case the hints override any execution plan the query optimizer might select for a query, so they are more than just your normal "hint". – Ed Guiness Nov 06 '08 at 11:59
  • Yes, but my point is that the query will work without any hints. Requiring every query to have hints is just silly. – Ned Batchelder Nov 06 '08 at 12:25
  • Thats not true. SQL Server obeys the hints pretty strictly. – usr Mar 06 '10 at 20:10
1

Normally this is just backwards. However, depending on your situation it might be desirable.

For example, we have one database (a set of databases on one server, actually) where the data is all a nightly snapshot dump of a mainframe system, used for reporting and other purposes. Aside from the batch process that recreates the databases each night, nothing does any writing to this system. In that context, the default locking scheme is not appropriate and politics between our group and the IT group that manages all our servers prevents us from changing it. So: pretty much all queries to those dbs have the with (nolock) hints.

I imagine there are other situations where you may have reporting databases with no writes, or perhaps the reverse: an archiving or logging database that is rarely read. The point is that occasionally a specialized database might be set up where the default locking scheme doesn't fit and you are not able to change it. Then you will need a plethora of pinatas... I mean hints.

But that's the exception that proves the rule. In general, the database optimizer is smarter than you are when it comes to things like locking.

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
1

Depends - the query optimizer makes pretty good choices of intent. What hints are your DBA's demanding? @Ned is a little amiss - a hint explicitly tells the optimizer not to figure-out a path - but use your optimization instead.

To legislate that you should always or never use hints is somewhat ignorant of the issues that hints are there to solve. Some occassions where hints have been critical:

  • NOLOCK to explicitly remove read-locks from domain lookup tables queried within a transaction.
  • nailing a query plan to a specific index because of statistics "drift" during heavy updates (in this instance the plan reverted to table-scan on a 10m row table than use the clustered index)

Never had to use join hints.

stephbu
  • 5,072
  • 26
  • 42