参数化（四）：处理非均匀数据分布
前面我们了解了参数嗅探可能是好的也可能是坏的。当数列的分布不均匀的时候参数嗅探就是不好的事情。例如，考虑“Status”列在Orders表中有总共10M行。该列有7个不同的值，如下分布:
| Status | Number of Rows |
| Open | 314 |
| Pending Approval | 561 |
| Approved | 28,990 |
| Paid | 17,610 |
|
Shipped |
817,197 |
|
Closed |
7,922,834 |
|
Cancelled |
1,032,886 |
如果查询status是“Open”的数据时使用参数嗅探，那么优化器很可能选择一个带有index seek 和 key lookup的执行计划。这个计划放在缓存中便于重用。当其他用户执行查询closed状态的时候，相同的执行计划被重用，这就很可能是一个灾难，因为现在将进行8M个键值查找操作。
另外的使用参数嗅探的糟糕情况是用非相等的谓词使用参数。请看下面的查询:
SELECT Id , CustomerId , TransactionDateTime , StatusId FROM Billing.Transactions WHERE TransactionDateTime BETWEEN @FromDateTime AND @ToDateTime ORDER BY TransactionDateTime ASC;
如果查询使用参数嗅探编译，使用值“2014-07-01″ 和“2014-08-01″，那么优化器基于统计估计行数并且大概估计行数为20000。然后创建基于这个估计行数的计划并且放在缓存中。后来的执行可以使用完全不同的参数。例如，用户执行查询用时间参数“2012-01-01″ 和“2014-01-01″。结果集大概有61000行，但是基于之前的行数的计划被重用，并且很可能不是一个好的执行计划。
那么，我们能做些什么来影响参数嗅探？
我将展示一些基于我之前使用存储过程实例的技术:
CREATE PROCEDURE Marketing.usp_CustomersByCountry ( @Country AS NCHAR(2) ) AS SELECT Id , Name , LastPurchaseDate FROM Marketing.Customers WHERE Country = @Country; GO
这里是一个“Country”列的分布情况:
| Country | Number of Rows |
| BE | 70 |
|
CL |
55 |
|
CN |
29,956 |
|
DK |
74 |
|
EG |
64 |
|
IL |
72 |
|
MT |
83 |
|
PT |
75 |
|
TR |
63 |
|
UK |
28,888 |
|
US |
40,101 |
|
VE |
78 |
正如所见，一共12个不同的值，其中三个是较多的行数，然而其余的行数非常少。这是一个极端的分配不均匀情况没，生产环境中可能很难看到。这里恰好可以展示我的观点…
在讨论可行的解决方案之前，先看一下问题…
首先参数赋值为IL。当存储过程首次用“IL”参数执行时，生成计划包含了一个寻找“Country”的索引。对于这个指定的执行这是很有帮助的优化器估计行数是72，完全准确。
下次存储过程执行时，使用参数为“US”。数据中有40,101行，并且这种情况下的最佳执行计划是使用聚集索引扫描，可以避免很多“key lookups”。但是计划已经在内存中，就会重用。不幸的是，这个计划包含了索引查找和“key lookup ”而不是聚集索引扫描，这就是一个非常差的执行计划。此时我们看到索引查找操作符的属性中估计行数是72，然后实际却是40000+。这就是执行计划错误引起的估计行数错误。如果我们查看SELECT 的“Parameter List” 属性，就能发现原因所在。由于编译1是“IL”，而运行时是“US”。
那么现在我们发现了问题，接下来让我们看一下可能的解决方案…
Solution #1 – sys.sp_recompile
很简单就是使用系统存储过程sys.sp_recompile从缓存中移除指定的执行计划或者所有计划引用的指定表和视图。这就是说下次存储过程再次执行时需要重新编译，新的执行计划将被创建。
记住我们的主要问题是值的分布。因此基于一套新的参数重新编译存储过程将创建指定的执行计划，但是大多数时候这并不解决问题，因为新的计划仍然只针对本次的值是好的，当遇到其他不同分布的参数值时依然是不好的计划。我建议当查询中过滤的值绝大多数情况下是惟一值的时候可以考虑重新编译的方式来解决问题，比如当where后面的ｓｔａｔｕｓ 状态为１的占据９９％的数据值时，一般情况就是好的计划。
Solution #2 – WITH RECOMPILE
如果你不喜欢前面这个赌博式的方法，那么WITH RECOMPILE很适合你。与之前依赖传递给指定执行的参数值不同，这种方式使你可以告诉优化器编译在每一个存储过程中编译计划。
ALTER PROCEDURE Marketing.usp_CustomersByCountry ( @Country AS NCHAR(2) ) WITH RECOMPILE AS SELECT Id , Name , LastPurchaseDate FROM Marketing.Customers WHERE Country = @Country; GO
每一次参数嗅探被使用时，意味着执行将得到优化器提供的最佳执行计划。既然新的计划每次执行都被创建，那么ＳＱＬＳｅｒｖｅｒ将不会把计划放到缓存中。
这是一个不错的解决方案，因为每次执行存储过程都产生一个最佳的计划，消除了随机赌博式的副作用。但是缺点是每次编译都必须经过昂贵的优化过程。这是需要密集的ＣＰＵ处理过程。如果系统已经处在ＰＣＵ高负载并且存储过程频繁执行，那么这种方式是不合适的。另一方面，如果ＣＰＵ使用率相对较低并且存储过程只是偶尔执行，那么这就是一个带给你最佳的解决方案。
Solution #3 – OPTION (RECOMPILE)
是一个与前者相似的解决方案，但是也有两个重要的不同点。首先，这个查询参数针对有问题的查询语句而不是整个存储过程。
ALTER PROCEDURE Marketing.usp_CustomersByCountry ( @Country AS NCHAR(2) ) AS SELECT Id , Name , LastPurchaseDate FROM Marketing.Customers WHERE Country = @Country OPTION (RECOMPILE); GO
只对一个语句的重编译节省了大量的资源。
其次，“WITH RECOMPILE”发生在编译时，而“OPTION (RECOMPILE)” 发生在运行时。整个例子中运行时执行这个语句时，暂停执行，重新编译该查询，生成新的执行计划。而其他部分则使用计划缓存。运行时编译带来的好处就是使优化器能预先知道所有的运行时值，甚至不需要参数嗅探。优化器知道参数的值，局部变量和环境设置，然后使用这些数据编译查询。多数情况下，运行时编译生成的计划要比编译时生成的计划好很多。
因此，你应该考虑使用“OPTION (RECOMPILE)” 而不是“WITH RECOMPILE”，因为它使用了更少的资源长生了更好的计划。但是要注意这种方式依然是十分占用ＣＰＵ的。
Solution #4 – OPTIMIZE FOR
另一查询选项“OPTIMIZE FOR”也可以解决参数嗅探问题。该选项指示优化器使用特定的一套参数而不是实际的参数来编译查询。实际上就是重写参数嗅探。注意，这个选项只有当查询必须被重编译的时候才能被使用。选项本身不会引起重编译。
ALTER PROCEDURE Marketing.usp_CustomersByCountry ( @Country AS NCHAR(2) ) AS SELECT Id , Name , LastPurchaseDate FROM Marketing.Customers WHERE Country = @Country OPTION (OPTIMIZE FOR (@Country = N'US')); GO
还记得“Sales. Orders”表的情形吗？99%的执行会使用“Pending Approval”作为参数。而不是使用sys.sp_recompile(重编译)，综上所述，如果希望下一次执行已然使用这个参数，俺么使用OPTIMIZE FOR 将会是此种情况的更佳选择，并且指示优化器无论实际参数在下一次执行时是什么都使用该参数（如上例中的US）。
通过使用“OPTIMIZE FOR UNKNOWN”可以禁止参数嗅探。这个选项指示优化器将参数设为位置，实际上就是禁用了参数嗅探。如果存储过程有多个参数，那么你能分别对每一个参数进行选项处理（禁用）。
ALTER PROCEDURE Marketing.usp_CustomersByCountry ( @Country AS NCHAR(2) ) AS SELECT Id , Name , LastPurchaseDate FROM Marketing.Customers WHERE Country = @Country OPTION (OPTIMIZE FOR (@Country UNKNOWN)); GO
Solution #5 – 最佳方案
到目前为止你可能注意到了，有两个我们希望达到有互相冲突的目的。一个是为每个执行创建最优的计划，另一个是最小化编译避免资源的浪费。“WITH RECOMPILE”方式完成了第一个目的，但是它需要每个执行重新编译。另一方面，sys.sp_recompile方式只重新编译了一次存储过程，但是不会为每个执行产生最佳计划。
那么最佳的解决方案就是平衡这两种冲突的目标。这种平衡思想就是分离参数值到不同的组，每组有不同的优化计划，并且生成不同的优化计划。每个计划只被编译一次，然后从这点来说每个执行都会得到最佳计划，因为计划基于参数值产生，所以合理的分组导致生成对应组的计划。
听起来像魔法吗？让我们看一下这个戏法如何实现…
首先我们需要把值分成不同的组。这是关键部分，并且有许多方式去分组。这里我将使用国家作为参数，将普通国家和非普通国家分成两组。如果该国家的行数占到了表行数的1%以上我将其定义为普通国家。假定SQLServer已经定义了普通国家，通过统计国家列字段。SQLServer 通常使用普通的参数值作为图形统计的条目。
因此我们将普通国家插入到“CommonCountries”表的“Country”，然后删除非普通国家…
CREATE TABLE
Marketing.CommonCountries
(
RANGE_HI_KEY NCHAR(2) NOT NULL ,
RANGE_ROWS INT NOT NULL ,
EQ_ROWS INT NOT NULL ,
DISTINCT_RANGE_ROWS INT NOT NULL ,
AVG_RANGE_ROWS FLOAT NOT NULL ,
CONSTRAINT
pk_CommonCountries_c_RANGEHIKEY
PRIMARY KEY CLUSTERED
(RANGE_HI_KEY ASC)
);
GO
INSERT INTO
Marketing.CommonCountries
(
RANGE_HI_KEY ,
RANGE_ROWS ,
EQ_ROWS ,
DISTINCT_RANGE_ROWS ,
AVG_RANGE_ROWS
)
EXECUTE ('DBCC SHOW_STATISTICS (N''Marketing.Customers'' , ix_Customers_nc_nu_Country) WITH HISTOGRAM');
GO
DECLARE
@RowCount AS INT;
SELECT
@RowCount = COUNT (*)
FROM
Marketing.Customers;
DELETE FROM
Marketing.CommonCountries
WHERE
EQ_ROWS < @RowCount * 0.01;
GO
表的查询内容如下:
| RANGE_HI_KEY | RANGE_ROWS | EQ_ROWS | DISTINCT_RANGE_ROWS | AVG_RANGE_ROWS |
| CN | 0 | 29956 | 0 | 1 |
| UK | 0 | 28888 | 0 | 1 |
| US | 0 | 40101 | 0 | 1 |
这样清楚极了。这三个是普通国家的例子。当然这是比较简单的例子，实际环境可能要复杂的多，有时甚至需要提出一些算法来区分普通和不普通的值。可以使用我这种统计的结果。也可以使用某种监视机制来追踪使用结果和计划。又或者需要开发一套自己的统计机制。无论如何，多数时候是需要开发一个算法来区分值为不同的组。
那么我们可以用这个国家的分组分别生成优化计划。这种方式需要创建不同存储过程，而存储过程除了名字外几乎都是一样的。
在实例中，我创建“Marketing.usp_CustomersByCountry_Common”和“Marketing.usp_CustomersByCountry_Uncommon”两个存储过程。如下：
CREATE PROCEDURE Marketing.usp_CustomersByCountry_Common ( @Country AS NCHAR(2) ) AS SELECT Id , Name , LastPurchaseDate FROM Marketing.Customers WHERE Country = @Country; GO CREATE PROCEDURE Marketing.usp_CustomersByCountry_Uncommon ( @Country AS NCHAR(2) ) AS SELECT Id , Name , LastPurchaseDate FROM Marketing.Customers WHERE Country = @Country; GO
接下来我们修改一个原始的存储过程，这个存储过程变成一个路由。它的工作就是价差参数值并根据值的分组确定执行哪一个对应的存储过程。
ALTER PROCEDURE Marketing.usp_CustomersByCountry ( @Country AS NCHAR(2) ) AS IF EXISTS ( SELECT NULL FROM Marketing.CommonCountries WHERE RANGE_HI_KEY = @Country ) BEGIN EXECUTE Marketing.usp_CustomersByCountry_Common @Country = @Country; END ELSE BEGIN EXECUTE Marketing.usp_CustomersByCountry_Uncommon @Country = @Country; END; GO
这是一个漂亮的解决方案:
首次普通国家作为参数使用，路由存储过程调用普通存储过程。一旦第一次被执行以后，计划被生产在缓存中。多亏了参数嗅探，从此以后，只要普通国家的存储过程被执行都会使用这个计划。然后，同样不常用国家也是如此…
因此，我们为每个参数值都提供了优秀的计划，并且每个计划只被编译一次。通常来书只有2到3组值，因此最多2到3个编译。这就是魔法的实质。
缺点：
当然这只是一个理想的方式，需要注意的是该方案的维护成本。一旦数据发生了改变，算法必须去维护修改来再次适应。如上面的例子，需要每一段时间去重新创建普通国家的表。
总结：
参数嗅探能是好的也可以是坏的事情。既然在SQLServer中默认使用，只要它是好的，我们就应该使用。我们的目的是根据不同场景识别参数嗅探，然后应用文中提到的方式来解决不好的参数嗅探问题。
今后我会选择一些具体生产问题来展示一下各种参数嗅探以及相应的衍生问题的处理方案。
