【SQL】hint、 Option(Recompile) 和optimize for

Option(Recompile)

写在sp最后，可以优化一些table scan变成index seek

因为它可以重新编译执行计划。直接跑sp 先生成完，然后最后又option这个再生成一遍，但是这遍会认真优化吧。不知道执行计划在不在了。

重新编译存储过程

摘自：https://msdn.microsoft.com/zh-cn/library/ms190439.aspx

建议

在首次编译或重新编译过程时，该过程的查询计划针对该数据库及其对象的当前状态进行优化。如果数据库对其数据或结构进行了重要更改，则重新编译过程会进行更新并针对这些更改优化过程的查询计划。这样可以提高过程的处理性能。
有时必须强制执行过程重新编译，而其他时间将自动执行。只要重新启动 SQL Server，就会发生自动重新编译操作。当该过程引用的基础表发生物理设计更改时，也会执行此操作。
强制过程重新编译的另一个原因是抵消过程编译的“参数查找”行为。当 SQL Server 执行过程时，该过程在编译时使用的任何参数值都作为生成查询计划的一部分包括在内。如果这些值表示随后调用此过程时使用的典型值，则该过程在每次编译和执行时都会从查询计划中获益。如果过程的参数值频繁异常，则强制执行过程的重新编译和基于其他参数值的新计划可以改善性能。
SQL Server 具有对过程执行语句级重新编译的特点。当 SQL Server 重新编译存储过程时，只编译导致重新编译的语句，而不编译整个过程。
如果过程的中某些查询定期使用非典型值或临时值，则可通过使用这些查询中的 RECOMPILE 查询提示来改善过程性能。由于仅使用此查询提示的查询将进行重新编译，而不是整个过程进行重新编译，因此将模仿 SQL Server 语句级重新编译行为。但除了使用过程的当前参数值外，RECOMPILE 查询提示还在编译该语句时使用存储过程中本地变量的值。有关详细信息，请参阅查询提示 (Transact-SQL)。

Hint

原文：

https://msdn.microsoft.com/zh-cn/library/ms187713.aspx

https://msdn.microsoft.com/zh-cn/library/ms181714.aspx

小心
由于 SQL Server 查询优化器通常会为查询选择最佳执行计划，因此我们建议资深开发人员和数据库管理员只有在不得已时才可使用提示。

SELECT *
FROM HumanResources.Employee AS e1
UNION
SELECT *
FROM HumanResources.Employee AS e2
OPTION (MERGE UNION);

DECLARE @city_name nvarchar(30);
DECLARE @postal_code nvarchar(15);
SET @city_name = 'Ascheim';
SET @postal_code = 86171;
SELECT * FROM Person.Address
WHERE City = @city_name AND PostalCode = @postal_code
OPTION ( OPTIMIZE FOR (@city_name = 'Seattle', @postal_code UNKNOWN) );

OPTIMIZE FOR ( @variable_name { UNKNOWN | = literal_constant } [ , ...n] )

在编译和优化查询时指示查询优化器对局部变量使用特定值。仅在查询优化期间使用该值，在查询执行期间不使用该值。

@variable_name: 在查询中使用的局部变量的名称，可以为其分配用于 OPTIMIZE FOR 查询提示的值。
UNKNOWN: 指定查询优化器在查询优化期间使用统计数据而不是初始值来确定局部变量的值。
literal_constant: 要分配给 @variable_name 并用于 OPTIMIZE FOR 查询提示的文字常量值。 literal_constant 只在查询优化期间使用，在查询执行期间不用作 @variable_name 的值。 literal_constant 可以是任意可用文字常量表示的 SQL Server 系统数据类型。 literal_constant 的数据类型必须可隐式转换为查询中 @variable_name 所引用的数据类型。

OPTIMIZE FOR 可以抵消优化器的默认参数检测行为，也可在创建计划指南时使用。有关详细信息，请参阅重新编译存储过程。

OPTIMIZE FOR UNKNOWN

指示查询优化器在编译和优化查询时使用所有局部变量的统计数据而不是初始值，包括使用强制参数化创建的参数。

如果在同一查询提示中使用 OPTIMIZE FOR @variable_name = literal_constant 和 OPTIMIZE FOR UNKNOWN，则查询优化器对特定值使用指定的 literal_constant，而对其余变量值使用 UNKNOWN。这些值仅用于查询优化期间，而不会用于查询执行期间。

建议

在首次编译或重新编译过程时，该过程的查询计划针对该数据库及其对象的当前状态进行优化。如果数据库对其数据或结构进行了重要更改，则重新编译过程会进行更新并针对这些更改优化过程的查询计划。这样可以提高过程的处理性能。
有时必须强制执行过程重新编译，而其他时间将自动执行。只要重新启动 SQL Server，就会发生自动重新编译操作。当该过程引用的基础表发生物理设计更改时，也会执行此操作。
强制过程重新编译的另一个原因是抵消过程编译的“参数查找”行为。当 SQL Server 执行过程时，该过程在编译时使用的任何参数值都作为生成查询计划的一部分包括在内。如果这些值表示随后调用此过程时使用的典型值，则该过程在每次编译和执行时都会从查询计划中获益。如果过程的参数值频繁异常，则强制执行过程的重新编译和基于其他参数值的新计划可以改善性能。
SQL Server 具有对过程执行语句级重新编译的特点。当 SQL Server 重新编译存储过程时，只编译导致重新编译的语句，而不编译整个过程。
如果过程的中某些查询定期使用非典型值或临时值，则可通过使用这些查询中的 RECOMPILE 查询提示来改善过程性能。由于仅使用此查询提示的查询将进行重新编译，而不是整个过程进行重新编译，因此将模仿 SQL Server 语句级重新编译行为。但除了使用过程的当前参数值外，RECOMPILE 查询提示还在编译该语句时使用存储过程中本地变量的值。有关详细信息，请参阅查询提示 (Transact-SQL)。