增量更新後統計資訊消失

我們有一個使用增量統計的大型分區 SQL Server 數據庫。所有索引都分區對齊。當我們嘗試通過分區線上重建一個分區時，所有的統計資訊都會在索引重建後消失。

下面是使用 AdventureWorks2014 數據庫在 SQL Server 2014 中複製問題的腳本。

--Example against AdventureWorks2014 Database

CREATE PARTITION FUNCTION TransactionRangePF1 (DATETIME)
AS RANGE RIGHT FOR VALUES 
(
  '20130501', '20130601', '20130701', '20130801', 
  '20130901', '20131001', '20131101', '20131201', 
  '20140101', '20140201', '20140301'
);
GO

CREATE PARTITION SCHEME TransactionsPS1 AS PARTITION TransactionRangePF1 TO 
(
 [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], 
 [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], 
 [PRIMARY], [PRIMARY], [PRIMARY]
);
GO

CREATE TABLE dbo.TransactionHistory 
(
 TransactionID        INT      NOT NULL, -- not bothering with IDENTITY here
 ProductID            INT      NOT NULL,
 ReferenceOrderID     INT      NOT NULL,
 ReferenceOrderLineID INT      NOT NULL DEFAULT (0),
 TransactionDate      DATETIME NOT NULL DEFAULT (GETDATE()),
 TransactionType      NCHAR(1) NOT NULL,
 Quantity             INT      NOT NULL,
 ActualCost           MONEY    NOT NULL,
 ModifiedDate         DATETIME NOT NULL DEFAULT (GETDATE()),
 CONSTRAINT CK_TransactionType 
   CHECK (UPPER(TransactionType) IN (N'W', N'S', N'P'))
) 
ON TransactionsPS1 (TransactionDate);


INSERT INTO dbo.TransactionHistory
SELECT * FROM Production.TransactionHistory
--  SELECT * FROM sys.partitions
--  WHERE object_id = OBJECT_ID('dbo.TransactionHistory');

CREATE NONCLUSTERED INDEX IDX_ProductId ON dbo.TransactionHistory (ProductId) 
 WITH (DATA_COMPRESSION = ROW, STATISTICS_INCREMENTAL=ON)  
 ON TransactionsPS1 (TransactionDate)

DBCC SHOW_STATISTICS('dbo.TransactionHistory', IDX_ProductId);
PRINT 'Stats are avialable'  

ALTER INDEX [IDX_ProductId] ON [dbo].[TransactionHistory] REBUILD 
 PARTITION = 9 WITH (ONLINE = ON , DATA_COMPRESSION = ROW)

PRINT 'After online index rebuild by partition stats are now gone'
DBCC SHOW_STATISTICS('dbo.TransactionHistory', IDX_ProductId);

PRINT 'Rebuild the stats with a rebuild for all paritions (this works)' 
ALTER INDEX [IDX_ProductId] ON [dbo].[TransactionHistory] REBUILD 
 PARTITION = ALL WITH (ONLINE = ON , DATA_COMPRESSION = ROW, 
 STATISTICS_INCREMENTAL = ON)

PRINT 'Stats are back'
DBCC SHOW_STATISTICS('dbo.TransactionHistory', IDX_ProductId);

PRINT 'Works correctly for an offline rebuild by partition'
ALTER INDEX [IDX_ProductId] ON [dbo].[TransactionHistory] REBUILD 
 PARTITION = 9 WITH (ONLINE = OFF , DATA_COMPRESSION = ROW)

   --stats still there  
DBCC SHOW_STATISTICS('dbo.TransactionHistory', IDX_ProductId);

ALTER INDEX [IDX_ProductId] ON [dbo].[TransactionHistory] REBUILD 
 PARTITION = 9 WITH (ONLINE = ON , DATA_COMPRESSION = ROW)

DBCC SHOW_STATISTICS('dbo.TransactionHistory', IDX_ProductId);
PRINT' stats are gone!!!!!!'

如圖所示，我們無法通過線上分區重建索引而不會失去索引的所有統計資訊。這對我們來說是一個主要的維護問題。似乎統計增量選項需要成為單索引重建語法的一部分，或者線上選項需要像離線選項一樣正確處理它。

如果我遺漏了什麼，請告訴我？

更新：

就我們對增量統計的需求而言：我們正在根據內部客戶 ID 而不是日期進行分區。因此，當引入新客戶（大量數據回載）時，我們可以簡單地更新分區的統計資訊，并快速避免為這個新客戶創建任何醜陋的計劃。我想我會把它作為一個錯誤送出給微軟，看看他們有什麼要說的，然後採用只重新採樣該分區的統計數據的解決方案。

連接錯誤報告：

使用增量統計資訊線上重建索引後統計資訊消失

更新：微軟已經確認這是一個錯誤。

不確定它本身是否是一個錯誤，但這絕對是一個有趣的事件。聯機分區重建是 SQL Server 2014 中的新功能，因此可能有一些內部結構需要對此進行排序。

這是我給你最好的解釋。增量統計絕對要求以相同的速率對所有分區進行採樣，這樣當引擎合併統計頁面時，它可以確信採樣的分佈是可比較的。REBUILD必須以 100% 的採樣率對數據進行採樣。不能保證分區 9 上的 100% 採樣率始終是其餘分區的準確採樣率。因此，引擎似乎無法合併樣本，最終得到一個空的統計數據塊。但是，統計對象仍然存在：

select 
   check_time = sysdatetime(),                         
   schema_name = sh.name,
   table_name = t.name,
   stat_name = s.name,
   index_name = i.name,
   stats_column = index_col(quotename(sh.name)+'.'+quotename(t.name),s.stats_id,1),
   s.stats_id,
   s.has_filter,                       
   s.is_incremental,
   s.auto_created,
   sp.last_updated,    
   sp.rows,
   sp.rows_sampled,                        
   sp.unfiltered_rows,
   modification_counter 
from sys.stats s 
join sys.tables t 
   on s.object_id = t.object_id
join sys.schemas sh
   on t.schema_id = sh.schema_id
left join sys.indexes i 
   on s.object_id = i.object_id
   and s.name = i.name
outer apply sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
where t.name = 'TransactionHistory' and sh.name = 'dbo'

您可以通過多種方式填充 blob：

UPDATE STATISTICS dbo.TransactionHistory (IDX_ProductId) WITH RESAMPLE;

要麼

UPDATE STATISTICS dbo.TransactionHistory (IDX_ProductId) WITH RESAMPLE ON PARTITIONS (9);

或者您可以等待 AutoStats 更新使用該對象的查詢計劃的第一次編譯：

-- look at my creative query
select * 
from dbo.TransactionHistory
where TransactionDate = '20140101';

說了這麼多，Erin Stellato 的這篇富有啟發性的文章強調了被認為是增量統計的主要缺陷。優化器不會在查詢計劃生成中不使用它們的分區級數據，從而降低了增量統計的假定好處。那麼，增量統計目前的好處是什麼？我認為它們的主要用途是能夠以比傳統統計更高的速率更一致地對大型表進行採樣。

使用您的範例，情況如下：

set statistics time on;

update statistics dbo.TransactionHistory(IDX_ProductId)
with fullscan;

--SQL Server Execution Times:
--  CPU time = 94 ms,  elapsed time = 131 ms.


update statistics dbo.TransactionHistory(IDX_ProductId)
with resample on partitions(2);

--SQL Server Execution Times:
--  CPU time = 0 ms,  elapsed time = 5 ms.

drop index IDX_ProductId On dbo.TransactionHistory;

CREATE NONCLUSTERED INDEX IDX_ProductId ON dbo.TransactionHistory (ProductId) 
 WITH (DATA_COMPRESSION = ROW)  
 ON [PRIMARY]

update statistics dbo.TransactionHistory(IDX_ProductId)
with fullscan;

--SQL Server Execution Times:
--  CPU time = 76 ms,  elapsed time = 66 ms.

增量統計的全掃描統計更新需要 131 毫秒。非分區對齊統計資訊的全掃描統計更新需要 66 毫秒。未對齊的統計資訊較慢，很可能是由於將各個統計資訊頁面合併回主直方圖所產生的成本. 但是，使用分區對齊的統計對象，我們可以在 5 毫秒內更新一個分區並將其合併回主直方圖 blob。因此，此時擁有增量統計資訊的管理員面臨著一個決定。他們可以通過僅更新傳統上需要更新的分區來減少總體統計資訊維護時間，或者他們可以嘗試更高的採樣率，以便他們可能在與之前的維護時間範圍相同的時間段內獲得更多的行採樣。前者在維護視窗中留有喘息的空間，後者可能會將非常大的表上的統計資訊推送到查詢根據更準確的統計資訊獲得更好計劃的地方。這不是保證，您的里程可能會有所不同。

讀者可以看到 66 毫秒並不是這張表的統計更新時間，所以我嘗試在 stackexchange 數據集上設置一個測試。我最近下載的轉儲中有 6,418,608 個文章（不包括 StackOverflow 文章和 2012 年的所有文章 - 我的數據錯誤）。

我已將數據分區，[CreationDate]因為 … 展示。

以下是一些非常標準場景的一些時間安排（100% - 索引重建，預設 - 統計數據自動更新或UPDATE STATISTICS沒有指定的採樣率：

• 使用全掃描創建非增量統計：CPU 時間 = 23500 毫秒，經過時間 = 22521 毫秒。
• 使用全掃描創建增量統計：CPU 時間 = 20406 毫秒，經過時間 = 15413 毫秒。
• 使用預設採樣率更新非增量統計：CPU 時間 = 406 毫秒，經過時間 = 408 毫秒。
• 使用預設採樣率更新增量統計：CPU 時間 = 453 毫秒，經過時間 = 507 毫秒。

假設我們比這些預設場景更複雜，並且已經確定 10% 的採樣率是應該為我們提供所需計劃的最低採樣率，同時將維護時間保持在合理的時間範圍內。

• 使用樣本 10% 更新非增量統計：CPU 時間 = 2344 毫秒，經過時間 = 2441 毫秒。
• 使用樣本 10% 更新增量統計：CPU 時間 = 2344 毫秒，經過時間 = 2388 毫秒。

到目前為止，擁有增量統計數據並沒有明顯的好處。但是，如果我們利用未記錄 sys.dm_db_stats_properties_internal()的DMV（如下），您可以深入了解您可能想要更新的分區。假設我們對分區 3 中的數據進行了更改，並且我們希望確保傳入查詢的統計資訊是最新的。以下是我們的選擇：

• 預設更新非增量（也是自動統計更新的預設行為）：408 毫秒。
• 以 10% 更新非增量：2441 毫秒。
• 更新增量統計，分區 3 重採樣（10% - 我們定義的採樣率）：CPU 時間 = 63 毫秒，經過時間 = 63 毫秒。

這是我們需要做出決定的地方。我們是否贏得了 63 毫秒的勝利。基於分區的統計更新，還是我們將採樣率提高得更高？假設我們願意在增量統計中以 50% 的初始採樣命中：

• 以 50% 更新增量統計：經過的時間 = 16840 毫秒。
• 使用重採樣更新增量統計，分區 3（50% - 我們的新更新時間）：經過時間 = 295 毫秒。

我們能夠採樣更多的數據，也許設置優化器來更好地猜測我們的數據（即使它還沒有使用分區級別的統計資訊），我們現在能夠更快地做到這一點增量統計。

不過，最後一件有趣的事情要弄清楚。同步統計更新呢？即使自動統計啟動，50% 的採樣率是否也會保留？

我從分區 3 中刪除了數據，並在 CreationDate 上執行了一個查詢，然後使用下面的相同查詢檢查了費率。保留了 50% 的採樣率。

所以，長話短說：增量統計可以是一個有用的工具，只要有適當的思考和初始設置工作。但是，您必須知道要解決的問題，然後才能適當地解決它。如果您得到糟糕的基數估計，您可能能夠通過戰略性採樣率和一些投資干預來獲得更好的計劃。但是，您只能獲得一小部分好處，因為所使用的直方圖是單個合併的統計頁面，而不是分區級別的資訊。如果您在維護視窗中感到痛苦，那麼增量統計也許可以幫助您，但它可能需要您設置一個高接觸的維護干預過程。不管，:

• 使用未與基表分區對齊的索引創建的統計資訊。
• 在 AlwaysOn 可讀輔助數據庫上創建的統計資訊。
• 在只讀數據庫上創建的統計資訊。
• 在過濾索引上創建的統計資訊。
• 在視圖上創建的統計資訊。
• 在內部表上創建的統計資訊。
• 使用空間索引或 XML 索引創建的統計資訊。

希望這可以幫助

select 
   sysdatetime(),                          
   schema_name = sh.name,
   table_name = t.name,
   stat_name = s.name,
   index_name = i.name,
   leading_column = index_col(quotename(sh.name)+'.'+quotename(t.name),s.stats_id,1),
   s.stats_id,
   parition_number = isnull(sp.partition_number,1),
   s.has_filter,                       
   s.is_incremental,
   s.auto_created,
   sp.last_updated,    
   sp.rows,
   sp.rows_sampled,                        
   sp.unfiltered_rows,
   modification_counter = coalesce(sp.modification_counter, n1.modification_counter) 
from sys.stats s 
join sys.tables t 
   on s.object_id = t.object_id
join sys.schemas sh
   on t.schema_id = sh.schema_id
left join sys.indexes i 
   on s.object_id = i.object_id
       and s.name = i.name
cross apply sys.dm_db_stats_properties_internal(s.object_id, s.stats_id) sp
outer apply sys.dm_db_stats_properties_internal(s.object_id, s.stats_id) n1
where n1.node_id = 1
   and (
           (is_incremental = 0)
              or
           (is_incremental = 1 and sp.partition_number is not null)
        )
   and t.name = 'Posts'
   and s.name like 'st_posts%'
order by s.stats_id,isnull(sp.partition_number,1)

引用自：https://dba.stackexchange.com/questions/111590