联接提示
可以在使用JOIN时要求最终执行计划使用指定的JOIN方式，支持LOOP\HASH\MERGE其一。这个我几乎工作中极少使用到。除非生产环境慢SQL的执行计划不合理，例如采用NESTED JOIN，但是LOOP的主表节点数据行数非常多，那是执行计划错误，通过更新统计数据STATISTICS都无法解决，那就是SQL SERVER优化器的锅。才需要通过联接提示的方式改变SQL去优化。这个时候可以通过MERGE JOIN或者HASH JOIN的方式来实现。主要是HASH JOIN，因为如果是MERGE JOIN是要求数据排序，如果本身有索引我相信优化器早就使用了，出现了错误采用NESTED JOIN操作的情况一定是HASH JOIN优化效果大于MERGE JOIN的。当然在优化的时候，条件允许的情况下肯定是要对比下MERGE JOIN和HASH JOIN的实际执行计划的资源消耗差异（IO、CPU和TIME）
查询提示
SQL结尾添加OPTION来指定查询提示，支持的查询提示非常多。查询提示和联结提示的使用场景其实是一样的，只是在下面集中场景需要考虑使用查询提示，查询提示在性能调优中的考虑优先级其实是非常低的，考虑到目前SQL SERVER优化器的可靠性已经非常高的，一般情况下是不需要人为干预的。执行计划的选择是受到SQL语句的参数、参数表字段的数据分布和表数据行数变化影响的，而查询提示会生硬的要求SQL SERVER按照我们的想法去选择执行计划，可能当下效果达到的，但是在选择查询提示的时候也要考虑未来的表数据变化，不然当下的改变会造成未来某一提前的问题，未来出现问题以后负责调优的DBA就不清楚当初你为什么要怎么做。
1、SQL语句性能突然下降非常多，经过统计数据更新、索引优化、语句重写都得不到解决或者达不到满意的效果
2、统计数据、索引和语句写法不存在问题或者最优的情况下，查询计划优化器的执行计划选择并不能满足对性能的要求（就是执行时间还是不满意）
上面两种情况作为DBA可能才是需要考虑查询提示。
不过这个显示中用的也不多。列举几个用到的过或者觉得可能有用的。
HASH GROUP
HASH JOIN也好，HASH GROUP也好，HASH都是我认为可能还存在使用场景的。因为从过往经验看，优化器容易选择错误（造成性能大幅下降）的还是排序或者循环，这个对资源消耗是最大的，那么HASH对于查询是有大表参与的情况下是相对比较友好的。
MAXDOP
这个也是比较有用的，如果语句比较复杂且存在大表或者聚合操作，可以制定并行线程数量，以防把CPU资源吃死。如果你的语句本身会消耗CPU且表多或者表数据量特别大，又不会对语句的时间有要求，或者执行语句的时段正好是查询繁忙的时间，那就可以指定这个去避免出现CPU资源吃死。
MAXRECUISION
递归层数限制，反正出现递归死循环。
RECOMPILE
如果查询参数字段的数据分布极不均匀，可以考虑加上RECOMPILE
TABLE HINT(table,INDEX(索引名), FORCESEEK)
指定查询使用某个表索引，FORCESEEK要求使用检索而不是表扫描
表提示
WITH跟在JOIN的表后面，比较有用的几个表提示
INDEX
指定索引，主要用于要求SQL SERVER使用索引查找，前提是你对参数字段的数据分布均匀性很有信心。
FORCESEEK
强制要求执行计划选择所有查找不是扫描，通常是跟着INDEX提示一起指明
HOLDLOCK|NOLOCK|READPAST|ROWLOCK|TABLOCK|XLOCK
这几个可能是和锁有关的几个提示可能会用到的。HOLDLOCK主要是用到存储过程事务里面，先对表加锁后一直到事务提交，SELECT查询用到。NOLOCK再常见不过。像生产环境查询表数据通常加NOLOCK避免造成堵塞影响生产上的查询，另外一个就是ETL抽数据也是加NOLOCK。REAPAST的使用场景是对于超高并发下更新数据，跳过已经加了锁的行而取下一行。这个就是电商的秒杀场景或者取号场景。ROWLOCK也是针对高并发更新数据场景，对行级别加锁而避免SQL SERVER对表级别加锁。TABLOCK就是相反，加表锁是当前事务会在多处对数据进行更新，避免更新完后后面有其他的并发事务更新了表的行（如果不加表锁，前面的数据更新是采用页锁或者行锁，但是这样子会影响当前存储过程事务在后面对表的二次更新的数据结果，就需要加表锁。）。TABLOCK另外一个变体是TABLOCKX，这个是直接排他，就是其他连接连表数据都不能查询，直接堵塞其他对表的查询请求。XLOCK是最高级别的锁，直接排他，指定XLOCK要同时指定ROWLOCK, PAGLOCK 或 TABLOCK。
参考：