Sqlserver關於統計資訊自動建立自動更新的知識點

官方文件

SSMS右鍵資料庫--Properties--Options--Automatic下面會出現和統計資訊相關的幾個選項，和sys.databases檢視的is_auto_create_stats_on,is_auto_create_stats_incremental_on,is_auto_update_stats_on,is_auto_update_stats_async_on這幾個欄位一一對應




AUTO_CREATE_STATISTICS
在自動建立統計資訊選項 AUTO_CREATE_STATISTICS 為 ON 時，查詢最佳化器將根據需要在查詢謂詞中的單獨列上建立統計資訊，以便改進查詢計劃的基數估計。這些單列統計資訊在現有統計資訊物件中尚未具有直方圖的列上建立。 AUTO_CREATE_STATISTICS 選項不確定是否為索引建立了統計資訊。 此選項也不生成篩選統計資訊。 它嚴格應用於全表的單列統計資訊。
查詢最佳化器透過使用 AUTO_CREATE_STATISTICS 選項建立統計資訊時，統計資訊名稱以 _WA 開頭。 可以使用下面的查詢來確定查詢最佳化器是否為查詢謂詞列建立了統計資訊。
SELECT OBJECT_NAME(s.object_id) AS object_name,  
    COL_NAME(sc.object_id, sc.column_id) AS column_name,  
    s.name AS statistics_name  
FROM sys.stats AS s
INNER JOIN sys.stats_columns AS sc  
    ON s.stats_id = sc.stats_id AND s.object_id = sc.object_id  
WHERE s.name like '_WA%'  
ORDER BY s.name;  



AUTO_UPDATE_STATISTICS
在自動更新統計資訊選項 AUTO_UPDATE_STATISTICS 為 ON 時，查詢最佳化器將確定統計資訊何時可能過期，查詢最佳化器透過計算自最後統計資訊更新後資料修改的次數並且將這一修改次數與某一閾值進行比較，確定統計資訊何時可能過期。 該閾值基於表中或索引檢視中的行數。

   SQL Server 2014 (12.x)，SQL Server 基於更改行的百分比使用閾值。 這與表中的行數無關。 閾值是：
        如果在評估時間統計資訊時表基數為 500 或更低，則每達到 500 次修改時更新一次。
        如果在評估時間統計資訊時表基數大於 500，則每達到 500 + 修改次數的百分之二十時更新一次。

   SQL Server 2016 (13.x) 開始，如果資料庫相容性級別為 130，SQL Server 將使用遞減的動態統計資訊更新閾值，此閾值將根據表中的行數進行調整。 它的計算方式為 1000 與當前的表基數乘積的平方根。 例如，如果表中包含 200 萬行，則計算為 sqrt (1000 * 2000000) = 44721.359。 進行此更改後，將會更頻繁地更新大型表的統計資訊。 但是，如果資料庫的相容性級別低於 130，則適用 SQL Server 2014 (12.x) 閾值。



AUTO_UPDATE_STATISTICS_ASYNC
非同步統計資訊更新選項 AUTO_UPDATE_STATISTICS_ASYNC 將確定查詢最佳化器是使用同步統計資訊更新還是非同步統計資訊更新。 預設情況下，非同步統計資訊更新選項為 OFF 狀態，並且查詢最佳化器以同步方式更新統計資訊。