Statistics & Cardinality Estimation

Thống kê (Statistics) là gì?

Statistics là metadata mà SQL Server Query Optimizer sử dụng để ước tính số lượng rows (cardinality) sẽ được trả về bởi một query operation. Optimizer dùng thông tin này để chọn execution plan tối ưu nhất.

Thành phần của Statistics

Histogram

Histogram chia dữ liệu thành tối đa 200 steps (SQL Server 2016+), mỗi step chứa:

• RANGE_HI_KEY: Giá trị cao nhất trong step
• RANGE_ROWS: Số rows có giá trị trong range (không bao gồm boundary)
• EQ_ROWS: Số rows có giá trị bằng RANGE_HI_KEY
• DISTINCT_RANGE_ROWS: Số distinct values trong range
• AVG_RANGE_ROWS: Average rows per distinct value trong range

Density Vector

Density = 1 / number_of_distinct_values. Density Vector chứa density cho mọi prefix combination của index columns.

-- Xem density vector và histogram
DBCC SHOW_STATISTICS ('Sales.Orders', 'IX_Orders_CustomerID');
-- Hoặc chi tiết hơn
DBCC SHOW_STATISTICS ('Sales.Orders', 'IX_Orders_CustomerID') WITH HISTOGRAM;
DBCC SHOW_STATISTICS ('Sales.Orders', 'IX_Orders_CustomerID') WITH DENSITY_VECTOR;
DBCC SHOW_STATISTICS ('Sales.Orders', 'IX_Orders_CustomerID') WITH STAT_HEADER;

Auto-Create và Auto-Update Statistics

AUTO_CREATE_STATISTICS

SQL Server tự động tạo statistics khi optimizer cần thông tin cho một column chưa có statistics.

-- Kiểm tra setting của database
SELECT name, is_auto_create_stats_on, is_auto_update_stats_on,
       is_auto_update_stats_async_on
FROM sys.databases
WHERE name = 'YourDatabase';

-- Bật Auto Create Statistics
ALTER DATABASE YourDatabase SET AUTO_CREATE_STATISTICS ON;

-- Bật Async Auto Update
ALTER DATABASE YourDatabase SET AUTO_UPDATE_STATISTICS_ASYNC ON;

AUTO_UPDATE_STATISTICS

SQL Server tự động update statistics khi dữ liệu thay đổi vượt ngưỡng:

• SQL Server 2016 trở về trước: 20% rows thay đổi (+ 500 rows cho table nhỏ)
• SQL Server 2016+ với compatibility level 130+: Dynamic threshold (dùng sqrt(1000 * table_rows))

-- Bật Auto Update Statistics
ALTER DATABASE YourDatabase SET AUTO_UPDATE_STATISTICS ON;

-- Async update: query không cần chờ statistics update xong
ALTER DATABASE YourDatabase SET AUTO_UPDATE_STATISTICS_ASYNC ON;

Lưu ý: AUTO_UPDATE_STATISTICS_ASYNC ON giúp tránh blocking nhưng plan hiện tại có thể dùng statistics cũ cho query đó. Plan mới sẽ được dùng cho lần chạy tiếp theo.

Xem và Phân Tích Statistics

sys.statistics và sys.stats_columns

-- Xem tất cả statistics trên một table
SELECT 
    s.name AS stats_name,
    s.auto_created,
    s.user_created,
    s.has_filter,
    s.filter_definition,
    s.stats_id,
    STATS_DATE(s.object_id, s.stats_id) AS last_updated,
    sp.rows,
    sp.rows_sampled,
    sp.steps,
    sp.unfiltered_rows,
    sp.modification_counter
FROM sys.statistics s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
WHERE s.object_id = OBJECT_ID('Sales.Orders')
ORDER BY s.name;

-- Xem các columns trong statistics
SELECT 
    s.name AS stats_name,
    sc.stats_column_id,
    c.name AS column_name
FROM sys.statistics s
JOIN sys.stats_columns sc ON s.object_id = sc.object_id AND s.stats_id = sc.stats_id
JOIN sys.columns c ON sc.object_id = c.object_id AND sc.column_id = c.column_id
WHERE s.object_id = OBJECT_ID('Sales.Orders');

Kiểm tra Statistics Staleness

-- Tìm statistics cũ (chưa update > 7 ngày và có nhiều modifications)
SELECT 
    OBJECT_NAME(s.object_id) AS table_name,
    s.name AS stats_name,
    STATS_DATE(s.object_id, s.stats_id) AS last_updated,
    sp.modification_counter,
    sp.rows,
    CAST(100.0 * sp.modification_counter / NULLIF(sp.rows, 0) AS DECIMAL(5,2)) AS pct_modified
FROM sys.statistics s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
WHERE sp.modification_counter > 0
    AND STATS_DATE(s.object_id, s.stats_id) < DATEADD(DAY, -7, GETDATE())
ORDER BY sp.modification_counter DESC;

Cập Nhật Statistics

UPDATE STATISTICS Options

-- Update tất cả statistics trên table với full scan
UPDATE STATISTICS Sales.Orders WITH FULLSCAN;

-- Update một statistics cụ thể
UPDATE STATISTICS Sales.Orders IX_Orders_CustomerID WITH FULLSCAN;

-- Dùng sample thay vì full scan (nhanh hơn, ít chính xác hơn)
UPDATE STATISTICS Sales.Orders WITH SAMPLE 30 PERCENT;
UPDATE STATISTICS Sales.Orders WITH SAMPLE 10000 ROWS;

-- Update tất cả statistics trong database
EXEC sp_updatestats; -- Chỉ update nếu có thay đổi

-- Update tất cả với full scan (chậm hơn nhưng chính xác nhất)
EXEC sp_msforeachtable 'UPDATE STATISTICS ? WITH FULLSCAN';

-- Dùng ROWCOUNT/PAGECOUNT để override (KHÔNG khuyến nghị cho production)
UPDATE STATISTICS Sales.Orders WITH ROWCOUNT = 5000000, PAGECOUNT = 100000;

UPDATE STATISTICS vs sp_updatestats

Statistics Update Thresholds

Old Threshold (trước SQL 2016)

Threshold = 500 + 20% of table rows

• Table 100 rows: update sau 520 changes
• Table 1,000,000 rows: update sau 200,500 changes
• Vấn đề: Table lớn rất khó trigger auto-update → outdated statistics

Dynamic Threshold (SQL 2016+ với Compat Level 130+)

Threshold = sqrt(1000 * table_rows)

• Table 1,000,000 rows: update sau ~31,623 changes (thay vì 200,500)
• Kích hoạt bằng cách set compatibility level ≥ 130

-- Kiểm tra compatibility level
SELECT name, compatibility_level FROM sys.databases WHERE name = DB_NAME();

-- Nâng compatibility level để dùng dynamic threshold
ALTER DATABASE YourDatabase SET COMPATIBILITY_LEVEL = 150; -- SQL 2019

-- Hoặc dùng trace flag 2371 (SQL 2008-2014 để enable dynamic threshold sớm)
DBCC TRACEON (2371, -1);

Trace Flag 2389 và 2390 — Ascending Key Problem

Vấn đề Ascending Key

Khi một column (thường là identity hoặc datetime) có giá trị mới liên tục được INSERT vào vượt ngoài histogram, optimizer sẽ ước tính 0 hoặc 1 row → chọn plan sai.

-- Ví dụ: OrderDate là ascending key
-- Histogram chỉ biết đến ngày hôm qua
-- Query hôm nay: optimizer estimate 1 row → chọn Nested Loops
-- Thực tế: 50,000 rows → cần Hash Join

SELECT * FROM Sales.Orders WHERE OrderDate >= '2024-01-01';

Giải pháp với Trace Flag

-- TF 2389: SQL Server detect ascending key statistic và mark histogram là "ascending"
-- TF 2390: Tương tự nhưng dùng cho unknown pattern (không chỉ ascending)
DBCC TRACEON (2389, -1);
DBCC TRACEON (2390, -1);

-- SQL Server 2016+ với CE 120+: Dùng Average Row Count thay vì estimate 1 row
-- Không cần trace flags nếu dùng CE mới

Giải pháp thực tế

-- 1. Update statistics thường xuyên hơn (sau batch insert lớn)
UPDATE STATISTICS Sales.Orders IX_Orders_OrderDate WITH FULLSCAN;

-- 2. Tạo Filtered Statistics cho range mới
CREATE STATISTICS ST_Orders_RecentDates 
ON Sales.Orders (OrderDate)
WHERE OrderDate >= '2024-01-01'
WITH FULLSCAN;

-- 3. Dùng OPTION (RECOMPILE) để force fresh estimate
SELECT * FROM Sales.Orders 
WHERE OrderDate >= @StartDate
OPTION (RECOMPILE);

Cardinality Estimation (CE)

CE70 vs CE120+

-- Kiểm tra CE model đang dùng
SELECT compatibility_level FROM sys.databases WHERE name = DB_NAME();

-- Force CE70 (Legacy) cho một query cụ thể
SELECT * FROM Sales.Orders
WHERE CustomerID = 1 AND StatusID = 2
OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'));

-- Force CE120
SELECT * FROM Sales.Orders
OPTION (USE HINT ('ENABLE_QUERY_OPTIMIZER_HOTFIXES'));

-- Force CE ở database level
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON;

Kiểm tra và Xác định CE Issues

-- So sánh estimated vs actual rows trong execution plan
-- Dùng STATISTICS XML để xem chi tiết
SET STATISTICS XML ON;
SELECT * FROM Sales.Orders WHERE CustomerID = 100;
SET STATISTICS XML OFF;

-- Dùng Query Store để tìm regressed queries
SELECT 
    qsq.query_id,
    qsq.query_hash,
    qsp.plan_id,
    qsrs.avg_logical_io_reads,
    qsrs.avg_cpu_time,
    TRY_CAST(qsp.query_plan AS XML) AS query_plan
FROM sys.query_store_query qsq
JOIN sys.query_store_plan qsp ON qsq.query_id = qsp.query_id
JOIN sys.query_store_runtime_stats qsrs ON qsp.plan_id = qsrs.plan_id
ORDER BY qsrs.avg_logical_io_reads DESC;

Cardinality Issues: Estimated vs Actual Row Count

Cách Identify CE Problems

1. Execution Plan: Nhìn vào “Estimated Number of Rows” vs “Actual Number of Rows”
2. Nếu ratio > 10x: Có thể có cardinality estimation problem
3. Triệu chứng: Nested Loops thay vì Hash Join, hoặc ngược lại

-- Query để tìm queries có cardinality mismatch lớn trong Query Store
SELECT TOP 20
    qsq.query_id,
    qsp.plan_id,
    qsrs.count_executions,
    qsrs.avg_logical_io_reads,
    (qsrs.avg_logical_io_reads * qsrs.count_executions) AS total_io,
    qsrs.avg_cpu_time / 1000.0 AS avg_cpu_ms,
    SUBSTRING(qsqt.query_sql_text, 1, 200) AS query_text
FROM sys.query_store_query qsq
JOIN sys.query_store_query_text qsqt ON qsq.query_text_id = qsqt.query_text_id
JOIN sys.query_store_plan qsp ON qsq.query_id = qsp.query_id
JOIN sys.query_store_runtime_stats qsrs ON qsp.plan_id = qsrs.plan_id
WHERE qsrs.avg_logical_io_reads > 10000
ORDER BY total_io DESC;

Filtered Statistics

Filtered Statistics cho phép tạo statistics chỉ trên một subset của data, giúp optimizer có thông tin chính xác hơn cho filtered queries.

-- Tạo filtered statistics cho orders của năm hiện tại
CREATE STATISTICS ST_Orders_Current_Year
ON Sales.Orders (CustomerID, OrderDate)
WHERE YEAR(OrderDate) = YEAR(GETDATE())
WITH FULLSCAN;

-- Tạo filtered statistics cho active products
CREATE STATISTICS ST_Products_Active
ON Products (CategoryID, Price)
WHERE IsActive = 1
WITH FULLSCAN;

-- Xem filtered statistics
SELECT 
    s.name,
    s.has_filter,
    s.filter_definition,
    STATS_DATE(s.object_id, s.stats_id) AS last_updated
FROM sys.statistics s
WHERE s.object_id = OBJECT_ID('Sales.Orders')
    AND s.has_filter = 1;

Multi-Column Statistics

Multi-column statistics cung cấp thông tin về correlation (sự tương quan) giữa các columns.

-- Tạo multi-column statistics
CREATE STATISTICS ST_Orders_Customer_Status
ON Sales.Orders (CustomerID, StatusID, OrderDate)
WITH FULLSCAN;

-- Xem density vector của multi-column statistics
-- Density càng thấp → column combination càng selective
DBCC SHOW_STATISTICS ('Sales.Orders', 'ST_Orders_Customer_Status') WITH DENSITY_VECTOR;

Density và Correlation

• Density = 1 / distinct_values: Density thấp = selective column
• Multi-column density giúp optimizer biết nếu kết hợp columns giảm result set bao nhiêu
• Nếu optimizer không có multi-column stats, nó assume column independence → ước tính sai

Statistics cho Non-Indexed Columns

SQL Server có thể auto-create statistics cho non-indexed columns (nếu AUTO_CREATE_STATISTICS ON). Bạn cũng có thể tạo thủ công:

-- Tạo statistics thủ công cho column không có index
CREATE STATISTICS ST_Orders_Notes
ON Sales.Orders (Notes)
WITH FULLSCAN;

-- Xem tất cả auto-created statistics (không phải từ index)
SELECT 
    OBJECT_NAME(s.object_id) AS table_name,
    s.name AS stats_name,
    s.auto_created,
    STATS_DATE(s.object_id, s.stats_id) AS last_updated
FROM sys.statistics s
WHERE s.auto_created = 1
    AND s.object_id > 100  -- Exclude system objects
ORDER BY OBJECT_NAME(s.object_id), s.name;

-- Xóa auto-created statistics không cần thiết
DROP STATISTICS Sales.Orders._WA_Sys_00000001_1ED998B2;

Statistics cho tempdb và In-Memory OLTP

tempdb Statistics

-- Statistics trong tempdb được tạo cho temp tables và table variables
-- Temp tables: auto-create and auto-update statistics (theo database setting)
-- Table variables: KHÔNG có statistics → optimizer assume 1 row

CREATE TABLE #TempOrders (
    OrderID INT,
    CustomerID INT,
    OrderDate DATE
);

-- Tạo statistics thủ công cho temp table
CREATE STATISTICS ST_TempOrders_Customer ON #TempOrders (CustomerID);

-- Verify
DBCC SHOW_STATISTICS ('#TempOrders', 'ST_TempOrders_Customer');

In-Memory OLTP Statistics

-- Memory-Optimized tables có statistics riêng
-- Xem statistics cho memory-optimized tables
SELECT 
    OBJECT_NAME(s.object_id) AS table_name,
    s.name AS stats_name,
    STATS_DATE(s.object_id, s.stats_id) AS last_updated
FROM sys.statistics s
JOIN sys.tables t ON s.object_id = t.object_id
WHERE t.is_memory_optimized = 1;

-- Update statistics cho memory-optimized table
UPDATE STATISTICS dbo.MemOptOrders WITH FULLSCAN;

Q&A theo Cấp Độ

Junior Level

Q: Statistics là gì và tại sao quan trọng?

A: Statistics là tập metadata mô tả phân phối dữ liệu trong một column hoặc set of columns. Query Optimizer dùng statistics để ước tính số rows sẽ được trả về (cardinality), từ đó chọn execution plan phù hợp (index scan vs seek, nested loops vs hash join). Statistics không chính xác → plan kém hiệu quả → query chậm.

Q: AUTO_CREATE_STATISTICS và AUTO_UPDATE_STATISTICS là gì?

A: AUTO_CREATE_STATISTICS ON cho phép SQL Server tự tạo statistics cho columns chưa có khi cần cho optimization. AUTO_UPDATE_STATISTICS ON cho phép SQL Server tự update statistics khi dữ liệu thay đổi vượt ngưỡng. Cả hai nên được bật trong hầu hết môi trường production.

Q: Làm sao xem statistics của một table?

A:

-- Xem danh sách statistics
SELECT name, STATS_DATE(object_id, stats_id) AS last_updated
FROM sys.statistics
WHERE object_id = OBJECT_ID('YourTable');

-- Xem chi tiết histogram
DBCC SHOW_STATISTICS ('YourTable', 'IndexOrStatsName');

Mid Level

Q: Giải thích vấn đề Ascending Key và cách giải quyết?

A: Vấn đề xảy ra khi column có giá trị mới luôn lớn hơn maximumvalue trong histogram (identity column, datetime). Optimizer không biết về các giá trị mới này và ước tính 0-1 row, dẫn đến plan sai. Giải pháp:

1. Update statistics thường xuyên hơn (sau batch insert lớn)
2. Dùng Trace Flag 2389/2390 (SQL 2014 trở về)
3. Nâng Compatibility Level ≥ 130 (CE 120 xử lý tốt hơn)
4. Dùng Filtered Statistics cho data mới
5. OPTION (RECOMPILE) cho ad-hoc queries

Q: Filtered Statistics là gì và khi nào nên dùng?

A: Filtered Statistics là statistics được tạo trên một subset của data (với WHERE clause). Dùng khi:

• Query thường xuyên filter trên một giá trị cụ thể hoặc range
• Auto statistics không đủ chính xác vì data distribution không đều
• Partition pruning cần statistics cho từng partition range

CREATE STATISTICS ST_Active_Customers
ON Customers (Region, CustomerType)
WHERE IsActive = 1
WITH FULLSCAN;

Q: Sự khác biệt giữa UPDATE STATISTICS WITH FULLSCAN và default sample rate?

A: Default sample rate tự động chọn dựa trên kích thước table (thường 20-30% cho table lớn). WITH FULLSCAN scan toàn bộ data → statistics chính xác nhất nhưng tốn I/O và thời gian nhất. Với table rất lớn (>100M rows), FULLSCAN có thể ảnh hưởng production. Trade-off: accuracy vs performance cost. Recommendation: dùng FULLSCAN trong maintenance window, sample rate cho frequent updates.

Senior Level

Q: Giải thích sự khác biệt giữa CE70 và CE120 và khi nào nên rollback về CE70?

A:

• CE70: Model cũ từ SQL 7.0, assume column independence cho multi-predicate (multiply selectivities). Đơn giản nhưng có thể overestimate hoặc underestimate.
• CE120: Model mới SQL 2014+, dùng exponential backoff cho multi-predicate (giảm dần impact của mỗi predicate sau), xử lý ascending keys tốt hơn, JOIN estimation sophistication hơn.

Khi nào rollback CE70: Sau khi nâng compatibility level, nếu một số query bị regression (chạy chậm hơn với CE120), có thể rollback:

• Database level: ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON
• Query level: OPTION (USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION'))
• Nên dùng Query Store để track regression trước khi quyết định

Q: Trong một hệ thống với table partitioning, làm sao manage statistics hiệu quả?

A:

1. Incremental Statistics (SQL 2014+): Update statistics chỉ cho partition có data thay đổi, không cần scan toàn bộ table.

-- Tạo partitioned table với incremental statistics
CREATE STATISTICS ST_Orders_OrderDate ON Sales.Orders (OrderDate)
WITH FULLSCAN, INCREMENTAL = ON;

-- Update statistics chỉ cho partition 5
UPDATE STATISTICS Sales.Orders ST_Orders_OrderDate
WITH RESAMPLE ON PARTITIONS (5);

2. Per-partition filtered statistics: Tạo filtered stats cho từng partition range để optimizer có thông tin chi tiết hơn về từng partition.

3. Monitor modification counter per partition: Dùng sys.dm_db_incremental_stats_properties thay vì sys.dm_db_stats_properties cho incremental stats.

Q: Làm sao diagnose và fix một slow query do cardinality estimation problem?

A: Step 1 - Identify: Bật Actual Execution Plan, so sánh “Estimated Rows” vs “Actual Rows”. Nếu ratio > 10x, có CE problem.

Step 2 - Root cause:

• Statistics cũ? → STATS_DATE() và modification_counter
• Ascending key? → Xem histogram, giá trị query có nằm ngoài RANGE_HI_KEY max không?
• Complex predicate? → Optimizer dùng correlation hay independence assumption?
• Parameter sniffing? → DBCC FREEPROCCACHE rồi chạy lại với actual parameter

Step 3 - Fix options (theo mức độ invasive):

1. UPDATE STATISTICS ... WITH FULLSCAN (least invasive)
2. Tạo Filtered Statistics hoặc Multi-column Statistics
3. OPTION (RECOMPILE) cho stored procedure với parameter sniffing
4. Query Store: Force good plan với sp_query_store_force_plan
5. Nâng/hạ Compatibility Level
6. Hint: OPTION (USE HINT ('ASSUME_JOIN_CONTAINMENT')) etc.