資料庫效能的參考依據 – Statistics
每一個 Query 都會經過SQL Server 的 Query optimizer
針對目前當下最佳的狀況進行查詢
Query optimizer 會考量資料的分布、Loading、index、計算出的 Cost (CPU 與 I/O cost)
最後選出相對便宜的 Cost 的 Execution plan 來執行
那麼 Query optimizer 還沒有執行，如何知道該實際資料的分布狀況呢?
這就是 SQL Server 平時就會進行的統計分析，也就是 Statistics
根據 Statistics 所提供的資訊，例如：資料的唯一性與分布狀況，
可以讓 Query plan 的產生更加精準
因此這篇文章就要討論 SQL Server 平時如何做這些統計分析
Statistics 的主要功能
SQL Server 在執行每一個 query 時，
主要以相對應計算出來的 Cost 來判斷怎樣的執行最有效率
因此，SQL Server 會對於 index 與欄位進行Statistics 資訊的收集
保持Statistics 資訊的正確性與即時性的更新，
Statistics 的維護工作將有助於產有有效率執行的 query plan
Statistics 自動更新規則
SQL Server 本身預設會自動更新 Statistics
更新的規則分為下列三段：
1. 沒有資料時，一有資料的新增就會立即更新 statistics
2. 當資料量小於 500筆時，增加資料量大於500筆時，才會進行統計更新
3. 多於500筆時，每增加 500筆 +20%的資料亮，才會進行更新
當然，也可以透過下列指令，微調更新的頻率
DBCC TRACEON(2371,-1);
統計資訊的狀態
SELECT c.name,
sc.object_id,
sc.stats_column_id,
sc.stats_id
FROM sys.stats_columns AS sc
JOIN sys.columns AS c
ON c.object_id = sc.object_id
AND c.column_id = sc.column_id
WHERE sc.object_id = OBJECT_ID(‘Test1’);
DBCC 統計資訊
DBCC SHOW_STATISTICS(TableName, IndexName);
該指令會列出Index的統計資訊，分述如下
- Updated:統計資訊什麼時候被更新，例如這個例子為 6.15 / 2014
- Table 共有多少筆資料： 10001
- 平均 Key 的長度：4 Bytes
- 樣本數 Rows Sampled : 10001
- Density: 同質性密度。資料重複性的高低。 Density = 1 / 資料值種類的計數. 。例如：性別的欄位，該 density = 1 / count( distinct (性別) ) = 0.5 。這個值越低，表示重複出現的值越低，越適合當index.
Statistics 的維護與更新
資料庫預測值為 Auto Statistics ON
也可以用下列指令或是 Management Studio 來做調整
- ALTER DATABASE AdventureWorks2012 SET AUTO_CREATE_STATISTICS OFF;
- ALTER DATABASE AdventureWorks2012 SET AUTO_UPDATE_STATISTICS_ASYNC ON;
Auto Statistics 的狀態為何?
- SELECT DATABASEPROPERTYEX(‘AdventureWorks2012’, ‘IsAutoUpdateStatistics’);
- SELECT is_auto_create_stats_on
FROM sys.databases
WHERE [name] = ‘AdventureWorks2012’;
針對特定欄位建立統計資訊
- CREATE STATISTICS Stats1 ON TableName(ColumnName);
手動更新特定統計資訊
- UPDATE STATISTICS TableName IndexName WITH FULLSCAN;
- 使用 FULLSCAN 可以提升該統計的精準度
Automatic Update Statistics Asynchronously
該預設時為 Off，因為更新統計資訊也需要耗費一些資源，因此
如果希望讓Query先執行，執行完之後在做統計資訊的更新，
這個值就可以調整為 ON
好處是，更新統計資訊時，不會影響目前正要執行的 Query，
因此該統計資訊的更新將對於下次(而不是目前)的 query 執行有所幫助
Query execution plan — > Statistics update
資料庫預設值為 off，也就是說，對於當下的 query ，
必須等到統計資訊更新完才會進行執行. Statistics update –> Query execution plan
總結
這篇我們主要討論 Query execution plan 產生的依據 cost
而資料庫怎麼知道怎樣的資料存取比較有效率，就是靠這些統計資訊
統計資訊可以是在一般的欄位，也可以是在索引
統計資訊資料庫預設會自動更新，當然隨著資料的增加，
系統管理者也可以手動更新統計資訊
定期的分析統計資訊，
可以知道統計是否過時間接導致不正確的Query Execution Plan影響查詢效能
因此，定期維護檢查統計資訊也是資料庫效能調整的重要一環。