使用过滤统计信息解决基数预估错误

基数预估是SQL Server里一颗隐藏的宝石。一般而言，基数预估指的是，在查询编译期间，查询优化器尝试找出在执行计划里从各个运算符平均返回的行数。这个估计用来驱动计划本身生成并选择正确的计划运算符——例如像Nested Loop, Merge Join,还是Hash Join的物理连接。当这些估计错误时，查询优化器就会选择错误的计划运算符，相信我——你的查询就会非常非常非常慢！

查询优化器使用称为统计信息对象作为基数预估。每次当你创建一个索引，SQL Server在下面也会创建一个统计对象。这个对象描述了那个索引的数据分布。另外，在查询执行时，SQL Server也能创建统计信息对象，在必须的时候（自动创建统计信息）。数据分布本身（复合索引键的第一列）被描述为所谓的直方图（Histogram）。

直方图最痛苦之一就是最大只有200的步长。步长是对于你所给定列数据一部分的数据分布情况描述。你的表变得越大，你的直方图就越不准确，因为你有最大200的步长（直方图必须尽可能紧凑，它必须复核8kb的页）。

在复合索引键里其他列，SQL Server在统计信息对象里用所谓的密度向量（Density Vector）来保存，它是复合索引键唯一值是如何的情况描述（彼此结合在一起）。例如在某列里有3个不同值，那列的密度向量是0.33333（1/3）。

从SQL Server 2008开始，SQL Server支持所谓的过滤统计信息（Filtered Statistics）（和过滤索引对应）。使用过滤统计信息，你可以为数据的子集创建统计信息对象。对于那个数据子集，你也会有直方图和密度向量。如果在你的数据里有极端值，你可以对那个范围的数据创建过滤统计信息对象，当那个范围的数据被查询时，就可以让查询优化器更好的估计返回的行数。因此使用过滤统计信息，你就提高了基数预估的准确性，SQL Server就会给更好的执行计划性能。下面代码显示在SQL Server 2008及后续版本里如何创建过滤统计信息对象： 

CREATE STATISTICS Country_Austria ON Country(ID) 
WHERE Name = 'Austria' 
GO

 从上面代码可以看到，你用WHERE子句限制表数据的子集，那会通过新的过滤统计信息对象来描述这些数据。但也只有的你的查询也包含这个where条件，查询优化器才可以只用这个新的统计信息对象，就像这样：

SELECT SalesAmount FROM Country
INNER JOIN Orders ON Country.ID = Orders.ID
WHERE Name = 'Austria'
GO

如果在的查询里并不包含同样的WHERE子句，查询优化期在执行计划里访问的索引的统计信息还是原来默认的。如果你对刚才的查询启用9204的跟踪标记，你就可以看到在基数预估时，那个统计信息被查询优化器使用：

SELECT SalesAmount FROM Country
INNER JOIN Orders ON Country.ID = Orders.ID
WHERE Name = 'Austria'
OPTION
(
    RECOMPILE,-- Used to see the Statistics Output
    QUERYTRACEON 3604,-- Redirects the output to SSMS
    QUERYTRACEON 9204 -- Returns the Statistics that were used during Cardinality Estimation ("Stats loaded")
)
GO

查询本身也会编译（因为RECOMPLIE查询提示，即使查询计划已被缓存），因此在SSMS的消息窗，你就可以看到拿个统计信息被用做基数预估。

以过滤统计信息的简单介绍为基础，我想给你通过实例展示下，过滤统计信息是如何提高执行计划质量的。 

-- Create a new database
CREATE DATABASE FilteredStatistics
GO
 
-- Use it
USE FilteredStatistics
GO
 
-- Create a new table
CREATE TABLE Country
(
ID INT PRIMARY KEY, 
Name VARCHAR(100)
) 
GO
 
-- Create a new table
CREATE TABLE Orders
(
ID INT, 
SalesAmount DECIMAL(18, 2)
) 
GO

 我们在表上建立相应的索引：

-- Create a Non-Clustered Index
CREATE NONCLUSTERED INDEX idx_Name ON Country(Name) 
GO
 
-- Create a Clustered Index
CREATE CLUSTERED INDEX idx_ID_SalesAmount ON Orders(ID, SalesAmount) 
GO

最后往2个表里插入初始数据： 

-- Insert a few records into the Lookup Table
INSERT INTO Country VALUES(0, 'Austria') 
INSERT INTO Country VALUES(1, 'UK')
INSERT INTO Country VALUES(2, 'France') 
GO
 
-- Insert uneven distributed order data
INSERT INTO Orders VALUES(0, 0)
 
DECLARE @i INT = 1 
 
WHILE @i <= 1000
BEGIN 
INSERT INTO Orders VALUES (1, @i) 
SET @i += 1
END
GO

 为了保证所有的统计信息都已经是最新的，我用全扫描更新了统计信息：

-- Update the Statistics on both tables
UPDATE STATISTICS Country WITH FULLSCAN 
UPDATE STATISTICS Orders WITH FULLSCAN 
GO

点击工具栏的显示包含实际的执行计划。我们来执行下列的查询：

SELECT SalesAmount FROM Country
INNER JOIN Orders ON Country.ID = Orders.ID
WHERE Name = 'UK'
OPTION
(
RECOMPILE,-- Used to see the Statistics Output
    QUERYTRACEON 3604,-- Redirects the output to SSMS
    QUERYTRACEON 9204-- Returns the Statistics that were used during Cardinality Estimation ("Stats loaded")
)
GO

从执行计划里可以看到，基数预估出现了大问题。

SQL Server 估计行数是501，聚集索引查找运算符的实际行数是1000。SQL Server这里使用idx_ID_SalesAmount统计信息对象的密度向量来做那个估计：密度向量是0.5（在那列我们只有2个不同值），因此估计行数是501（1001 * 0.5）。

当你用Austria参数值执行同样的查询，SQL Server又一次估计行数是501，但是查询本身值返回1行……当其他运算符使用这些估计做运算时，这个行为在执行计划里会有巨大的副作用。例如，Sort和Hash运算符根据这些估计作为内存授予需要的大小。如果低估，你的查询会涌向TempDb，如果高估，你就在浪费内存，当你有大量的并发查询是，就会导致竞争问题（查询内存的最大数量是有资源管理器限制的……）

你可以使用过滤统计信息来帮助这些特殊场景。这个会给SQL Server关于数据本身分布的更多信息，也会在基数预估里得到帮助。对于那个特殊场景，我创建2个不同的过滤统计信息，对于每个国家我都创建各自的过滤统计信息对象： 

-- Fix the problem by creating Filtered Statistics Objects
CREATE STATISTICS Country_UK ON Country(ID) 
WHERE Name = 'UK'
 
CREATE STATISTICS Country_Austria ON Country(ID) 
WHERE Name = 'Austria' 
GO

 现在当你重新执行查询时，最后你会看到基数预估是正确的：

 当你在你表上上创建了过滤统计信息时，你也要注意维护。从整个表本身——如果有20%的数据改变时，SQL Server会自动更新统计信息！！！ 假设你有10000行的表，你在表的子集上创建了过滤统计信息，就定子集行数是500条。在这个情况下，当指定列有2000行改变时，SQL Server会更新过滤统计信息对象。因此你要更新过滤统计信息对象里4倍的数据，才会使统计信息失效然后它被更新（在过滤统计信息区间外，没有数据发生改变）。这是很糟糕的情况，当你使用过滤统计信息时，要记住这个。

希望这篇文章给你过滤统计信息的很好概述，对于给出的查询，你知道如何使用过滤统计信息帮助SQL Server提高基数预估。

感谢关注！

参考文章：

https://www.sqlpassion.at/archive/2013/10/29/fixing-cardinality-estimation-errors-with-filtered-statistics/