使用過濾統計信息解決基數預估錯誤

基數預估是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/