Sql Server 中的提示
每个sql语句都需要提示吗？ 我们有一个 dba，他对此很反感，并要求我们在存储过程中的每个 select 和 update 语句上添加提示。 这真的有必要吗？
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
通常不会。 把它们放在所有东西上听起来有点矫枉过正。
文档说
因为 SQL Server 查询优化器通常会选择查询的最佳执行计划，我们建议经验丰富的开发人员和数据库管理员仅将 join_hint、query_hint 和 table_hint 作为最后的手段使用
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
您的 DBA 错了。
来自 MS：
Your DBA is wrong.
From MS:
提示只是提示。 它们帮助优化器尽可能地完成最好的工作。 但与任何优化一样，您应该关注实际存在问题的语句。
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.
取决于 - 查询优化器做出了很好的意图选择。 您的 DBA 需要什么提示？ @Ned 有点不对劲 - 提示明确告诉优化器不要找出路径 - 而是使用您的优化。
立法规定您应该始终使用或从不使用提示，这在某种程度上忽视了提示要解决的问题。 在某些情况下，提示至关重要：
永远不必使用连接提示。
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:
Never had to use join hints.
通常这只是倒退。 但是，根据您的情况，这可能是可取的。
例如，我们有一个数据库（实际上是一台服务器上的一组数据库），其中的数据都是大型机系统的夜间快照转储，用于报告和其他目的。 除了每晚重新创建数据库的批处理过程之外，没有任何操作对该系统进行任何写入操作。 在这种情况下，默认的锁定方案并不合适，而且我们的团队与管理所有服务器的 IT 团队之间的政治斗争阻止我们对其进行更改。 所以：几乎所有对这些数据库的查询都有
with (nolock)提示。我想在其他情况下，您可能拥有没有写入的报告数据库，或者相反：很少读取的归档或日志记录数据库。 关键是，有时可能会设置一个专用数据库，但默认锁定方案不适合并且您无法更改它。 然后你将需要大量的皮纳塔......我的意思是提示。
但这是证明规则的例外。 一般来说，当涉及到诸如锁定之类的事情时，数据库优化器比您更聪明。
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.