Tìm hiểu statistics là gì và làm thế nào để tối ưu statistics trong SQL Server

Trong SQL Server, Statistics (thống kê) là một thành phần quan trọng giúp Query Optimizer (bộ tối ưu truy vấn) đưa ra kế hoạch thực thi hiệu quả nhất cho các truy vấn.

Statistics trong SQL Server là gì ?

Statistics là tập hợp thông tin mô tả phân bố dữ liệu trong một hoặc nhiều cột của bảng hoặc chỉ mục. Nó bao gồm:
- Histogram: Biểu đồ tần suất thể hiện phân bố giá trị trong cột.
- Density Vector: Thông tin về độ trùng lặp của các giá trị.
- Cardinality: Số lượng hàng ước tính phù hợp với điều kiện truy vấn.
SQL Server sử dụng statistics để ước lượng số lượng hàng mà một truy vấn sẽ trả về, từ đó chọn kế hoạch thực thi tối ưu (ví dụ: dùng chỉ mục hay quét toàn bộ bảng).
Ứng dụng của Statistics
- Tối ưu hóa truy vấn: Giúp SQL Server chọn cách thực thi truy vấn nhanh nhất.
- Giảm chi phí thực thi: Tránh quét toàn bộ bảng nếu có thể dùng chỉ mục.
- Cải thiện hiệu năng hệ thống: Truy vấn nhanh hơn, ít tiêu tốn tài nguyên hơn.
Ví dụ:
Giả sử bạn có bảng Customers với cột City chứa thông tin thành phố của khách hàng.

-- Tạo bảng mẫu
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    Name NVARCHAR(100),
    City NVARCHAR(100)
);
 
-- Thêm dữ liệu mẫu
INSERT INTO Customers (CustomerID, Name, City)
VALUES (1, 'Alice', 'Hanoi'),
       (2, 'Bob', 'Hanoi'),
       (3, 'Charlie', 'Saigon'),
       (4, 'David', 'Danang'),
       (5, 'Eve', 'Hanoi');
Khi bạn chạy truy vấn:

SELECT * FROM Customers WHERE City = 'Hanoi';
SQL Server sẽ dùng statistics trên cột City để ước lượng có bao nhiêu dòng khớp với 'Hanoi'. Nếu nó biết rằng 'Hanoi' xuất hiện nhiều, nó có thể chọn quét chỉ mục thay vì quét toàn bộ bảng.


Xem Statistics hiện có:

-- Xem danh sách statistics trên bảng
EXEC sp_helpstats 'Customers', 'ALL';
 
-- Xem chi tiết một statistics cụ thể
DBCC SHOW_STATISTICS ('Customers', 'City');
Cập nhật Statistics
Statistics có thể được cập nhật tự động, nhưng bạn cũng có thể cập nhật thủ công:
-- Cập nhật statistics thủ công
UPDATE STATISTICS Customers;

Làm thế nào để tối ưu hóa statistics ?

Tối ưu hóa statistics trong SQL Server là một bước quan trọng để đảm bảo rằng Query Optimizer có thông tin chính xác nhất để đưa ra kế hoạch thực thi hiệu quả. Dưới đây là các chiến lược và kỹ thuật tối ưu hóa statistics:

1. Bật chế độ tự động cập nhật statistics
SQL Server có hai tùy chọn quan trọng:

  • AUTO_CREATE_STATISTICS: Tự động tạo statistics khi cần.
  • AUTO_UPDATE_STATISTICS: Tự động cập nhật statistics khi dữ liệu thay đổi đáng kể.

Kiểm tra và bật nếu chưa bật:

-- Kiểm tra trạng thái
SELECT name, is_auto_create_stats_on, is_auto_update_stats_on
FROM sys.databases
WHERE name = 'TenCSDL';
 
-- Bật tự động
ALTER DATABASE TenCSDL SET AUTO_CREATE_STATISTICS ON;
ALTER DATABASE TenCSDL SET AUTO_UPDATE_STATISTICS ON;
2. Cập nhật thủ công khi cần thiết
Khi bạn thực hiện bulk insert/update/delete, statistics có thể không được cập nhật kịp thời. Bạn nên cập nhật thủ công:

-- Cập nhật toàn bộ statistics của bảng
UPDATE STATISTICS TenBang;
 
-- Hoặc cập nhật cụ thể
UPDATE STATISTICS TenBang TenStatistics;
3. Dùng FULLSCAN để tăng độ chính xác
Mặc định, SQL Server chỉ lấy mẫu dữ liệu để tạo statistics. Nếu bạn cần độ chính xác cao hơn:

-- Tạo statistics với toàn bộ dữ liệu
CREATE STATISTICS TenStatistics ON TenBang(CotX) WITH FULLSCAN;
4. Dùng sp_createstats để tạo statistics tự động

-- Tạo statistics cho tất cả cột chưa có
EXEC sp_createstats;
5. Lên lịch cập nhật định kỳ bằng SQL Agent
Bạn có thể tạo một SQL Agent Job để chạy UPDATE STATISTICS định kỳ (hàng ngày, hàng tuần...) để đảm bảo statistics luôn mới.

6. Theo dõi hiệu quả bằng DBCC SHOW_STATISTICS
Bạn có thể xem histogram và density vector để đánh giá chất lượng statistics:

DBCC SHOW_STATISTICS ('TenBang', 'TenStatistics');
Khi nào nên cập nhật thủ công ?
  • Sau khi nhập dữ liệu lớn.
  • Sau khi xóa hoặc cập nhật hàng loạt.
  • Khi thấy truy vấn chậm bất thường.
  • Khi histogram không phản ánh đúng phân bố dữ liệu thực tế.

Như vậy qua bài viết này Stanford đã giúp các bạn học sql tìm hiểu về statistics là gì và làm sao để tối ưu statistiscs trong SQL Server. Bạn có thể thực hành theo các ví dụ và hướng dẫn trong từng mục để hiểu rõ hơn nhé.

Bên cạnh đó nếu bạn đang muốn được đào tạo bài bản từ cơ bản đến nâng cao có thể tham gia ngay khóa học sql cùng chuyên gia giàu kinh nghiệm Stanford tại đây: http://bit.ly/2SLPYFF và nhận ưu đãi hấp dẫn của Stanford trong thời gian này. Bạn có thể gọi theo hotline: 0963 723 236 - 0866 586 366 để được gọi lại tư vấn trực tiếp nhé.

=============================
☎ STANFORD – ĐÀO TẠO VÀ PHÁT TRIỂN CÔNG NGHỆ
Hotline: 0963 723 236 - 0866 586 366
Website: https://stanford.com.vn
Facebook: http://bit.ly/2FN0TYb
Youtube: http://bit.ly/2TkKT7I

Tags: tìm hiểu statistics, tối ưu statistics trong sql server